Solved

How to retrieve a list with current dsn names

Posted on 2006-11-23
3
305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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 …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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