mike_F1
asked on
VB.NET connection to MySQL
Having worked with JET and Access my entire life!, I have finally decided to take a look at SQL - in particular MySQL Sever version 4.1, MySQL Connector Net 1.0 (Net 1.1 , Visual Basic 2003).
Many examples I have seen have used ODBC to connect but I am currently looking at using the .NET reference provided with MySQL - perhaps some-one can tell me the Pros and Cons of these two methods - but that's an aside really.
My current problem is that this code simply does not connect. On the con.Open() command I get the exception: "specified cast is not valid."
Here's the code:
Imports MySql.Data.MySqlClient
....
Sub Main()
Dim Con As New MySqlConnection
Dim cmd As MySqlCommand
Con.ConnectionString = "server=localhost;database
Try
con.Open()
Catch ex As Exception
MsgBox("Can't open the database" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error!")
Exit Sub
End Try
.....
Many examples I have seen have used ODBC to connect but I am currently looking at using the .NET reference provided with MySQL - perhaps some-one can tell me the Pros and Cons of these two methods - but that's an aside really.
My current problem is that this code simply does not connect. On the con.Open() command I get the exception: "specified cast is not valid."
Here's the code:
Imports MySql.Data.MySqlClient
....
Sub Main()
Dim Con As New MySqlConnection
Dim cmd As MySqlCommand
Con.ConnectionString = "server=localhost;database
Try
con.Open()
Catch ex As Exception
MsgBox("Can't open the database" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error!")
Exit Sub
End Try
.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jr
I've had a go with the code above - I still get the same error. I think the clue to the solution must be in the error message I am getting: "specified cast is not valid."
Clearly a cast is being specified but that cast is not valid. Where is it specifying a cast? Is it something to do with the database itself?
BTW I couldn't open the pdf link my acrobat version 6 bombed every time I tried to open it.
Thanks anyway
I've had a go with the code above - I still get the same error. I think the clue to the solution must be in the error message I am getting: "specified cast is not valid."
Clearly a cast is being specified but that cast is not valid. Where is it specifying a cast? Is it something to do with the database itself?
BTW I couldn't open the pdf link my acrobat version 6 bombed every time I tried to open it.
Thanks anyway
ASKER
Here's an interesting thing...
If I take the 'Exit Sub' out of the Catch section it runs through and the rest of the program works great!
I'm working again but clearly the cast message is there for a reason. I do have an issue with BOOLEAN being converted to TINYINT(1) when I converted my Access DB to SQL could this be what is throwing up the message???
If I take the 'Exit Sub' out of the Catch section it runs through and the rest of the program works great!
I'm working again but clearly the cast message is there for a reason. I do have an issue with BOOLEAN being converted to TINYINT(1) when I converted my Access DB to SQL could this be what is throwing up the message???
ASKER
Update...
Cannot save any data because the connection is either not open or valid.
Doh!!
Cannot save any data because the connection is either not open or valid.
Doh!!
Your probelm would be solve. Plz visit the following site "www.connectionstrings.com". You would find a correct connection string for MYSQL. Download ODBC.NET drivers from microsoft side and try to use this.
ASKER
Actually the problem was due to a bug in mysql NET connector version 1.0.4. The newer version 1.0.5 works OK.
jr, you can have the points because I found the link to www.connectionstrings.com quite useful, and you were first to offer this link.
Thanks.
jr, you can have the points because I found the link to www.connectionstrings.com quite useful, and you were first to offer this link.
Thanks.
Sorry for the lag in answers here. I've been too busy at work lately.
I'm glad you got it to work, though, and yeah.. connectionstrings.com are quite usefull :)
-jr
I'm glad you got it to work, though, and yeah.. connectionstrings.com are quite usefull :)
-jr
ASKER