sql - C# + MySQL prepared statement w/ON DUPLICATE KEY UPDATE problems -


I have a C # application that is having problems with a duplicate key update in MySQL using a prepared statement is.

When I use the connection variable mypcmd.ExecuteNonQuery (); Check back at a breakpoint before false , mypcmd.IsPrepared . The query does not do anything (as the primary key it should be updated in advance) and no exception is thrown. mypcmd.Parameters.count = 25 As it should be and if I drill down in the data view, the value is OK.

When the query goes through, the key is updated in 0

  • MySQL Server version 5.0.51A-24 + Lenny5
  • all parameter name (iefname :? Fname, middlename :? Middlename etc.) MySQL C # assembly version (mysql.data.dll) 6.3.6.0
  • WPF app is .NET 4.0

    code as follows: 

      insert const string sqlIpatients = @ "` tblpatients` ( `Nanbr`,` Fname`, `Middlename`, Srnem`` ` Title ',' contact number ',' mobile ', `sex`,` dob`, `homeman`,` hosse', `address1`,` address 2`, 'address 3`,' address 4`, 'post Code`, `Notes`,` Home`, 'SP1`,' SP2`, 'SP3', ('' Number '', 'Fname', '? Midnight', 'Surname', '' Title ' , '' Contact ',' 'mobilino' ',' spell ',' sex ','? Dub ',' house name ',' hosenno ','? Ed1 ','? Address2 ','? Address3 ' , '? Address4', 'Pincode', 'Notes',' Home ','? Sp1 ','? Sp2 ','? Sp3 ','? Sp4 ','? Sp5 ','? Sp6 ',' 'Ama IL') Key update of duplicate `number` = value (` number`), `Fname` = value (` FNA`), `middle length '= value (` middlename'), `surname` = value (` surname `),` Title` = value (`title`),` contact number` = value (`concordance 'Mobilin' = Value (`Mobilino`),` gender` = value (`gender`),` dub` = value (`dob`),` hosinum` = value (`housewife`),` HOUSEIN '= value (`hussano),` address1` = value (`address1`),` address2` = electricity (`address2`),` address3` = value (`address3`),` address 4` = value (`address 4`),` postcode` = value (`postcode '),` notes` = value (`notes`),` home` = value (`` home'), `sp1` = vALUES (`Sp1`),` sp2` = vALUES (`sp2`),` sp3` = vALUES (`3x3),` sp4` = vALUES (`sp4`),` sp5` = vALUES (`sp5`) `Sp6` = VALUES (` sp6`), `email` = VALUES (` email`) ";   
     ...    MySqlCommand mypcmd = New MySqlCommand (sqlIpatients, myConn2); Mypcmd.Prepare (); Mypcmd.Parameters.Add ("number", MySqlDbType.UInt32); Mypcmd.Parameters.Add ("? Fname", MySqlDbType.VarChar, 20); Mypcmd.Parameters.Add ("middlename", MySqlDbType.VarChar, 20); Mypcmd.Parameters.Add ("? Nickname", MySqlDbType.VarChar, 40); Mypcmd.Parameters.Add ("Title", MySqlDbType.VarChar, 6); Mypcmd.Parameters.Add ( "" contactno ", MySqlDbType.VarChar, 40); Mypcmd.Parameters.Add ("? Mobileno ", MySqlDbType.VarChar, 40); Mypcmd.Parameters.Add (" gender ", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ( "? Dob", MySqlDbType.Date); Mypcmd.Parameters.Add ( "Housnam", MySqlDbType.VarChar, 20); Mypcmd.Parameters.Add ( "houseno", MySqlDbType.VarChar, 20); Mypcmd.Parameters.Add ( "? address 1", MySqlDbType.TinyText); Mypcmd.Parameters.Add ( "? Address2", MySqlDbType.TinyText); Mypcmd.Parameters.Add ( "? address 3", MySqlDbType. TinyText); Mypcmd.Parameters.Add ( "? Address4", MySqlDbType.TinyText); Mypcmd.Parameters.Add ( "? Pincode", MySqlDbType.TinyText); Mypcmd.Parameters.Add ( "" notes ", MySqlDbType.Text) ; Mypcmd.Parameters.Add ("Home", MySqlDbType.UInt16); Mypcmd.Parameters.Add ("? Sp1", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ("? Sp2", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ("? Sp3", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ("? Sp4", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ("? Sp5", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ("? Sp6", MySqlDbType.VarChar, 1); Mypcmd.Parameters.Add ("Email", MySqlDbType.Text); Var prodr = procmd.ExecuteReader (); While (prodr.Read ()) {foreach (MySqlParameter p in mypcmd.Parameters) {p.value = prodr [p.ParameterName.Replace ("?", "" "]] ToString () Trim (); }} Mypcmd.ExecuteNonQuery ();   
     What am I missing? Please help!   
     
     
     Just got to know what was the problem. The SQL Query will remove the single quotes now works like a charm.  
     How easy it was decided, it hopes that it will leave anyone out of my headache.

Comments

Post a Comment