MS SQL Server and ODBC

Nedi_Freguglia · May 30, 2019, 11:29am

OS: Windows 10
Database: SQL Server Express 2012
Type of connection: ODBC
Xojo Version: 2018r4

My project starts with a login window, where users type user name and password.
If user name and password are correct, then the main window is displayed, otherwise this error message is displayed (automatically, not by my project), and then this box is displayed (not displayed by my project).

The connection string is this

[code]DB = New ODBCDatabase

Dim SqlStr As String =“Driver={SQL Server Native Client 11.0};Server=” + DB_Host + “;Database=” + DB_Name + _
SqlStr = SqlStr “;UID=” + DB_User + “;Pwd=” + DB_Password + “;MARS_Connection=yes;Persist Security Info=False;”

DB.DataSource = SqlStr
[/code]

If username and/or password is wrong, I’d like to display my MsgBox, not others.

Is there a way to do this?

Thanks to everybody.

Nedi

Emile_Schwarz · May 30, 2019, 11:31am

Its your choice, keep the good work.

Nedi_Freguglia · May 30, 2019, 11:55am

OS: Windows 10
Database: SQL Server Express 2012
Type of connection: ODBC
Xojo Version: 2018r4

My project starts with a login window, where users type user name and password.
If user name and password are correct, then the main window is displayed, otherwise this error message is displayed
Box 1
(automatically, not by my project), and then this box is displayed Box 2 (not displayed by my project).
The connection string is this

DB = New ODBCDatabase 

Dim SqlStr As String ="Driver={SQL Server Native Client 11.0};Server=" + DB_Host + ";Database=" + DB_Name + _
SqlStr = SqlStr  ";UID=" + DB_User + ";Pwd=" + DB_Password + ";MARS_Connection=yes;Persist Security Info=False;"

DB.DataSource = SqlStr

If username and/or password is wrong, I’d like to display my MsgBox, not others.

Is there a way to do this?

Thanks to everybody.

Nedi

Nedi_Freguglia · June 1, 2019, 6:16am

No ideas?

Greg_O_Lone · June 1, 2019, 10:52am

Have you tried adding

DriverCompletion=SQL_DRIVER_NOPROMPT

To your connection string?

https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-2017

Nedi_Freguglia · June 1, 2019, 1:21pm

Yes, Greg, in my first connection string I added this, but it didn’t work…

Tanner_Lee1 · June 1, 2019, 1:55pm

I found this during a search. It’s some old VB code from 2006. Maybe it helps.

Private Sub Main() On Error GoTo ErrHandler 'your code here ErrHandler: If Err.Number = -2147217843 then MsgBox "Authentication Failed", vbCritical, "Error Encountered." Else 'a different error occurred Msgbox "An error occurred. The error number is: " & Err.Number & _ " and the error description is: " & Err.Description, vbCritical, _ "Error Encountered." End If End Sub

Nedi_Freguglia · June 1, 2019, 2:07pm

Thank you, Tanner Lee, but unfortunately it doesn’t work.
In fact the ODBC login prompt is displayed immediately after the DB.Connect, and before every other program line of code, so it is impossible to avoid it programmatically.
However I found this: if I put a breakpoint on the DB.Connect instruction and then I click on the “Step” button, everything works fine; but if I click on Resume button (or if I have no breakpoints) then the login prompt is displayed.

Tanner_Lee1 · June 1, 2019, 2:54pm

Are you getting any error codes that you can read after attempting a failed login? (I’d like to display a custom error dialog as well on login failure).

Nedi_Freguglia · June 2, 2019, 8:44am

Hi Tanner Lee!

No, I don’t get any error code (in this case it would be quite simple to solve the problem).
This is the code

ConnectionSuccessful = DB.Connect


If ConnectionSuccessful Then

  // leggo i parametri SMTP
  stringa = strIn.ReadLine
  s = Split(stringa, ";")
  gSMTPServer = s(0)
  gSMTPPort = s(1)
  gSMTPUser = s(2)
  gSMTPPwd = s(3)
  Main.Show
  LoginWindow.Hide
  LoginWindow.Close

