none
Create an ODBC connection in the registry using vb.NET

Answers

  • There is also another API that was designed to do this. It can create both user and system DSNs.

            Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Integer, ByVal ByValfRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer
        Private Declare Function SQLInstallerError Lib "ODBCCP32.DLL" (ByVal iError As Integer, ByRef pfErrorCode As Integer, ByVal lpszErrorMsg As System.Text.StringBuilder, ByVal cbErrorMsgMax As Integer, ByRef pcbErrorMsg As Integer) As Integer
    
        Private Const ODBC_ADD_DSN As Short = 1 ' Add data source
        Private Const ODBC_ADD_SYS_DSN As Short = 4
        Private Const vbAPINull As Integer = 0 ' NULL Pointer
    
        Public Sub CreateUserDSN()
    
            Dim intRet As Integer
            Dim Driver As String
            Dim Attributes As String
    
            'Set the driver to SQL Server because it is most common.
            Driver = "SQL Server"
            'Set the attributes delimited by null.
            'See driver documentation for a complete
            'list of supported attributes.
            Attributes = "SERVER=(local)" & Chr(0)
            Attributes = Attributes & "DESCRIPTION=Local DSN" & Chr(0)
            Attributes = Attributes & "DSN=Local DSN" & Chr(0)
            Attributes = Attributes & "DATABASE=Northwind" & Chr(0)
            'Unsupported by SQL Server
            'Attributes = Attributes & "Uid=" & Chr(0) & "pwd=" & Chr(0)
            'To show dialog, use Form1.Hwnd instead of vbAPINull.
            intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, Driver, Attributes)
            If intRet <> 0 Then
                MsgBox("DSN Created")
            Else
                Dim nErrorCode As Integer
                Dim strError As New System.Text.StringBuilder(255)
                Dim nErrorLen As Integer
                intRet = SQLInstallerError(1, nErrorCode, strError, 255, nErrorLen)
                MsgBox("Create Failed - " & Left$(strError.ToString, nErrorLen))
            End If
    
        End Sub
    
        Public Sub CreateSystemDSN()
    
            Dim ReturnValue As Integer
            Dim Driver As String
            Dim Attributes As String
    
            'Set the driver to SQL Server because it is most common.
            Driver = "SQL Server"
            'Set the attributes delimited by null.
            'See driver documentation for a complete
            'list of supported attributes.
            Attributes = "SERVER=SomeServer" & Chr(0)
            Attributes = Attributes & "DESCRIPTION=New DSN" & Chr(0)
            Attributes = Attributes & "DSN=DSN_TEMP" & Chr(0)
            Attributes = Attributes & "DATABASE=pubs" & Chr(0)
            'To show dialog, use Form1.Hwnd instead of vbAPINull.
            ReturnValue = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, Driver, Attributes)
            If ReturnValue <> 0 Then
                MsgBox("DSN Created")
            Else
                MsgBox("Create Failed")
            End If
    
        End Sub



    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by JMANCI Tuesday, August 11, 2009 3:30 PM
    • Edited by Paul P Clement IVMVP Tuesday, August 11, 2009 3:54 PM Code amended for missing constants
    Tuesday, August 11, 2009 1:53 PM

