Solved

How to retrieve a list with current dsn names

Posted on 2006-11-23
3
303 Views
Last Modified: 2011-10-03
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
Comment
Question by:CraftLord
  • 2
3 Comments
 
LVL 5

Expert Comment

by:DEEPESH
ID: 18001520
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
 
LVL 6

Accepted Solution

by:
manch earned 250 total points
ID: 18001526
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
 
LVL 5

Expert Comment

by:DEEPESH
ID: 18001527
Another useful link
http://vbcity.com/forums/faq.asp?fid=8&cat=General
and read q1391
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question