Error in Inserting record in mysql using ODBC

[sealthiel_murice13]

I got a trouble in inserting record in mysql using ODBC).

this is my sample code:

myinsert.CommandText = "INSERT INTO supplier( ID, SUPPLIERCODE, BUSINESSNAME, CONTACTPERSON, CONTACTNUM1, CONTACTNUM2, CONTACTNUM3, EMAILADD, BUSINESSADD, OTHERADD ) VALUES(?XID, ?SCODE, ?BUSS, ?CONT, ?CONTNUM1, ?CONTNUM2, ?CONTNUM3, ?EADD, ?BUSSADD, ?OTHADD)"
myinsert.Parameters.AddWithValue("@XID", txtnum.Text)
myinsert.Parameters.AddWithValue("@SCODE", txtsuppliercode.Text)
myinsert.Parameters.AddWithValue("@BUSS", txtsuppliercompany.Text)
myinsert.Parameters.AddWithValue("@CONT", txtsuppliername.Text)
myinsert.Parameters.AddWithValue("@CONTNUM1", txtcont1.Text)
myinsert.Parameters.AddWithValue("@CONTNUM2", txtcont2.Text)
myinsert.Parameters.AddWithValue("@CONTNUM3", txtcont3.Text)
myinsert.Parameters.AddWithValue("@EADD", txtadd1.Text)
myinsert.Parameters.AddWithValue("@BUSSADD", txtadd2.Text)
myinsert.Parameters.AddWithValue("@OTHADD", txtadd3.Text)
Try
conn.Open()
myinsert.ExecuteNonQuery()
Catch myerror As Exception
MsgBox("There was an error updating the database: " & myerror.Message)
End Try


any help.. i really appreciate for your emmidiate respose.

 

[jmcilhinney]

Would you care to let us know what actually happens when you execute that code? Is there an error message? My first guess would be that your SQL contains parameter names like "?XID" while you're adding parameters to your command like "@XID". They don't look the same to me.

 

[sealthiel_murice13]

Exact error

hi,

the exact error displayed when i executed the program was:

There was an error updating database: ERROR[42000][MySQL][ODBC 5.1 Driver][mysqld-5.1.33-commuinity]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'XID,'x'SCODE,'x'BUSS,'x'CONT,'x'CONTNUM!,'x'CONTNUM2,'x'CONTNUM#,'x'EADD',at line 1.

thanks again.

 

[jmcilhinney]

Judging by that error message, the code you posted earlier isn't the code you actually executed. This is from your first post:

VALUES(?XID, ?SCODE, ?BUSS, ?CONT, ?CONTNUM1, ?CONTNUM2, ?CONTNUM3, ?EADD, ?BUSSADD, ?OTHADD)

This is from your most recent post:

the right syntax to use near 'XID,'x'SCODE,'x'BUSS,'x'CONT,'x'CONTNUM!,'x'CONTN UM2,'x'CONTNUM#,'x'EADD',at line 1

From that it looks like you mistyped your SQL code and hit Shift when entering the 3.

 

[cjard]

If youre using ODBC drivers, do they actually understand named parameters for MySql?

 

[jmcilhinney]

With what cjard posted in mind, I'd be inclined to use OleDb over Odbc if I had the choice. Even better, you might want to get the MySQL-specific ADO.NET provider from the MySQL web site.