All replies

  • Hi JMANCI,

     

    .NET has provided us with powerful APIs to access Windows registry.  Here is a small example to create an ODBC connection string as a registry string value in  \HKEY_CURRENT_USER\Software\MyODBCConnections\ODBCConnectionString1.  

     

    It is a Windows Forms application.  Button1 creates the registry key and Button2 reads it.

    =====================================================================
    Imports Microsoft.Win32

     

    Public Class Form1

     

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

            Dim connStr As String = "ODBC Connection String"

            Try

                ' Create the sub key HKEY_CURRENT_USER\Software\MyODBCConnections

                Dim key As RegistryKey = Registry.CurrentUser.CreateSubKey("SOFTWARE\MyODBCConnections")

     

                ' Set a value of ODBCConnectionString1 in type of RegistryValueKind.String (REG_SZ)

                key.SetValue("ODBCConnectionString1", connStr, RegistryValueKind.String)

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try

           

        End Sub

     

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

            Try

                ' Open the sub key HKEY_CURRENT_USER\Software\MyODBCConnections

                Dim key As RegistryKey = Registry.CurrentUser.OpenSubKey("SOFTWARE\MyODBCConnections", True)

     

                ' Get the value of ODBCConnectionString1

                MsgBox(key.GetValue("ODBCConnectionString1").ToString())

            Catch ex As Exception

                MsgBox(ex.Message)

            End Try

           

        End Sub

    End Class
    =====================================================================

     

    For more information about working with Windows Registry in VB.NET, please refer to
    http://www.codeproject.com/KB/vb/registry_with_vb.aspx

     

    Please note: if we need to save some value into some protected place in Registry like the HKEY_LOCAL_MACHINE, the administrator privilege is necessary.  In Windows Vista or later versions of Windows, due to UAC, we can add a manifest to our project to elevate the application to administrator level or directly run the application as Administrator.  For detail, please see http://msdn.microsoft.com/en-us/library/bb756929.aspx

    Also, here is really helpful website for database connection strings, http://www.connectionstrings.com/
     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, August 11, 2009 2:46 AM
    Moderator
  • There is also another API that was designed to do this. It can create both user and system DSNs.

            Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Integer, ByVal ByValfRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer
        Private Declare Function SQLInstallerError Lib "ODBCCP32.DLL" (ByVal iError As Integer, ByRef pfErrorCode As Integer, ByVal lpszErrorMsg As System.Text.StringBuilder, ByVal cbErrorMsgMax As Integer, ByRef pcbErrorMsg As Integer) As Integer
    
        Private Const ODBC_ADD_DSN As Short = 1 ' Add data source
        Private Const ODBC_ADD_SYS_DSN As Short = 4
        Private Const vbAPINull As Integer = 0 ' NULL Pointer
    
        Public Sub CreateUserDSN()
    
            Dim intRet As Integer
            Dim Driver As String
            Dim Attributes As String
    
            'Set the driver to SQL Server because it is most common.
            Driver = "SQL Server"
            'Set the attributes delimited by null.
            'See driver documentation for a complete
            'list of supported attributes.
            Attributes = "SERVER=(local)" & Chr(0)
            Attributes = Attributes & "DESCRIPTION=Local DSN" & Chr(0)
            Attributes = Attributes & "DSN=Local DSN" & Chr(0)
            Attributes = Attributes & "DATABASE=Northwind" & Chr(0)
            'Unsupported by SQL Server
            'Attributes = Attributes & "Uid=" & Chr(0) & "pwd=" & Chr(0)
            'To show dialog, use Form1.Hwnd instead of vbAPINull.
            intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, Driver, Attributes)
            If intRet <> 0 Then
                MsgBox("DSN Created")
            Else
                Dim nErrorCode As Integer
                Dim strError As New System.Text.StringBuilder(255)
                Dim nErrorLen As Integer
                intRet = SQLInstallerError(1, nErrorCode, strError, 255, nErrorLen)
                MsgBox("Create Failed - " & Left$(strError.ToString, nErrorLen))
            End If
    
        End Sub
    
        Public Sub CreateSystemDSN()
    
            Dim ReturnValue As Integer
            Dim Driver As String
            Dim Attributes As String
    
            'Set the driver to SQL Server because it is most common.
            Driver = "SQL Server"
            'Set the attributes delimited by null.
            'See driver documentation for a complete
            'list of supported attributes.
            Attributes = "SERVER=SomeServer" & Chr(0)
            Attributes = Attributes & "DESCRIPTION=New DSN" & Chr(0)
            Attributes = Attributes & "DSN=DSN_TEMP" & Chr(0)
            Attributes = Attributes & "DATABASE=pubs" & Chr(0)
            'To show dialog, use Form1.Hwnd instead of vbAPINull.
            ReturnValue = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, Driver, Attributes)
            If ReturnValue <> 0 Then
                MsgBox("DSN Created")
            Else
                MsgBox("Create Failed")
            End If
    
        End Sub



    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by JMANCI Tuesday, August 11, 2009 3:30 PM
    • Edited by Paul P Clement IVMVP Tuesday, August 11, 2009 3:54 PM Code amended for missing constants
    Tuesday, August 11, 2009 1:53 PM
  • Thank you Lingzhi and Paul for your suggested answers. We ended up using Paul's API solution to address our issue. Thanks again for your help.
    Tuesday, August 11, 2009 3:35 PM
  • BTW, I amended the code because I noticed that I had forgotton to include some constants. I also included the code to create a system DSN.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, August 11, 2009 3:56 PM