Bug #18130 Error in INSERT statement
Submitted: 10 Mar 2006 10:32 Modified: 7 Apr 2006 10:00
Reporter: Jaroslav Zeman Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:Server 5.0 OS:Windows (Win XP 2000)
Assigned to: CPU Architecture:Any

[10 Mar 2006 10:32] Jaroslav Zeman
Description:
1. Using vb.net, MySQL Connector Net 1.0.7\bin\.NET 2.0\MySql.Data.dll
2. DDL Table:
CREATE TABLE `lock_elements` (
  `id_author` int(11) unsigned NOT NULL,
  `activity` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `lock_element_id` int(11) unsigned NOT NULL,
  `type_element` char(1) collate utf8_czech_ci NOT NULL,
  PRIMARY KEY  (`lock_element_id`,`type_element`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci
3. SQL query: 
INSERT INTO lock_elements (id_author, type_element, lock_element_id) VALUES (?id_author, ?type_element, ?lock_element_id)
4. code:
Connection = New MySqlConnection
Connection.ConnectionString = "my setting, connect to local computer"
Connection.Open()
Command.Connection = Connection
Command.CommandText = "INSERT INTO lock_elements (id_author, type_element, lock_element_id) VALUES (?id_author, ?type_element, ?lock_element_id)"
'in input array is my inserting values
If IsArray(inputparams) Then
  If Not ParamsIsAdded Then
    For i As Integer = 0 To UBound(inputparams, 1)
      Command.Parameters.Add(Params(i), inputparams(i))
    Next
    ParamsIsAdded = True
  Else
    For i As Integer = 0 To UBound(inputparams, 1)
      Command.Parameters(i).Value = inputparams(i)
    Next
  End If
End If
Command.ExecuteNonQuery()
EventExecuteNow()
5. Error:
When I am insert inputparams() = {21, "M", 102}
In table is 21, "M", 10, In table must be 21, "M", 102
6. Remark:
Schema with table above contains 76 tables (mixed engines)
I am test some name column in table, still identical result
I am test some order column in table, still identical result
I am test some engine, still identical result

How to repeat:
Still (first execute or repeat execute)

Suggested fix:
None
[10 Mar 2006 12:18] Valeriy Kravchuk
Thank you for a PROBLEM REPORT. Please, specify, what exact version of MySQL server do you use.
[10 Mar 2006 16:11] Jaroslav Zeman
MyServer v. 5.0.18-nt
[10 Mar 2006 16:41] Jaroslav Zeman
Array inputparams is type Object in vb.net code. So-called, code: Command.Parameters.Add(Params(i), inputparams(i)), must be convert object inputparams(…) to Integer (because according to ddl table column is Integer). During debugging program conversion is made, debugger reports: value is 102 in parameter, and is type Int32, but in table will be store only value 10. In more 100 other SQL command is this conversion without problem. When before Command.Parameters.Add(Params(i), inputparams(i)), for this case, is anywhere hard insert value over this code: inputparams(…) = CInt(...), afterwards is correct value putted to table from inputparams(…).
[7 Apr 2006 10:00] Tonci Grgin
Sorry, I was unable to repeat your problem with latest Connector/NET. If you're still expiriencing it, please post complete code which reproduces the error. Attached is xls file with filled table.
              // loop 
              //inserting a value with parameters
              cmd.Parameters.Add("?id_author", MySqlDbType.Int32);
              cmd.Parameters["?id_author"].Value = ida;

              cmd.Parameters.Add("?type_element", MySqlDbType.Char);
              cmd.Parameters["?type_element"].Value = "M";

              cmd.Parameters.Add("?lock_element_id", MySqlDbType.UInt32);
              cmd.Parameters["?lock_element_id"].Value = edid;

              cmd.CommandText = 
                  "INSERT INTO lock_elements (id_author, type_element, lock_element_id) VALUES (?id_author, ?type_element, ?lock_element_id)";

              cmd.ExecuteNonQuery();
              // inc integer params
[7 Apr 2006 10:02] Tonci Grgin
Program output

Attachment: lock_elements.xls (application/vnd.ms-excel, text), 8.68 KiB.