Click here to Skip to main content
15,907,395 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I would like to know the best code for Add, Edit & Delete DSN using MySQL ODBC & VB.NET.

So far this is my code, the problem is that i need to run the system as admin.

Imports Microsoft.Win32

Public Class frmmysqlDSN
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
     Public Sub New()

        'This call is required by the Windows Form Designer.

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
            End If
        End If
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents Label6 As System.Windows.Forms.Label
    Friend WithEvents Label8 As System.Windows.Forms.Label
    Friend WithEvents cmdDsn As System.Windows.Forms.Button
    Friend WithEvents cmdclose As System.Windows.Forms.Button
    Friend WithEvents txtdsnname As System.Windows.Forms.TextBox
    Friend WithEvents txtdescription As System.Windows.Forms.TextBox
    Friend WithEvents txtserver As System.Windows.Forms.TextBox
    Friend WithEvents txtdatabase As System.Windows.Forms.TextBox
    Friend WithEvents txtusername As System.Windows.Forms.TextBox
    Friend WithEvents txtpassword As System.Windows.Forms.TextBox
    Friend WithEvents txtport As System.Windows.Forms.TextBox
    Friend WithEvents GetDSN As System.Windows.Forms.Button
    Friend WithEvents Label7 As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.Label4 = New System.Windows.Forms.Label()
        Me.Label5 = New System.Windows.Forms.Label()
        Me.Label6 = New System.Windows.Forms.Label()
        Me.Label8 = New System.Windows.Forms.Label()
        Me.txtdsnname = New System.Windows.Forms.TextBox()
        Me.txtdescription = New System.Windows.Forms.TextBox()
        Me.txtserver = New System.Windows.Forms.TextBox()
        Me.txtdatabase = New System.Windows.Forms.TextBox()
        Me.txtusername = New System.Windows.Forms.TextBox()
        Me.txtpassword = New System.Windows.Forms.TextBox()
        Me.txtport = New System.Windows.Forms.TextBox()
        Me.cmdDsn = New System.Windows.Forms.Button()
        Me.cmdclose = New System.Windows.Forms.Button()
        Me.GetDSN = New System.Windows.Forms.Button()
        Me.Label7 = New System.Windows.Forms.Label()
        Me.Label1.AutoSize = True
        Me.Label1.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label1.Location = New System.Drawing.Point(56, 40)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(80, 14)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "DSN Name :"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.Label2.AutoSize = True
        Me.Label2.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label2.Location = New System.Drawing.Point(56, 64)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(85, 14)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "Description :"
        Me.Label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.Label3.AutoSize = True
        Me.Label3.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label3.Location = New System.Drawing.Point(24, 112)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(113, 14)
        Me.Label3.TabIndex = 3
        Me.Label3.Text = "Database Name :"
        Me.Label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.Label4.AutoSize = True
        Me.Label4.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label4.Location = New System.Drawing.Point(40, 88)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(95, 14)
        Me.Label4.TabIndex = 2
        Me.Label4.Text = "Server Name :"
        Me.Label4.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.Label5.AutoSize = True
        Me.Label5.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label5.Location = New System.Drawing.Point(64, 160)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(74, 14)
        Me.Label5.TabIndex = 5
        Me.Label5.Text = "Password :"
        Me.Label5.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.Label6.AutoSize = True
        Me.Label6.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label6.Location = New System.Drawing.Point(56, 136)
        Me.Label6.Name = "Label6"
        Me.Label6.Size = New System.Drawing.Size(82, 14)
        Me.Label6.TabIndex = 4
        Me.Label6.Text = "User Name :"
        Me.Label6.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.Label8.AutoSize = True
        Me.Label8.ForeColor = System.Drawing.Color.RoyalBlue
        Me.Label8.Location = New System.Drawing.Point(96, 184)
        Me.Label8.Name = "Label8"
        Me.Label8.Size = New System.Drawing.Size(39, 14)
        Me.Label8.TabIndex = 6
        Me.Label8.Text = "Port :"
        Me.Label8.TextAlign = System.Drawing.ContentAlignment.MiddleRight
        Me.txtdsnname.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtdsnname.Location = New System.Drawing.Point(152, 40)
        Me.txtdsnname.Name = "txtdsnname"
        Me.txtdsnname.Size = New System.Drawing.Size(224, 21)
        Me.txtdsnname.TabIndex = 7
        Me.txtdsnname.Text = ""
        Me.txtdescription.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtdescription.Location = New System.Drawing.Point(152, 64)
        Me.txtdescription.Name = "txtdescription"
        Me.txtdescription.Size = New System.Drawing.Size(224, 21)
        Me.txtdescription.TabIndex = 8
        Me.txtdescription.Text = "MySQL ODBC 3.51 Driver DSN"
        Me.txtserver.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtserver.Location = New System.Drawing.Point(152, 88)
        Me.txtserver.Name = "txtserver"
        Me.txtserver.Size = New System.Drawing.Size(224, 21)
        Me.txtserver.TabIndex = 9
        Me.txtserver.Text = "localhost"
        Me.txtdatabase.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtdatabase.Location = New System.Drawing.Point(152, 112)
        Me.txtdatabase.Name = "txtdatabase"
        Me.txtdatabase.Size = New System.Drawing.Size(224, 21)
        Me.txtdatabase.TabIndex = 10
        Me.txtdatabase.Text = ""
        Me.txtusername.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtusername.Location = New System.Drawing.Point(152, 136)
        Me.txtusername.Name = "txtusername"
        Me.txtusername.Size = New System.Drawing.Size(224, 21)
        Me.txtusername.TabIndex = 11
        Me.txtusername.Text = "root"
        Me.txtpassword.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtpassword.Location = New System.Drawing.Point(152, 160)
        Me.txtpassword.Name = "txtpassword"
        Me.txtpassword.Size = New System.Drawing.Size(224, 21)
        Me.txtpassword.TabIndex = 12
        Me.txtpassword.Text = ""
        Me.txtport.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.txtport.Location = New System.Drawing.Point(152, 184)
        Me.txtport.Name = "txtport"
        Me.txtport.Size = New System.Drawing.Size(224, 21)
        Me.txtport.TabIndex = 13
        Me.txtport.Text = "3306"
        Me.cmdDsn.BackColor = System.Drawing.Color.DodgerBlue
        Me.cmdDsn.ForeColor = System.Drawing.SystemColors.ControlLightLight
        Me.cmdDsn.Location = New System.Drawing.Point(152, 208)
        Me.cmdDsn.Name = "cmdDsn"
        Me.cmdDsn.Size = New System.Drawing.Size(112, 72)
        Me.cmdDsn.TabIndex = 14
        Me.cmdDsn.Text = "&Create DSN"
        Me.cmdclose.BackColor = System.Drawing.Color.DodgerBlue
        Me.cmdclose.ForeColor = System.Drawing.SystemColors.ControlLightLight
        Me.cmdclose.Location = New System.Drawing.Point(152, 280)
        Me.cmdclose.Name = "cmdclose"
        Me.cmdclose.Size = New System.Drawing.Size(224, 48)
        Me.cmdclose.TabIndex = 16
        Me.cmdclose.Text = "E&xit"
        Me.GetDSN.BackColor = System.Drawing.Color.DodgerBlue
        Me.GetDSN.ForeColor = System.Drawing.SystemColors.ControlLightLight
        Me.GetDSN.Location = New System.Drawing.Point(264, 208)
        Me.GetDSN.Name = "GetDSN"
        Me.GetDSN.Size = New System.Drawing.Size(112, 72)
        Me.GetDSN.TabIndex = 15
        Me.GetDSN.Text = "&Get DSN"
        Me.Label7.BackColor = System.Drawing.Color.DodgerBlue
        Me.Label7.ForeColor = System.Drawing.SystemColors.ControlLightLight
        Me.Label7.Name = "Label7"
        Me.Label7.Size = New System.Drawing.Size(480, 24)
        Me.Label7.TabIndex = 18
        Me.Label7.Text = "Create Mysql DSN - By Vipul Patel ("
        Me.Label7.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        Me.AutoScaleBaseSize = New System.Drawing.Size(7, 14)
        Me.BackColor = System.Drawing.Color.White
        Me.ClientSize = New System.Drawing.Size(480, 333)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Label7, Me.GetDSN, Me.cmdclose, Me.cmdDsn, Me.txtport, Me.txtpassword, Me.txtusername, Me.txtdatabase, Me.txtserver, Me.txtdescription, Me.txtdsnname, Me.Label8, Me.Label5, Me.Label6, Me.Label3, Me.Label4, Me.Label2, Me.Label1})
        Me.Font = New System.Drawing.Font("Verdana", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.KeyPreview = True
        Me.Name = "frmmysqlDSN"
        Me.Text = "Create Mysql DSN - By Vipul Patel ("

    End Sub

#End Region

    Private Sub cmdDsn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDsn.Click
        Dim strdriverodbc As New String(Space(255))

        If txtdsnname.Text = "" Then
            MsgBox("Enter System DSN Name.", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
        End If

        If txtdescription.Text = "" Then
            MsgBox("Enter Description.", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
        End If

        If txtserver.Text = "" Then
            MsgBox("Enter Server Name.", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
        End If

        If txtdatabase.Text = "" Then
            MsgBox("Enter Database Name.", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
        End If

        If txtusername.Text = "" Then
            MsgBox("Enter Username Name.", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
        End If

        'check that mysql ODBC drive is installed or not
        If checkMySQLDriver(strdriverodbc) = False Then
            MsgBox("MySQL ODBC 3.51 Driver Is Not Installed." + vbCrLf + " Please Install The MySQl ODBC 3.51 Driver First.", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
        End If

        'check that the dsnname is already exist ?
        If MySQLDSNWanted(Trim(txtdsnname.Text)) = True Then
            MsgBox("DSN Name Already Exists." + vbCrLf + " Please Enter Another Name", MsgBoxStyle.Information, "Create Mysql DSN")
            Exit Sub
            MakeMySQLDSN(Trim(txtdatabase.Text), Trim(txtdsnname.Text), Trim(txtdescription.Text), strdriverodbc, Trim(txtusername.Text), Trim(txtpassword.Text), Trim(txtserver.Text), Trim(txtport.Text), 3, "")
        End If

        strdriverodbc = "C:\WINDOWS\System32\myodbc3.dll"
    End Sub

    Private Function MakeMySQLDSN(ByVal DB_Name As String, _
                                ByVal DSN As String, _
                                ByVal Description As String, _
                                ByVal Driver_Name As String, _
                                ByVal userid As String, _
                                ByVal password As String, _
                                ByVal Server_Name As String, _
                                ByVal port As String, _
                                ByVal stroption As String, _
                                ByVal stmt As String _
                                ) As Boolean

        Dim lResult As Long
        Dim hKeyHandle As Long
        Dim msg1 As String

        Dim regHandle As RegistryKey ' Stores the Handle to Registry in which values need to be set

        Dim reg As RegistryKey = Registry.LocalMachine
        Dim conRegKey1 As String = "SOFTWARE\ODBC\ODBC.INI\" & DSN
        Dim conRegKey2 As String = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"

            regHandle = reg.CreateSubKey(conRegKey1)
            regHandle.SetValue("Database", DB_Name)
            regHandle.SetValue("Description", Description)
            regHandle.SetValue("Driver", Driver_Name)
            regHandle.SetValue("Option", stroption)
            regHandle.SetValue("Password", password)
            regHandle.SetValue("Port", port)
            regHandle.SetValue("Server", Server_Name)
            regHandle.SetValue("Stmt", stmt)
            regHandle.SetValue("User", userid)

            regHandle = reg.CreateSubKey(conRegKey2)
            regHandle.SetValue(DSN, "MySQL ODBC 3.51 Driver")
            MsgBox("Successfully created the System DSN." & vbCrLf & "You can view the created DSN by clicking on Get DSN button.", MsgBoxStyle.Information, "Create System DSN")
        Catch err As Exception
        End Try
    End Function

    Private Sub GetDSN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GetDSN.Click
        Call Shell("rundll32.exe shell32.dll,Control_RunDLL ODBCCP32.cpl @2, 5")
    End Sub

    Private Sub frmmysqlDSN_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Function checkMySQLDriver(ByRef DriverODBC As String) As Boolean
        Dim regHandle As RegistryKey            ' Stores the Handle to Registry in which values need to be set
        Dim reg As RegistryKey = Registry.LocalMachine
        Dim conRegKey1 As String = "SOFTWARE\ODBC\ODBCINST.INI\MySQL ODBC 3.51 Driver"
            regHandle = reg.OpenSubKey(conRegKey1)
            If regHandle.ValueCount > 0 Then
                DriverODBC = regHandle.GetValue("Driver")
                checkMySQLDriver = True
                checkMySQLDriver = False
            End If
        Catch err As Exception
        End Try
    End Function

    Private Function MySQLDSNWanted(ByVal strdsnName As String) As Boolean
        Dim reghandle As RegistryKey
        Dim reg As RegistryKey = Registry.LocalMachine
        Dim conRegKey1 As String = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\"
        Dim tmpdsnvalue As String
            reghandle = reg.OpenSubKey(conRegKey1)
            If reghandle.ValueCount > 0 Then
                tmpdsnvalue = reghandle.GetValue(strdsnName)
                If tmpdsnvalue = "" Then
                    MySQLDSNWanted = False
                    MySQLDSNWanted = True
                End If
                MySQLDSNWanted = False
            End If
        Catch err As Exception
        End Try
    End Function

    Private Sub cmdclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclose.Click
    End Sub

    Private Sub frmmysqlDSN_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles MyBase.MouseDown
        If e.Button = MouseButtons.Right Then
            MsgBox("Create Mysql DSN - By Vipul Patel (", MsgBoxStyle.Information, "Create Mysql DSN - By Vipul Patel (")
        End If
    End Sub
End Class

Any idea will help, thanks
Updated 13-May-13 5:09am

You're not explaining the problem and how you expect this stuff to work at all.

You need admin priv's because you're writing to HKEY_LOCAL_MACHINE. Normal users cannot do that and there is no way around it.
Share this answer
Maciej Los 13-May-13 15:14pm    
Are you sure? As good as i remember normal users can add/edit only 'user DSN'. Admins can add/edit 'system DSN'.
Please, read this:[^]
Dave Kreskowiak 13-May-13 16:02pm    
Yes, I'm sure. Users cannot modify DSNs stored under HKEY_LOCAL_MACHINE, but CAN read them.

He's complaining that the code to modify a system DSN needs admin permissions. Well, yeah, it's a system DSN, only modifiable by admins.
Maciej Los 13-May-13 16:05pm    
OK, now i understand your answer. Previously i did not read question to the end. OP wants to create system DSN.
Sorry and thank you for response ;)

BTW: +5!
Dear Sir :
Thanks for your code.
It is true you need permissions to write in the LOCAL_MACHINE , but you can save the DSN in the CURRENT_USER (Registry.CurrentUser)
Ichecked the drive using the local machine registry key and create the dns in the CurrentUser

It works fine for me.

I beg your pardon for my English.
Share this answer
CHill60 8-May-14 19:20pm    
If you meant to respond to either the original post or the solution then please use the "Have a Question or Comment?" link next to the relevant post, otherwise that poster will not be notified that you have responded. Avoid posting "solutions" that are really comments as they will attract downvotes.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900