Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to retrieve a list with current dsn names

Posted on 2006-11-23
3
Medium Priority
?
307 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 750 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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