MS SQL Server and ODBC

  1. ‹ Older
  2. 3 months ago
  3. Nedi F

    May 30 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    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

  4. Nedi F

    May 31 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    No ideas?

  5. Greg O

    Jun 1 Xojo Inc
    Edited 3 months ago

    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

  6. Nedi F

    Jun 1 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

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

  7. Tanner L

    is not verified Jun 1 Pre-Release Testers Toronto, Canada

    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
  8. Nedi F

    Jun 1 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)
    Edited 3 months ago

    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.

  9. Tanner L

    is not verified Jun 1 Pre-Release Testers Toronto, Canada

    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).

  10. Nedi F

    Jun 2 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)
    Edited 3 months ago

    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...

  11. 2 months ago

    Nedi F

    Jun 8 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    No suggestions?

  12. Tanner L

    is not verified Jun 8 Pre-Release Testers Toronto, Canada

    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.

  13. Nedi F

    Jun 9 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    Thank you, Tanner Lee….but unfortunately nothing has changed.
    This is my 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 

    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!!!

  14. Tanner L

    is not verified Jun 9 Pre-Release Testers Toronto, Canada

    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.

  15. Nedi F

    Jun 10 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

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

  16. Nedi F

    Jun 12 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    So there is no solution to this problem?

  17. Tanner L

    is not verified Jun 12 Pre-Release Testers Toronto, Canada

    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.

  18. Nedi F

    Jun 12 Pre-Release Testers, Xojo Pro Europe (Italy, Bologna)

    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!

or Sign Up to reply!