MySqlParameters In Query

Hi Guys and Galls

I’m trying to update a table in mysql database using the following code

[SIZE=“1”]Public Sub InsertUsers(ByVal sender As Object, ByVal e As EventArgs)
Dim sqlInsert As String
sqlInsert = "INSERT INTO tblTestComp(strUserName, strPassword, DOB) "
sqlInsert += “VALUES(?, ?, ?)”

Dim objCmd As MySqlCommand
objCmd = New MySqlCommand(sqlInsert, objConnMySQL)
Dim i As Integer = 0

objConnMySQL.Open()
For i = 0 To 2
objCmd.Parameters.Add(New MySqlParameter())
Next

objCmd.Parameters(0).Value = “BillWordsWorth”
objCmd.Parameters(1).Value = “Kipper88”
objCmd.Parameters(2).Value = “1985-05-21”

objCmd.ExecuteNonQuery()
objConnMySQL.Close()

End Sub[/SIZE]

The error I get is Object reference not set to an instance of an object
on the line objCmd.Parameters.Add(New MySqlParameter())

When I change the connection, command and parameters to oledb objects and run against MS Access databse it works fine. I have been trying to amend it for 2 days now to work with MySQL objects but nothing seems to work

I would be very grateful for any ideas
Thanks

Hi Guys not sure this post belongs here or in MySQL forum but I’m trying to insert a row into MySQL database table with the insert statement

sqlInsert = "INSERT INTO tblTestComp(strUserName, strPassword, DOB) "
sqlInsert += “VALUES(?, ?, ?)”

Setting the parameters with:
objCmd.Parameters(0).Value = What Ever
objCmd.Parameters(1).Value = What Ever
objCmd.Parameters(2).Value = What Ever

Using MySql.Data.MySqlClient data client

Can anyone tell me if this is even possible or not
Many Thanks

I use basically the same technique very happily with a couple modifications. One, make sure the tblTestComp is the exact name of the table in your MySQL database and that strUserName, strPassword, and DOB are the exact column names in that table. Pretty straightforward.

Also, the VALUES(?, ?, ?) will cause problems because that’s specifically a SQL syntax for the Jet engine with powers Access. MySQL takes a syntax like…

         workingSqlModificationText += "VALUES ( @pages_title, @pages_filename, @pages_websiteid, @pages_urlpathid ) "

Finally, the .Value property of the Parameters collection is something I’d had problems with, though I can’t remember exactly what. I found several blogs and forum threads which raised the same issues and recommended AddWithValue instead. I haven’t had problems since I switched. I haven’t programmed VB in ages, but I think this is basically the changes you need to make.

[SIZE=1]Public Sub InsertUsers(string inputUserName, string inputPassword, string inputDOB)
Dim sqlInsert As String
sqlInsert = "INSERT INTO tblTestComp(strUserName, strPassword, DOB) "
sqlInsert += “VALUES(@UserName, @Password, @DOB)”

Dim objCmd As MySqlCommand
objCmd = New MySqlCommand(sqlInsert, objConnMySQL)

objConnMySQL.Open()

[/SIZE]objCmd.Parameters.AddWithValue(“@UserName”, inputUserName);
objCmd.Parameters.AddWithValue(“@Password”, inputPassword);
objCmd.Parameters.AddWithValue(“@DOB”, inputDOB);

[SIZE=1] objCmd.ExecuteNonQuery()
objConnMySQL.Close()

End Sub[/SIZE]

OK thanks Chroniclemaster1
I knew I could add the parameters by name as you suggested above which I have already done I was just wondering why I couldn’t use VALUES(?, ?, ?) as I have done in Access and SQL Server which you have explained as well

Thanks very much