Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

How to retrieve a list with current dsn names

Need to create a selection dialog where the users of my program can select the odbc dsn names.
So the program can dynamically access different databases.
0
CraftLord
Asked:
CraftLord
  • 2
1 Solution
 
DEEPESHCommented:
You have to use following apis

Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, pcbDescription%) As Integer

Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)

Public Const SQL_SUCCESS As Long = 0

Public Const SQL_FETCH_NEXT As Long = 1

or another way is to read registry all dsn are stored in registry under
HKEY_Local_Machine --Software--odbc remember these will be system level dsn
0
 
manchCommented:
Use the following Function for it
It will return a String array assign this string array as a DataSource to the Control on which u want to display the DSNs

    Public Enum DSNType
        All = 0
        UserDSN = 1
        SystemDSN = 2
    End Enum

    '---------------------------------------------------------------------------------------
    'FetchDSNNames
    '---------------------------------------------------------------------------------------
    Public Shared Function FetchDSNNames(ByVal pObjDSNType As DSNType) As String
        '---------------------------------------------------------------------------------------
        'Function Name  :   FetchDSNNames
        'Purpose        :   Fetches the DSN Names on the System (User and System DSNs)
        'Return Type    :   List of DSNNames Seperated by Return Character (String)
        'Modificed By   :
        'Modification   :
        '---------------------------------------------------------------------------------------

        Dim pObjRegistryRootKey As RegistryKey
        Dim pObjRegistrySubKey As RegistryKey
        Dim psNameData As String
        Dim psODBCDSNName As String
        Dim psAllDSNNames As String
        Dim psUserDSNs As String
        Dim psSystemDSNs As String

        Try

            psAllDSNNames = "" & Chr(13)

            psUserDSNs = "" & Chr(13)
            psSystemDSNs = "" & Chr(13)
            '---------------------------------------------------------------------
            'Fetching System DSN's [ Local Machine Registry Scan ]
            '---------------------------------------------------------------------

            'Setting the Registry Root Key
            pObjRegistryRootKey = Registry.LocalMachine

            'To access Key_Local_Machine registry
            pObjRegistrySubKey = pObjRegistryRootKey.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources")

            '
            For Each psODBCDSNName In pObjRegistrySubKey.GetValueNames()
                'Get all the values in the Key

                'Type of DSN [SQL Server etc.]
                psNameData = Convert.ToString(pObjRegistrySubKey.GetValue(psODBCDSNName))

                'Adding the Data to the String
                psAllDSNNames = psAllDSNNames & psODBCDSNName & Chr(13)
                psSystemDSNs = psSystemDSNs & psODBCDSNName & Chr(13)

            Next

            'Closing the Registries After Reading
            pObjRegistrySubKey.Close()
            pObjRegistryRootKey.Close()
            '---------------------------------------------------------------------

            '---------------------------------------------------------------------
            'Fetching User DSN's [ Current User Registry Scan ]
            '---------------------------------------------------------------------

            'Setting the Registry Root Key
            pObjRegistryRootKey = Registry.CurrentUser

            'To access Key_Local_Machine registry
            pObjRegistrySubKey = pObjRegistryRootKey.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources")

            For Each psODBCDSNName In pObjRegistrySubKey.GetValueNames()
                'Get all the values in the Key

                'Type of DSN [SQL Server etc.]
                psNameData = Convert.ToString(pObjRegistrySubKey.GetValue(psODBCDSNName))

                'Adding the Data to the String
                psAllDSNNames = psAllDSNNames & psODBCDSNName & Chr(13)
                psUserDSNs = psUserDSNs & psODBCDSNName & Chr(13)

            Next

            'Closing the Registries After Reading
            pObjRegistrySubKey.Close()
            pObjRegistryRootKey.Close()
            '---------------------------------------------------------------------

            Dim psReturnValue As String
            psReturnValue = ""
            If pObjDSNType = DSNType.All Then
                psReturnValue = psAllDSNNames
            ElseIf pObjDSNType = DSNType.SystemDSN Then
                psReturnValue = psSystemDSNs
            ElseIf pObjDSNType = DSNType.UserDSN Then
                psReturnValue = psUserDSNs
            End If

            Return psReturnValue

        Catch objGeneraException As Exception

            psAllDSNNames = ""
            Return psAllDSNNames

        End Try

    End Function
0
 
DEEPESHCommented:
Another useful link
http://vbcity.com/forums/faq.asp?fid=8&cat=General
and read q1391
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now