VB .NET to mySQL help 1

[smanny]

I am writing a desktop application that needs to connect to a linux machine running mySQL. I basically want to be able to add data to a table. Any assistance would be appreciated. I have a ton of tidbits on how to do this, but no complete pictures. Anyone who has done this, I could use some serious help! Thanks!

- Shane

 

[darpankumar]

You can use the

http://sourceforge.net/project/showfiles.php?group_id=47556&release_id=124469

link to connect to the MySQL from ADO.Net.

Yours Faithfully,

Darpan Kumar Gogia
darpan.kumar@india.birlasoft.com

 

[smanny]

Thanks for the link! Looks interesting, but unfortunately I have NO idea how to use it. I am looking into it, but would appreciate additional information. Thanks again!

- Shane

 

[krisbrixon]

adding data into a database can be as simple as:

Imports System.Data
Public Class frmSimpleADO
Private Sub AddData()
Dim sSQL_Script As String = "INSERT INTO tblSample (Memo1) VALUES ('hi')"
Dim sConnection_String As String = "Driver={mySQL};Server=mySrvName;Option16834;Database=mydatabase;"
Dim oConnection As New OleDb.OleDbConnection(sConnection_String)
Dim oCommand As New OleDb.OleDbCommand(sSQL_Script, oConnection)
oCommand.ExecuteNonQuery()
End Sub
End Class

You can make adding data a whole lot more complex, but to add data I think this is probably the least amount of code you can get away with.

Here are two links to help you figure out the connection string for mySQL:

http://www.connectionstrings.com

http://www.able-consulting.com/ADO_Conn.htm

If this does not help then try to be more specific in where you are having problems.

Kris

 

[smanny]

Kris,

I gave you a star for that one. Your code and the links you provided are what I need I think. Best information I have found yet. Thanks!

If I need more information, I will more specific where I am getting stuck. For now, I just needed a frame of reference on where to even begin. I think this provides enough to go on.

- Shane

 

[smanny]

Need additional help. How can you test to see if the connection to the db was successful or not? Any advice would be appreciated.

 

[krisbrixon]

Try this:

Imports System.Data
Public Class frmSimpleADO
Private Sub AddData()
Dim iReturnValue As Integer
Dim sSQL_Script As String = "INSERT INTO tblSample (Memo1) VALUES ('hi')"
Dim sConnection_String As String = "Driver={mySQL};Server=mySrvName;Option16834;Database=mydatabase;"
Dim oConnection As New OleDb.OleDbConnection(sConnection_String)
Dim oCommand As New OleDb.OleDbCommand(sSQL_Script, oConnection)
Try
iReturnValue = oCommand.ExecuteNonQuery()
MsgBox(iReturnValue & " rows affected.&quot
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class


Kris

 

[krisbrixon]

one change:

MsgBox(iReturnValue & " rows affected.&quot
to
MsgBox(iReturnValue.ToString & " rows affected.&quot

 

[smanny]

With your code, I get the following error:

An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll

Additional information: An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

As you can tell I am a total novice at connecting to DBs. I am also connecting remotely to the linux mySQL server.

Thanks for the other info to on how to check.

 

[smanny]

I tried the EID dProvider dll and connected! Locally anyway. I was also able to do an INSERT. Can't seem to get a remote connection to work for some reason. Anyway, I am curious why your code didn't work. If you have an thoughts, drop me a message. Thanks!

- Shane

 

[krisbrixon]

it's not like I tested the code that I posted. I have never used mySQL. The code was to give you a direction to head in to solve your problem.

Kris