Add, Edit & Delete DSN using MySQL ODBC & VB.NET

Question

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.

VB

Imports Microsoft.Win32


Public Class frmmysqlDSN
    Inherits System.Windows.Forms.Form

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

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        '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
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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.SuspendLayout()
        '
        'Label1
        '
        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
        '
        'Label2
        '
        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
        '
        'Label3
        '
        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
        '
        'Label4
        '
        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
        '
        'Label5
        '
        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
        '
        'Label6
        '
        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
        '
        'Label8
        '
        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
        '
        'txtdsnname
        '
        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 = ""
        '
        'txtdescription
        '
        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"
        '
        'txtserver
        '
        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"
        '
        'txtdatabase
        '
        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 = ""
        '
        'txtusername
        '
        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"
        '
        'txtpassword
        '
        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 = ""
        '
        'txtport
        '
        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"
        '
        'cmdDsn
        '
        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"
        '
        'cmdclose
        '
        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"
        '
        'GetDSN
        '
        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"
        '
        'Label7
        '
        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 (vipul_p44@yahoo.com)"
        Me.Label7.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'frmmysqlDSN
        '
        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 (vipul_p44@yahoo.com)"
        Me.ResumeLayout(False)

    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")
            txtdsnname.Focus()
            Exit Sub
        End If

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

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

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

        If txtusername.Text = "" Then
            MsgBox("Enter Username Name.", MsgBoxStyle.Information, "Create Mysql DSN")
            txtusername.Focus()
            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")
            txtdsnname.Focus()
            Exit Sub
        Else
            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"

        Try
            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.Close()
            reg.Close()

            regHandle = reg.CreateSubKey(conRegKey2)
            regHandle.SetValue(DSN, "MySQL ODBC 3.51 Driver")
            regHandle.Close()
            reg.Close()
            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
            MsgBox(err.Message)
        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"
        Try
            regHandle = reg.OpenSubKey(conRegKey1)
            If regHandle.ValueCount > 0 Then
                DriverODBC = regHandle.GetValue("Driver")
                checkMySQLDriver = True
            Else
                checkMySQLDriver = False
            End If
        Catch err As Exception
            MsgBox(err.Message)
        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
        Try
            reghandle = reg.OpenSubKey(conRegKey1)
            If reghandle.ValueCount > 0 Then
                tmpdsnvalue = reghandle.GetValue(strdsnName)
                If tmpdsnvalue = "" Then
                    MySQLDSNWanted = False
                Else
                    MySQLDSNWanted = True
                End If
            Else
                MySQLDSNWanted = False
            End If
        Catch err As Exception
            MsgBox(err.Message)
        End Try
    End Function

    Private Sub cmdclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdclose.Click
        End
    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 (vipul_p44@yahoo.com)", MsgBoxStyle.Information, "Create Mysql DSN - By Vipul Patel (vipul_p44@yahoo.com)")
        End If
    End Sub
End Class

Any idea will help, thanks

Posted 13-May-13 5:07am

JMAM

Updated 13-May-13 5:09am

v2

Add a Solution

2 solutions

Solution 2

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.

Posted 8-May-14 13:16pm

santidat

Comments

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Accepted Answer · 2013-05-13T08:52:00

Solution 1

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.

Posted 13-May-13 8:52am

Dave Kreskowiak

Comments

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: http://en.wikipedia.org/wiki/Data_source_name[^]

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!