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()
MyBase.New()
InitializeComponent()
End Sub
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
Private components As System.ComponentModel.IContainer
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()
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 (vipul_p44@yahoo.com)"
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 (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
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
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
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
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