locked
No value given for one or more required parameters in C# while using OLEDB command for update query

    Question

  • Dear  All,

                      This is Srikanth,I'm a new comer to this msdn and my task is to update and delete rows in the ms access data base using C#. But there is an error showing "No value given for one or more required parameters." whle running update query.

    con.Open();

    cmd =

    new OleDbCommand("update emp set ename='" + TextBox2.Text + "',job='" + TextBox3.Text + "',sal='" + TextBox4.Text + "',dept='" + TextBox5.Text + "' where eno='" + TextBox1.Text + "' "

    , con);

     

     

    cmd.ExecuteNonQuery();

    Response.Write(

    "<script>alert('" + "Record Updated" + "')</script>"

    );

    cmd.Dispose();

    con.Close();

    The above is the code written by me in the program for update query,Please tell me where did i made mistake in the program.

    Thanks,

    Srikanth.

    Wednesday, June 29, 2011 10:00 AM

Answers

  • Use parametrized query, which is as said, if nothing else, better looking:

     

    using(OleDbConnection con = new OleDbConnection("connString"))
    {
      string query = @"UPDATE emp SET ename = @param1, job = @param2, sal = @param3, dept = @param4 WHERE eno = @param5";
      using(OleDbCommand cmd = new OleDbCommand(query, con))
      {
       cmd.Parameters.Add("@param1", OleDbType.Varchar, 50).Value = TextBox2.Text;
       cmd.Parameters.Add("@param2", OleDbType.Varchar, 50).Value = TextBox3.Text;
       cmd.Parameters.Add("@param3", OleDbType.Varchar, 50).Value = TextBox4.Text;
       cmd.Parameters.Add("@param4", OleDbType.Varchar, 50).Value = TextBox5.Text;
       cmd.Parameters.Add("@param5", OleDbType.Varchar, 50).Value = TextBox1.Text;
       try
       {
         cmd.ExecuteNonQuery();
       }
       catch(Exception ex)
       {
         MessageBox.Show(ex.Message);
       }
      }
    }
    

     


    Try it this way. And tell me what will be the error message if it will occur, ok?

    bye


    Mitja
    • Marked as answer by Aspen VJ Friday, July 08, 2011 2:00 AM
    Wednesday, June 29, 2011 11:45 AM
  • Keep in mind that with OleDb, parameters are positional, not named. You can name your parameters, but you cannot use the @ syntax in your command (it throws an error about needing to declare a scalar variable) ... the correct syntax is to use the ? ... and it will take the parameters in the order in which you've added them.

    Also, I prefer the .AddWithValue syntax, which is even more readable, I think.

    using(OleDbConnection con = new OleDbConnection("connString"))
    {
     string query = @"UPDATE emp SET ename = ?, job = ?, sal = ?, dept = ? WHERE eno = ?";
     OleDbCommand cmd = new OleDbCommand(query, con)
     cmd.Parameters.AddWithValue("@ename", TextBox2.Text);
     cmd.Parameters.AddWithValue("@job", TextBox3.Text);
     cmd.Parameters.AddWithValue("@sal", TextBox4.Text);
     cmd.Parameters.AddWithValue("@dept", TextBox5.Text);
     cmd.ParametersAddWithValue("@eno", TextBox1.Text);
    
     con.Open();
     cmd.ExecuteNonQuery();
    }
    
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Aspen VJ Friday, July 08, 2011 2:01 AM
    Saturday, July 02, 2011 4:31 PM

All replies

  • Hi, and welcome to MSDN.

    1st of all, you should try to learn using Parametrized query. Now you pass values directly in the query statements, using parametrized query gives you a better control over the code, and it a way better looking.

    But anyway, even this code of yours should work fine. Can I ask you, if all the column in your dataBase`s table as type of varchar (string)? Because you pass to all of then a Text property, which is type of stirng.

    So if you have any column for example type of integer (int), you have to convert the data passed to the database, for example., lets say "sal" is type of decimal:

     

    sal='" + decimal.Parse(TextBox4.Text) + "' //and so on
    



    Mitja
    Wednesday, June 29, 2011 10:21 AM
  • Hi Mitja,

    Thank you for your response, I'm using access database as back end version 2010.All the fields in the database are "text" datatypes,including salary column.

    Please suggest me is there any other way to use oledb command to update the queries in database.

    Wednesday, June 29, 2011 11:15 AM
  • Use parametrized query, which is as said, if nothing else, better looking:

     

    using(OleDbConnection con = new OleDbConnection("connString"))
    {
      string query = @"UPDATE emp SET ename = @param1, job = @param2, sal = @param3, dept = @param4 WHERE eno = @param5";
      using(OleDbCommand cmd = new OleDbCommand(query, con))
      {
       cmd.Parameters.Add("@param1", OleDbType.Varchar, 50).Value = TextBox2.Text;
       cmd.Parameters.Add("@param2", OleDbType.Varchar, 50).Value = TextBox3.Text;
       cmd.Parameters.Add("@param3", OleDbType.Varchar, 50).Value = TextBox4.Text;
       cmd.Parameters.Add("@param4", OleDbType.Varchar, 50).Value = TextBox5.Text;
       cmd.Parameters.Add("@param5", OleDbType.Varchar, 50).Value = TextBox1.Text;
       try
       {
         cmd.ExecuteNonQuery();
       }
       catch(Exception ex)
       {
         MessageBox.Show(ex.Message);
       }
      }
    }
    

     


    Try it this way. And tell me what will be the error message if it will occur, ok?

    bye


    Mitja
    • Marked as answer by Aspen VJ Friday, July 08, 2011 2:00 AM
    Wednesday, June 29, 2011 11:45 AM
  • Keep in mind that with OleDb, parameters are positional, not named. You can name your parameters, but you cannot use the @ syntax in your command (it throws an error about needing to declare a scalar variable) ... the correct syntax is to use the ? ... and it will take the parameters in the order in which you've added them.

    Also, I prefer the .AddWithValue syntax, which is even more readable, I think.

    using(OleDbConnection con = new OleDbConnection("connString"))
    {
     string query = @"UPDATE emp SET ename = ?, job = ?, sal = ?, dept = ? WHERE eno = ?";
     OleDbCommand cmd = new OleDbCommand(query, con)
     cmd.Parameters.AddWithValue("@ename", TextBox2.Text);
     cmd.Parameters.AddWithValue("@job", TextBox3.Text);
     cmd.Parameters.AddWithValue("@sal", TextBox4.Text);
     cmd.Parameters.AddWithValue("@dept", TextBox5.Text);
     cmd.ParametersAddWithValue("@eno", TextBox1.Text);
    
     con.Open();
     cmd.ExecuteNonQuery();
    }
    
    

     


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by Aspen VJ Friday, July 08, 2011 2:01 AM
    Saturday, July 02, 2011 4:31 PM