VB.NET connecting to MySQL [ODBC]

[coolant]

I tried reading a few tutorials on how to setup ODBC and all that. Does anybody have a sure fire way of achieving this? I am completly lost.

Thanks! :)

[azcodemonkey]

There is a native .NET connector for MySQL, so there's no need for ODBC.

http://dev.mysql.com/downloads/connector/net/1.0.html

[coolant]

  weenur said:

There is a native .NET connector for MySQL, so there's no need for ODBC.

http://dev.mysql.com/downloads/connector/net/1.0.html

I tried that. Can you give me an example or show me one? Thanks.

[coolant]

Last question...

I have the following code :

'Insert

MyCommand.CommandText = "INSERT INTO my_vb_net VALUES(30, computer.FullDomainName)"
Console.WriteLine("INSERT, Total rows affected:" & MyCommand.ExecuteNonQuery())

xml.WriteStartElement("Information")
xml.WriteElementString("Name", computer.FullDomainName)
xml.WriteElementString("Group", computer.RequestedTargetGroupName)
xml.WriteEndElement()

It will write the computer name to the xml file, but instead of inserting the computer name into the SQL db, it just puts computer.FullDomainName. I know with PHP you'd just put " . computer.FullDomainName . ", but unsure in .NET

Thanks :)

[azcodemonkey]

OK. I finally got around to this. Sorry for the delay.

To use the .NET provider, download the setup and install it. In your project, add a reference to the MySql.Data.dll assembly. To use, well, here's an example of inserting, updating, getting, and deleting.

I'm using VB.NET 2005, so if you're not, sorry. You won't be able to use the Using statement. You'll have to use the Try Catch Finally to ensure your connection closes.

In the form load handler, i'm also showing 4 ways to get the hostname. The last one assumes you know at least the hostname or the IP, so it's a little redundant.

Imports System.Configuration
Imports MySql.Data.MySqlClient
Imports System.Text

<System.Runtime.InteropServices.ComVisible(False)> _
Public Class Form1
	Private conStr As String

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		' Getting the connection string from my app.config.
		' "server=localhost;user id=<userID>; password=<password>; database=test_db; pooling=false"
		Dim config As Configuration
		config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
		conStr = config.ConnectionStrings.ConnectionStrings("MySQL").ConnectionString

		' Use the My namespace
		Console.WriteLine(My.Computer.Name)
		' Use System.Environment
		Console.WriteLine(System.Environment.MachineName)
		' Use the Dns class
		Console.WriteLine(System.Net.Dns.GetHostName())
		' Use the Dns class to get more detailed info
		Dim host As System.Net.IPHostEntry
		host = System.Net.Dns.GetHostEntry("www.microsoft.comConsole.WriteLine(host.HostName)
	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		' Create a record
		Using con As New MySqlConnection(conStr)
			con.Open()
			Dim cmd As New MySqlCommand()
			cmd.Connection = con
			cmd.CommandType = CommandType.StoredProcedure
			cmd.CommandText = "usp_insertupdate"
			Dim p As New MySqlParameter
			p.ParameterName = "_id"
			p.Value = 0
			cmd.Parameters.Add(p)
			p = New MySqlParameter("_fname", "John")
			cmd.Parameters.Add(p)
			p = New MySqlParameter("_lname", "Hamilton")
			cmd.Parameters.Add(p)
			p = New MySqlParameter("_age", 36)
			cmd.Parameters.Add(p)

			' Get back the id for the row
			Dim o_id As Object = cmd.ExecuteScalar()

			' Store the id in the button tag... cheesey, but hey, it's only an example
			Button1.Tag = o_id
		End Using
		Button1.Enabled = False
		Button3.Enabled = True
		Button2.Enabled = True
		Button4.Enabled = True
	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		' Get the record by id
		Using con As New MySqlConnection(conStr)
			con.Open()
			Dim cmd As New MySqlCommand("usp_getbyid", con)
			Dim p As New MySqlParameter("_id", Button1.Tag)
			cmd.CommandType = CommandType.StoredProcedure
			cmd.Parameters.Add(p)

			Dim r As MySqlDataReader
			r = cmd.ExecuteReader()

			' iterate throught the reader
			' bind to a ListBox... cheese, pure cheese...
			Dim sb As New StringBuilder(1024)
			While r.Read
				sb.AppendLine(String.Format("ID: {0}, First: {1}, Last: {2}, Age: {3}", _
				 r.GetInt32(0), r.GetString(1), r.GetString(2), r.GetInt32(3)))
			End While
			Dim s() As String = sb.ToString().Split(Environment.NewLine)
			ListBox1.DataSource = s

		End Using
	End Sub

	Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
		' Update the record.
		Using con As New MySqlConnection(conStr)
			con.Open()
			Dim cmd As New MySqlCommand("usp_insertupdate", con)
			cmd.CommandType = CommandType.StoredProcedure
			Dim p As New MySqlParameter("_id", Button1.Tag)
			cmd.Parameters.Add(p)
			p = New MySqlParameter("_fname", "Juan")
			cmd.Parameters.Add(p)
			p = New MySqlParameter("_lname", "DeMarco")
			cmd.Parameters.Add(p)
			p = New MySqlParameter("_age", 25)
			cmd.Parameters.Add(p)

			cmd.ExecuteNonQuery()
		End Using
	End Sub

	Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
		' Delete the record
		Using con As New MySqlConnection(conStr)
			con.Open()
			Dim cmd As New MySqlCommand("usp_delete", con)
			cmd.CommandType = CommandType.StoredProcedure
			Dim p As New MySqlParameter("_id", Button1.Tag)
			cmd.Parameters.Add(p)

			cmd.ExecuteNonQuery()

			Button1.Enabled = True
			Button3.Enabled = False
			Button2.Enabled = False
			Button4.Enabled = False

		End Using
	End Sub
End Class