Call a stored procedure with parameter in c# and MySQL[Answered] RSS

• • Reply
  Golia

  Member

  63 Points

  129 Posts

  Call a stored procedure with parameter in c# and MySQL

  May 23, 2020 12:59 PM|Golia|LINK

  Hi gurus,

  I need your help.

  I can do a updating in my program and I try to do an update by call a created stored procedure from my database.

  in this example I have to get the value of the checkbox selected in a gridview and update it

  in the gridview one or more boxes can be checkbox

  My FirstSP

  DELIMITER $$
  
  DROP PROCEDURE IF EXISTS FirstSP$$
  
  CREATE PROCEDURE FirstSP(idUser char(4), oID INT (11))
  
  DECLARE idUsern CHAR (4);
  DECLARE IDn INT (11);
  
  SET @idUsern = idUser;
  SET @IDn = oID;
  
  SET @s = CONCAT('SELECT * FROM doTable_', idUser, 
                  ' WHERE idUser = ? AND ID = ?;');
  PREPARE stmt FROM @s;
  EXECUTE stmt USING @idUsern, @IDn;
  
  set @query = CONCAT(' UPDATE doTable_', idUser,
                      ' SET xExists = 1 WHERE idUser IN (?) AND ID IN (?);');
  PREPARE stmt FROM @query;
  EXECUTE stmt USING @idUsern, @IDn;
  
  END
  
  DELIMITER ;

  the stored procedure tested in the dabatase works but I can try for single user

  but tested in c# I have error

  I've tried with selected single or multiple checkboxes

  ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community]
  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 'FirstSP' at line 1 
  
  cmd.ExecuteNonQuery();

  In debug the return of parameters is

  S110
  16
  S110
  15

  I searched on google for some good example but I found nothing interesting.

  my code below

  foreach (GridViewRow row in gv.Rows)
  {
      if (row.RowType == DataControlRowType.DataRow)
      {
          CheckBox chkRow = (CheckBox)(row.Cells[0].FindControl("chkRow"));
  
          if (chkRow.Checked)
          {
              int oID = Convert.ToInt32((gv.DataKeys[row.RowIndex].Value));
  
              if (oID > 0)
              {
                  using (OdbcConnection con =
                      new OdbcConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
                  {
                      using (OdbcCommand cmd =
                          new OdbcCommand())
                      {                                
                          cmd.CommandText = "FirstSP";
                          cmd.CommandType = CommandType.StoredProcedure;
                          cmd.Parameters.AddWithValue("idUser", "S110");
                          cmd.Parameters.AddWithValue("oID", oID.ToString());
                          cmd.Connection = con;
                          con.Open();
                          cmd.ExecuteNonQuery();
                      }
                  }
              }
          }
      }
  }

• • Reply
  RJ BOY

  None

  0 Points

  3 Posts

  Re: Call a stored procedure with parameter in c# and MySQL

  May 23, 2020 05:29 PM|RJ BOY|LINK

  Hi, You need to cross-check your stored procedure. The rest code is correct.
• • Reply
  Golia

  Member

  63 Points

  129 Posts

  Re: Call a stored procedure with parameter in c# and MySQL

  May 23, 2020 06:17 PM|Golia|LINK

  thanks for reply.

  i've solved with

              foreach (GridViewRow row in gvdelete.Rows)
              {
                  if (row.RowType == DataControlRowType.DataRow)
                  {
                      CheckBox chkRow = (CheckBox)(row.Cells[0].FindControl("chkRow"));
  
                      if (chkRow.Checked)
                      {
                          int oID = Convert.ToInt32((gvdelete.DataKeys[row.RowIndex].Value));
  
                          sql = @String.Format(" CALL FirstSP(?, ?); ");
  
                          using (OdbcConnection con =
                              new OdbcConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
                          {
                              using (OdbcCommand cmd =
                                  new OdbcCommand(sql, con))
                              {
                                  try
                                  {
                                      cmd.Connection.Open();
                                      cmd.Parameters.AddWithValue("idUser", "S110");
                                      cmd.Parameters.AddWithValue("oID", oID.ToString());
                                      cmd.ExecuteNonQuery();
                                      BindData();
                                      Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('Rows deleted.');", true);
                                  }
                                  catch (Exception ex)
                                  {
                                      throw ex;
                                  }
                                  finally
                                  {
                                      cmd.Connection.Close();
                                  }
                              }
                          }
                      }
                      else
                      {
                          Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('Not selected.');", true);
                      }
                  }
              }

  but i don't understand because not working with

  cmd.CommandText = "FirstSP";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("idUser", "S110");
  cmd.Parameters.AddWithValue("oID", oID.ToString());
  cmd.Connection = con;
  con.Open();
  cmd.ExecuteNonQuery();

• • Reply
  yij sun

  Contributor

  3730 Points

  1424 Posts

  Re: Call a stored procedure with parameter in c# and MySQL

  May 26, 2020 09:21 AM|yij sun|LINK

  Hi Golia,

  As your codes,you need to call stored_procedure_name when you execute a MySQL Stored procedure in command line.You could compare these:

  MySQL:

  sql = @String.Format(" CALL FirstSP(?, ?); ");

  SQL:

   cmd.CommandText = "FirstSP";
   cmd.CommandType = CommandType.StoredProcedure;

  Best regards,

  Yijing Sun

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.