Microsoft KB Archive/155304

= How To Trap an Invalid ODBC Logon Using the ODBC API =

Article ID: 155304

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q155304



SUMMARY
This article demonstrates how to use Open Database Connectivity (ODBC) API calls to test the validity of a Data Source Name, User ID, and Password.



MORE INFORMATION
When a user tries opening an ODBC Data Source with an incorrect Data Source Name, User ID, or Password, ODBC prompts the user for the correct information. It is not possible to trap for this error using Visual Basic Error Handling methods.

The following steps demonstrate how to test the logon parameters:

 Start a new Visual Basic project. Form1 is created by default. Add a command button (Command1) to Form1. Add three text boxes (Text1, Text2, and Text3) to Form1. Add a check box (Check1) to Form1.  Add the following code to the General Declarations section of Form1: Option Explicit

#If Win32 Then

Private Declare Function SQLAllocEnv Lib "odbc32.dll" _ (phenv As Long) As Integer Private Declare Function SQLAllocConnect Lib "odbc32.dll" _ (ByVal henv As Long, phdbc As Long) As Integer Private Declare Function SQLConnect Lib "odbc32.dll" ( _          ByVal hdbc As Long, ByVal szDSN As String, _           ByVal cbDSN As Integer, ByVal szUID As String, _           ByVal cbUID As Integer, ByVal szAuthStr As String, _           ByVal cbAuthStr As Integer) As Integer Private Declare Function SQLFreeEnv Lib "odbc32.dll" _ (ByVal henv As Long) As Integer Private Declare Function SQLFreeConnect Lib "odbc32.dll" _ (ByVal hdbc As Long) As Integer Private Declare Function SQLError Lib "odbc32.dll" ( _          ByVal henv As Long, ByVal hdbc As Long, ByVal hstmt As Long, _           ByVal szSqlState As String, pfNativeError As Long, _           ByVal szErrorMsg As String, ByVal cbErrorMsgMax As Integer, _           pcbErrorMsg As Integer) As Integer #ElseIf Win16 Then Private Declare Function SQLAllocEnv Lib "odbc.dll" _ (phenv As Long) As Integer Private Declare Function SQLAllocConnect Lib "odbc.dll" _ (ByVal henv As Long, phdbc As Long) As Integer Private Declare Function SQLConnect Lib "odbc.dll" ( _          ByVal hdbc As Long, ByVal szDSN As String, _           ByVal cbDSN As Integer, ByVal szUID As String, _           ByVal cbUID As Integer, ByVal szAuthStr As String, _           ByVal cbAuthStr As Integer) As Integer Private Declare Function SQLFreeEnv Lib "odbc.dll" _ (ByVal henv As Long) As Integer Private Declare Function SQLFreeConnect Lib "odbc.dll" _ (ByVal hdbc As Long) As Integer Private Declare Function SQLError Lib "odbc.dll" ( _          ByVal henv As Long, ByVal hdbc As Long, _           ByVal hstmt As Long, ByVal szSqlState As String, _           pfNativeError As Long, ByVal szErrorMsg As String, _           ByVal cbErrorMsgMax As Integer, pcbErrorMsg As Integer) _ As Integer #End If

Private Const SQL_SUCCESS As Long = 0 Private Const SQL_SUCCESS_WITH_INFO As Long = 1

Private Function IsValidODBCLogin(ByVal sDSN As String, _        ByVal sUID As String, ByVal sPWD As String) As Boolean Dim henv As Long   'Environment Handle Dim hdbc As Long   'Connection Handle Dim iResult As Integer

'Obtain Environment Handle iResult = SQLAllocEnv(henv) If iResult <> SQL_SUCCESS Then IsValidODBCLogin = False Exit Function End If

'Obtain Connection Handle iResult = SQLAllocConnect(henv, hdbc) If iResult <> SQL_SUCCESS Then IsValidODBCLogin = False iResult = SQLFreeEnv(henv) Exit Function End If

'Test Connect Parameters iResult = SQLConnect(hdbc, sDSN, Len(sDSN), sUID, Len(sUID), _                 sPWD, Len(sPWD)) If iResult <> SQL_SUCCESS Then If iResult = SQL_SUCCESS_WITH_INFO Then 'The Connection has been successful, but SQLState Information 'has been returned 'Obtain all the SQLState Information If Check1.Value Then ShowSQLErrorInfo hdbc, vbInformation IsValidODBCLogin = True Else 'Obtain all the Error Information If Check1.Value Then ShowSQLErrorInfo hdbc, vbExclamation IsValidODBCLogin = False End If       Else IsValidODBCLogin = True End If

'Free Connection Handle and Environment Handle iResult = SQLFreeConnect(hdbc) iResult = SQLFreeEnv(henv)

End Function

Private Sub Form_Load

Text1.Text = "DSN" Text2.Text = "User ID" Text3.Text = "" Text3.PasswordChar = "*" Command1.Caption = "Test Connect" Check1.Caption = "Return Errors and Warnings"

End Sub

Private Sub Command1_Click Dim sServer As String, sLogin As String, sPassword As String

sServer = Text1.Text sLogin = Text2.Text sPassword = Text3.Text

If IsValidODBCLogin(sServer, sLogin, sPassword) = True Then MsgBox "Connection Successful", vbInformation, "ODBC Logon" Else MsgBox "Connection Failed", vbExclamation, "ODBC Logon" End If

End Sub

Private Sub ShowSQLErrorInfo(hdbc As Long, iMSGIcon As Integer) Dim iResult As Integer Dim hstmt As Long Dim sBuffer1 As String * 16, sBuffer2 As String * 255 Dim lNative As Long, iOutlen As Integer

sBuffer1 = String$(16, 0) sBuffer2 = String$(256, 0)

Do 'Cycle though all the Errors iResult = SQLError(0, hdbc, hstmt, sBuffer1, lNative, sBuffer2, _                   256, iOutlen) If iResult = SQL_SUCCESS Then If iOutlen = 0 Then MsgBox "Error -- No error information available", _ iMSGIcon, "ODBC Logon" Else MsgBox Left$(sBuffer2, iOutlen), iMSGIcon, "ODBC Logon" End If         End If        Loop Until iResult <> SQL_SUCCESS

End Sub

 Press F5 to run the project. Enter a valid Data Source Name, User ID, and Password in the appropriate text boxes and click on the Test Connect button. A dialog box appears showing whether the connection is Successful or has Failed. Select the check box "Return Errors and Warnings," and click on the Test Connection button. Any errors and warnings are returned.</ol>

<div class="references_section">