Greetings:
I am using 3.20.9.0 version and MySql 5. and trying to use the insert sql command from the mysqlcommandbuilder.
This is the code generated by vb.net 2003
'mySqlInsertCommand1
'
Me.mySqlInsertCommand1.CommandText = "INSERT INTO state(state_id, state_descrioption) VALUES (:state_id, :state_descrioption)"
Me.mySqlInsertCommand1.Connection = Me.MySqlConnection1
Me.mySqlInsertCommand1.Name = "mySqlInsertCommand1"
Me.mySqlInsertCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("state_id", CoreLab.MySql.MySqlType.VarChar, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, ""))
Me.mySqlInsertCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("state_descrioption", CoreLab.MySql.MySqlType.VarChar, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, ""))
Me.mySqlInsertCommand1.UpdatedRowSource = System.Data.UpdateRowSource.None
When I try to execute this command using the following lines:
mySqlInsertCommand1.Parameters.Add(":state_id", TextBox1.Text)
mySqlInsertCommand1.Parameters.Add(":state_descrioption", TextBox2.Text)
Try
MySqlConnection1.Open()
mySqlInsertCommand1.ExecuteNonQuery()
MySqlConnection1.Close()
Catch myerror As MySqlException
MsgBox("There was an error creating record: " & myerror.Message)
End Try
I get the following error:
An unhandled exception of type 'System.InvalidOperationException' occurred in corelab.mysql.dll
Additional information: ':state_id' parameter is missing at the statement
Can anyone advise what is wrong?
Thanks
Problem with mysqlcommandbuilder
Hi.
The problem is that you create parameters collection twice. Moreover, when you assign any value to a parameter use their names without colon (":"). E.g.:
Or you can do it by the number of the parameter in the collection. The following statements are equal:
(Don't forget that parameters collection is zero-based)
Take a look at your code corrected accordingly to preceding notices:
Good luck
The problem is that you create parameters collection twice. Moreover, when you assign any value to a parameter use their names without colon (":"). E.g.:
Code: Select all
MySqlInsertCommand1.Parameters("state_id").Value = TextBox1.Text
Code: Select all
MySqlInsertCommand1.Parameters("state_id").Value = TextBox1.Text
MySqlInsertCommand1.Parameters(0).Value = TextBox1.Text
Take a look at your code corrected accordingly to preceding notices:
Code: Select all
Me.MySqlInsertCommand1.CommandText = "INSERT INTO state(state_id, state_descrioption) VALUES (:state_id, :state_descrioption)"
Me.mySqlInsertCommand1.Connection = Me.MySqlConnection1
Me.mySqlInsertCommand1.Name = "mySqlInsertCommand1"
Me.mySqlInsertCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("state_id", CoreLab.MySql.MySqlType.VarChar, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, ""))
Me.mySqlInsertCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("state_descrioption", CoreLab.MySql.MySqlType.VarChar, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, ""))
Me.mySqlInsertCommand1.UpdatedRowSource = System.Data.UpdateRowSource.None
MySqlInsertCommand1.Parameters("state_id").Value = TextBox1.Text
MySqlInsertCommand1.Parameters("state_descrioption").Value = TextBox2.Text
Try
MySqlConnection1.Open()
mySqlInsertCommand1.ExecuteNonQuery()
MySqlConnection1.Close()
Catch myerror As CoreLab.MySql.MySqlException
MsgBox("There was an error creating record: " & myerror.Message)
End Try