[RESOLVED] ODBC connections list

[tibor]

Hi.

I am working with a simple application with the goals of listing the DSN connections available on a machine. I found a nice little snippet of code on the net that works well in vb6.

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copyright ©1996-2009 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Distribution: You can freely use this code in your own
'               applications, but you may not reproduce 
'               or publish this code on any web site,
'               online service, or distribute as source 
'               on any media without express permission.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Declare Function SQLDataSources Lib "odbc32.dll" _
  (ByVal hEnv As Long, _
   ByVal fDirection As Integer, _
   ByVal szDSN As String, _
   ByVal cbDSNMax As Integer, _
   pcbDSN As Integer, _
   ByVal szDescription As String, _
   ByVal cbDescriptionMax As Integer, _
   pcbDescription As Integer) As Long

Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
  (ByVal HandleType As Integer, _
   ByVal InputHandle As Long, _
   OutputHandlePtr As Long) As Long
   
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
  (ByVal EnvironmentHandle As Long, _
   ByVal dwAttribute As Long, _
   ByVal ValuePtr As Long, _
   ByVal StringLen As Long) As Long
   
Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
  (ByVal HandleType As Integer, _
   ByVal Handle As Long) As Long
                         
Private Const SQL_MAX_DSN_LENGTH As Long = 32
Private Const SQL_MAX_DESC_LENGTH As Long = 128
Private Const SQL_SUCCESS As Long = 0
Private Const SQL_FETCH_NEXT As Long = 1
Private Const SQL_NULL_HANDLE As Long = 0
Private Const SQL_HANDLE_ENV As Long = 1
Private Const SQL_ATTR_ODBC_VERSION As Long = 200
Private Const SQL_OV_ODBC3 As Long = 3
Private Const SQL_IS_INTEGER As Long = (-6)


Private Sub Form_Load()

   GetUserSystemDSN

End Sub


Private Sub GetUserSystemDSN()

   Dim hEnv As Long         'handle to the environment
   Dim sServer As String
   Dim sDriver As String
   Dim nSvrLen As Integer
   Dim nDvrLen As Integer
   
  'obtain a handle to the environment
   If SQLAllocHandle(SQL_HANDLE_ENV, _
                     SQL_NULL_HANDLE, hEnv) <> 0 Then
    
     'if successful, set the
     'environment for subsequent calls
      If SQLSetEnvAttr(hEnv, _
                       SQL_ATTR_ODBC_VERSION, _
                       SQL_OV_ODBC3, _
                       SQL_IS_INTEGER) <> 0 Then
   
         
        'set up the strings for the call
         sServer = Space$(SQL_MAX_DSN_LENGTH)
         sDriver = Space$(SQL_MAX_DESC_LENGTH)
               
        'load the DSN names
         Do While SQLDataSources(hEnv, _
                                 SQL_FETCH_NEXT, _
                                 sServer, _
                                 SQL_MAX_DSN_LENGTH, _
                                 nSvrLen, _
                                 sDriver, _
                                 SQL_MAX_DESC_LENGTH, _
                                 nDvrLen) = SQL_SUCCESS
          
           'add data to the controls
            List1.AddItem Left$(sServer, nSvrLen)
            List2.AddItem Left$(sDriver, nDvrLen)
            
           'repad the strings
            sServer = Space$(SQL_MAX_DSN_LENGTH)
            sDriver = Space$(SQL_MAX_DESC_LENGTH)
               
        Loop
        
      End If  'If SQLSetEnvAttr
   
     'clean up
      Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)
   
   End If  'If SQLAllocHandle
   
  'since each DSN returned its corresponding
  'driver, and a given driver can be used
  'for multiple DSN's, remove any adjacent
  'duplicates
   RemoveListDuplicates List2

End Sub


Private Sub RemoveListDuplicates(lst As ListBox)

   Dim n As Long

   If TypeOf lst Is ListBox Then
   
      With lst
      
         If .ListCount > 1 Then
                  
            For n = .ListCount - 1 To 0 Step -1
               If .List(n) = .List(n - 1) Then lst.RemoveItem n
            Next
            
         End If
      
      End With
   
   End If
   
End Sub

I am working in vb.net and looking to update this so that it works with .net. The only problem I have is.

                Do While SQLDataSources(hEnv, _
                                        SQL_FETCH_NEXT, _
                                        sServer, _
                                        SQL_MAX_DSN_LENGTH, _
                                        nSvrLen, _
                                        sDriver, _
                                        SQL_MAX_DESC_LENGTH, _
                                        nDvrLen) = SQL_SUCCESS

This kicks out an error: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

The function is declared as:

Private Declare Function SQLDataSources Lib "odbc32.dll" _
  (ByVal hEnv As Long, _
   ByVal fDirection As Integer, _
   ByVal szDSN As String, _
   ByVal cbDSNMax As Integer, _
   pcbDSN As Integer, _
   ByVal szDescription As String, _
   ByVal cbDescriptionMax As Integer, _
   pcbDescription As Integer) As Long

Now I am assuming that the .NET framework does not like accessing the odbc32.dll and thus will not debug any further. I have tried tweaking the debug options allowing unmanaged code and disabling optimizations but that still will not allow me to run the application.

My question is, is there a work around using this code to access the DSN info from the .dll or is there anyone that has a better alternative to pulling this info as I would like to give the user the option to select the DSN from a list rather than typing it in.

Thanks

[si_the_geek]

The problem is that you are basically using different data types... a VB6 Long is the same as a VB.Net Integer, and a VB6 Integer is the same as a VB.Net Short.

[tibor]

Thanks alot for the help. I ran the code through the upgrade utility to convert it from vb6 to .net because I had changed the data types as you had pointed out but for some reason it wasnt populating anything on the end result. I am not sure why I didnt think to convert it over in the first place but this was the end result for the functions and worked nicely.

    Private Declare Function SQLDataSources Lib "odbc32.dll" _
    (ByVal hEnv As Integer, _
     ByVal fDirection As Short, _
     ByVal szDSN As String, _
     ByVal cbDSNMax As Short, _
     ByRef pcbDSN As Short, _
     ByVal szDescription As String, _
     ByVal cbDescriptionMax As Short, _
     ByRef pcbDescription As Short) As Integer
	
    Private Declare Function SQLAllocHandle Lib "odbc32.dll" _
    (ByVal HandleType As Short, _
     ByVal InputHandle As Integer, _
     ByRef OutputHandlePtr As Integer) As Integer
	
    Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
    (ByVal EnvironmentHandle As Integer, _
     ByVal dwAttribute As Integer, _
     ByVal ValuePtr As Integer, _
     ByVal StringLen As Integer) As Integer
	
    Private Declare Function SQLFreeHandle Lib "odbc32.dll" _
    (ByVal HandleType As Short, _
     ByVal Handle_Renamed As Integer) As Integer