listing the system DSN in a combobox

[Sgiri1]

need code to list the system DSN in my system in a combo box

[Sweeve]

One way to do it is to look in the odbc data sources from the ODBC.INI file.

Private Declare Function GetWindowsDirectory Lib "kernel32" Alias "GetWindowsDirectoryA" _
                (ByVal lpBuffer As String, _
                 ByVal nSize As Long) As Long

Public Declare Function GetPrivateProfileSection Lib "kernel32" Alias "GetPrivateProfileSectionA" _
                (ByVal lpAppName As String, _
                 ByVal lpReturnedString As String, _
                 ByVal nSize As Long, _
                 ByVal lpFileName As String) As Long


Private Sub LoadDriverCombo()

    Dim RetVal      As Long
    Dim I           As Long
    Dim ODBCDir     As String
    Dim strReturn   As String
    Dim ODBCSrc()   As String
    
    strReturn = Space$(4096 & " ")

    '//need to look in system directory instead of registry since some
    '//data sources don't update registry entries 

    '//get the default system directory
    ODBCDir = GetWinDir
    
    '//odbc list should be in path
    ODBCDir = FixPathName(ODBCDir) & "odbc.ini"
    
    '//retrieve the odbc information
    RetVal = GetPrivateProfileSection("ODBC 32 bit Data Sources", strReturn, Len(strReturn) - 1, ODBCDir)
    
    strReturn = UCase$(strReturn)

    '//found the ODBC file - now parse it into the combo
    If RetVal > 32 Then
        ODBCSrc() = Split(UCase$(strReturn), Chr(0))
            
        For I = LBound(ODBCSrc) To UBound(ODBCSrc)
            If Trim$(ODBCSrc(I)) <> "" Then
                cboDataSource.AddItem (Left$(ODBCSrc(I), InStr(1, ODBCSrc(I), "=") - 1))
            End If
        Next

    Else
       error handle
    End If

End Sub


Function GetWinDir() As String

    Dim RetVal      As Long
    Dim strTemp     As String
    
    '//create string for return value
    strTemp = Space$(256)
    RetVal = Len(strTemp)
    
    '//call the API
    Call GetWindowsDirectory(strTemp, RetVal)
    
    '//trim off the trailing null added by the API
    If InStr(Trim(strTemp), Chr$(0)) Then
        strTemp = Left$(strTemp, InStr(strTemp, Chr$(0)) - 1)
    End If
    
    GetWinDir = strTemp
    
End Function

[sasha84]

Hi,

I need to display the list of system DSN in a combobox(VB 6.0).
I tried with your code in this thread. But it is not listing the DSN's.
Do I need to Change anything in the properties of combobox?
I am not well versed with VB. So help me.

[Sgiri1]

what is 'FixPathName' in the code...its left undeclared..it throws up a compile time error

[Sweeve]

Sry, that's just a home-grown string routine that appends the "\" to the end of a file path if it doesn't already contain one. It's not required.

Public Function FixPathName(PathName As String) As String

    FixPathName = PathName & IIf(Right(PathName, 1) = "\", "", "\")
    
End Function

[Sweeve]

Usually your dsn is listed in the windows directory, ODBC.INI file.

So all this code does read this ini file and parse the associated driver information from it and then put the result into a combo box.

Another method is to go into the registry under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI and return the system dsns that way.

If you want to return user dsns, you'll need to read HKEY_CURRENT_USER instead of hkey_local_machine.