Problem with `quotes` in parameters collection RSS

• • Reply
  

  cms9651

  Member

  184 Points

  599 Posts

  Problem with `quotes` in parameters collection

  Nov 09, 2012 05:05 PM|LINK

  Hi all.

  This is my partial code behind with db mysql and parameters:

  cmd.Parameters.Clear();
                      cmd.CommandText = "UPDATE tbl_g ";
                      cmd.CommandText = cmd.CommandText + "SET D = '1' ";
                      cmd.CommandText = cmd.CommandText + "WHERE ";
                      cmd.CommandText = cmd.CommandText + " 1 ";
                      cmd.CommandText = cmd.CommandText + "AND DAA = ? ";
                      cmd.CommandText = cmd.CommandText + "AND Node = ? ";
                      cmd.CommandText = cmd.CommandText + "ORDER BY ";
                      cmd.CommandText = cmd.CommandText + " DAA ASC ";
                      cmd.CommandText = cmd.CommandText + "LIMIT ?; ";
                      cmd.Parameters.Add("param1", strDAA);
                      cmd.Parameters.Add("param2", strNode);
                      cmd.Parameters.Add("param3", strQ.Replace("''", "").Trim());
                      cmd.Connection = myConnectionString;
                      myConnectionString.Open();
                      cmd.ExecuteNonQuery();
                      myConnectionString.Close();

  The query update print error because I use in the clause LIMIT the value of param3 strQ.

  ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.24]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 ''5'' at line 1

  The problem are the `quotes` in the param3 strQ.

  I tried with 

  cmd.Parameters.Add("param3", strQ.Replace("''", "").Trim());

  But doesn't work... can you help me?
  
  
  

  • Moved by bbcompent1 on Nov 09, 2012 06:13 PM
• • Reply
  

  Mikesdotnett...

  All-Star

  155593 Points

  19979 Posts

  Moderator

  MVP

  Re: Problem with `quotes` in parameters collection

  Nov 09, 2012 07:43 PM|LINK

  You don't need to escape quotes in parameter values. They are treated as literal strings:

  cmd.Parameters.Add("param3", strQ.Trim());

  Regards, Mike
  Web Pages CMS | My Site | Twitter
  • Marked as answer by cms9651 on Nov 10, 2012 09:24 PM
• • Reply
  

  oned_gk

  All-Star

  35838 Points

  7323 Posts

  Re: Problem with `quotes` in parameters collection

  Nov 10, 2012 01:21 AM|LINK

  cmd.CommandText = cmd.CommandText + "WHERE ";
                      cmd.CommandText = cmd.CommandText + " 1 ";

  
  combined text will be : " ... WHERE 1 ...."

  Can mysql using where condition like above?

  Usualy : .... WHERE Col1 = 1  ....

  Mybe you set wrong command

  Programming to simplify, dont look for hard way
  Suwandi - Non Graduate Programmer
  • Marked as answer by cms9651 on Nov 10, 2012 09:24 PM
• • Reply
  

  cms9651

  Member

  184 Points

  599 Posts

  Re: Problem with `quotes` in parameters collection

  Nov 10, 2012 09:23 PM|LINK

  Ok thanks, I solved:

  cmd.Parameters.Clear();
                      cmd.CommandText = "UPDATE tbl_g ";
                      cmd.CommandText = cmd.CommandText + "SET D = '1' ";
                      cmd.CommandText = cmd.CommandText + "WHERE ";
                      cmd.CommandText = cmd.CommandText + "DAA = ? ";
                      cmd.CommandText = cmd.CommandText + "AND Node = ? ";
                      cmd.CommandText = cmd.CommandText + "ORDER BY ";
                      cmd.CommandText = cmd.CommandText + "DAA ASC ";
                      cmd.CommandText = cmd.CommandText + "LIMIT ?; ";
                      cmd.Parameters.Add("param1", strDAA);
                      cmd.Parameters.Add("param2", strNode);
                      cmd.Parameters.Add("param3", strQ.Trim());
                      cmd.Connection = myConnectionString;
                      myConnectionString.Open();
                      cmd.ExecuteNonQuery();
                      myConnectionString.Close();