Problem with mysqlcommandbuilder

rng5000 · Post by **rng5000** » Sat 18 Mar 2006 22:38

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

Alexey · Post by **Alexey** » Mon 20 Mar 2006 08:07

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.:

Code: Select all

MySqlInsertCommand1.Parameters("state_id").Value = TextBox1.Text

Or you can do it by the number of the parameter in the collection. The following statements are equal:

Code: Select all

MySqlInsertCommand1.Parameters("state_id").Value = TextBox1.Text
MySqlInsertCommand1.Parameters(0).Value = TextBox1.Text

(Don't forget that parameters collection is zero-based)

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

Good luck