Else
  'MsgBox "Utente o password errati. Riprovare."
  MsgBox DB.ErrorMessage
  txtUser.SetFocus
End If

When the program executes the statement “ConnectionSuccessful = DB.Connect” (after typing a wrong user and/or password) two boxes are displayed:
First box
and then the ODBC login box is displayed
Second box
And then, finally, my MsgBox is displayed.

As you can see, there is nothing I can do in my code to avoid this: I hope there is a way or in the Connection string, or in some other way…

Nedi_Freguglia · June 8, 2019, 9:33am

No suggestions?

Tanner_Lee1 · June 8, 2019, 2:08pm

I can’t replicate the issue here. I’m using SQL Server 2005 and have tried with: MBS (Mac/Win), Actual ODBC Driver (Mac), SQL Server ODBC (Win). All of them provide a trappable db.error when using an incorrect password.

FWIW, here’s my Xojo ODBC connection string:

#If TargetWin32 ConnString = "Driver={SQL Server};Server=" + TestDB.Host + ";Database=eval;UID=" + TestDB.UserName + ";Pwd=" + TestDB.Password TestDB.DataSource = ConnString #EndIf #If TargetMacOS ConnString = "Driver={Actual SQL Server};Server=" + TestDB.Host + ";Database=eval;UID=" + TestDB.UserName + ";Pwd=" + TestDB.Password TestDB.DataSource = ConnString #endif

I’ll also say that I moved to using MBS exclusively years ago because of the many issues in Xojo’s MSSQL (and ODBC when used with MSSQL) drivers.

Nedi_Freguglia · June 9, 2019, 7:30am

Thank you, Tanner Lee.but unfortunately nothing has changed.
This is my code:

[code]
DB = New ODBCDatabase

Dim SqlStr As String =“Driver={SQL Server};Server=” + DB_Host + “;Database=” + DB_Name + _
“;UID=” + DB_User + “;Pwd=” + DB_Password + “;MARS_Connection=yes;”
DB.DataSource = SqlStr
’ --------------------------------------
DB.Host = DB_Host
DB.DatabaseName = DB_Name
DB.UserName = DB_User
DB.Password = DB_Password

ConnectionSuccessful = DB.Connect

If DB.Error = False Then
Main.Show
LoginWindow.Hide
LoginWindow.Close

Else
MsgBox DB.ErrorMessage
txtUser.SetFocus
End If [/code]

If I put a breakpoint in “ConnectionSuccessful = DB.Connect” and then I click on the “Step” button everything goes right: the If statement is executed, and then the MsgBox is displayed.
But if I click on the Resume button then the ODBC boxes are displayed, as shown in my previous posts.

I’m getting out of my head!!!

Tanner_Lee1 · June 9, 2019, 12:29pm

By chance do you have an ODBC DSN configured on the client PC? If so, try renaming it to something else. I ask because I’m curious about where that error dialog box is coming from. Xojo creates DSN-less connections, and in my experience it either connects or fails without any prompting. However, if you are calling the DSN, then it would be normal for Microsoft to display those ODBC error dialogs.

Be sure to check both the 32 and 64 bit versions of the ODBC Data Sources.

Nedi_Freguglia · June 10, 2019, 7:42am

No, Tanner Lee, I don’t use any DSN, but I connect to the database directly through the Connection String.

Nedi_Freguglia · June 12, 2019, 3:57pm

So there is no solution to this problem?

Tanner_Lee1 · June 12, 2019, 4:46pm

I’ve got nothing. As I mentioned above, I can’t replicate the issue.

FWIW, the error code is 18456, but the only middleware that gave me the correct code was the Actual ODBC driver on Mac. MBS, Xojo, and even SQLServer ODBC all gave different codes. Ugh.

Nedi_Freguglia · June 13, 2019, 5:41am

The only solution I know is not to use ODBC connection, but using the Xojo plugin: in this case everything works fine.
Thanks to everybody!