SQL UPDATE - "No value given for one or more required parameters"

SQL UPDATE - "No value given for one or more required parameters"

• Rusty Miller

  Hello Blair;

  Believe it or not, I appreciate your assistance.  I understand that  " . . . your not trying to be a dick . . .", I am not ready to be hired on anywhere using my C# skills, you see, I am still learning C#, and what you see above is what I've been able to gleen from books and MSDN.  I'm learning as I go and "on my own"; I can't afford to attend classes, besides, C# (and .NET) are not being offered thru any of the three colleges here in my home town (yet).

  I replaced what I had already coded with your suggestion and I am receiving that ever popular error: "Object reference not set to an instance of an object." when the execution gets to this line: 
  
          editCmnd.Connection.Open();

  
  I have stepped thru this code with breakpoints and set a module level declaration:

      private OleDbCommand editCmnd = null;

  and have not been able to find the problem.  I found that searching the internet is like looking for a "needle in a haystack" with over 31,000,000 references! 

  I hate to ask this, but do you have any idea as to how I can resolve this

  
  
  

• MikeWDub

  That's correct. Just comma separate the list of columns and the list of values.
  
  

• David Reynolds

  now here are the neat things about using parameters. . .

  1. The SQL is clear.

  2. SQL Syntax errors will reflect actual syntax errors.

  3. Automatic type conversion. if NationalDuesPaidDate were defined as a date in your database,

  cmd.Parameters.Add(new OleDbParameter("NatPaidDate", this.textBoxNatlDuesPaidDate.Text)); 

  would work as long as the text box could be parsed as a datetime.

  4. You don't have to worry about quotes in your input.

  5. On batch execution, performance can be enhanced. If you use the extended version of the parameter that specifies type and size, you can call 'OleDbCommand.Prepare()' to compile it. As you loop through, just change the parameter value(s) and execute again.

  Oh. . . and you can also use the simpler form -

    cmd.Parameters.Add("Lname", this.textBoxLastName.Text);

   

  
  
  

• khaled94

  it could also be done this way - sorry for the typos

  string strEditCommand = "UPDATE Post1889 SET ";
  strEditCommand += " LastName = ,  FirstName = ,  Address = , ";
  strEditCommand += " City = , State = , ZipCode = ,";
  strEditCommand += " HomePhone = , EmailAddress = , ";
  strEditCommand += " MembershipStatus = , DateJoined = , ";
  strEditCommand += " NationalDuesPaidDate = ,  NationalDuesChekNum = , ";
  strEditCommand += " PostDuesPaidDate = , PostDuesChekNum =   ";
  strEditCommand += " WHERE SAMS_ID = ";

  using (OleDbConnection con = new OleDbConnection(strEditConnect))
  {
      using (OleDbCommand cmd = new OleDbCommand(strEditCommand, con))
      {
          cmd.Parameters.Add(new OleDbParameter("Lname", this.textBoxLastName.Text));
          cmd.Parameters.Add(new OleDbParameter("Fname", this.textBoxFirstName.Text));
          cmd.Parameters.Add(new OleDbParameter("Address", this.textBoxMemberAddress.Text));
          cmd.Parameters.Add(new OleDbParameter("City", this.textBoxAddressCity.Text));
          cmd.Parameters.Add(new OleDbParameter("State", this.textBoxAddressState.Text));
          cmd.Parameters.Add(new OleDbParameter("Zip", this.textBoxAddressZipCode.Text));
          cmd.Parameters.Add(new OleDbParameter("Phone", this.textBoxPhoneNumber.Text));
          cmd.Parameters.Add(new OleDbParameter("Email", this.textBoxEmailAddress.Text));
          cmd.Parameters.Add(new OleDbParameter("Status", this.textBoxMembershipStatus.Text));
          cmd.Parameters.Add(new OleDbParameter("JoinDate", this.textBoxDateJoined.Text));
          cmd.Parameters.Add(new OleDbParameter("NatPaidDate", this.textBoxNatlDuesPaidDate.Text));
          cmd.Parameters.Add(new OleDbParameter("NatChekNum", this.textBoxNatlCheckNumber.Text));
          cmd.Parameters.Add(new OleDbParameter("PostPaidDate", this.textBoxPostDuesPaidDate.Text));
          cmd.Parameters.Add(new OleDbParameter("PostChekNum", this.textBoxPostCheckNumber.Text));
          cmd.Parameters.Add(new OleDbParameter("SamID", this.textboxSamsId.Text));
          cmd.Connection.Open();
          cmd.ExecuteNonQuery();
      }
  }

  
  
  

• Priyanka63947

  This is a continuation of my original request.  For some reason, I cannot see what the devil is wrong with this INSERT query.  I have tested this query by removing all of the fields (there are 14) and replacing them one by one but the error still occurs.  There is an UPDATE query that works fine and updates the database successfully (see the previous entries above); however there must be something I'm not seeing   To the eye this query looks correct; however, when the app runs, I receive this error: System.Data.OleDb.OledbException: No value given for one or more required parameters.  The query looks like this: (Note: This is just one field of 14 that the database contains.)          strAddCommand = "INSERT INTO Post1889 ";          strAddCommand += "(SAMS_ID ";          strAddCommand += ") VALUES (";          strAddCommand += "SAMS_ID = ) ";                    using ( OleDbConnection conn = new OleDbConnection( strAddConnect ) )          {            using ( OleDbCommand addCmnd = new OleDbCommand( strAddCommand, conn ) )            {             addCmnd.Parameters.Add(new OleDbParameter("SamId", this.textboxSamsId.Text));                          conn.Open();             addCmnd.ExecuteNonQuery();             conn.Close();            }          } Can anyone see what I am apparantly missing  The error is occuring on the addCmnd.ExecuteNonQuery() line. Thank you;

  
  
  

• gm001

  So, when I add the other 13 fields to the query, it would look like this :

  INSERT INTO Post1889

    (SAMS_ID, fld2, fld3, fld4, fld5, fld6, fld7, fld8, fld9 ... )

    VALUES ( , , , , , , , , , , , , , )

   

   

  
  
  

• Sentient

  this is my number one pet peeve. people building SQL.

  if you would just use parameters all these problems go away.

  Oh yeah it can be done by parsing for quotes and other manipulations. . . but why go through all that. DO IT CORRECTLY FROM THE GET GO!!! Who knows where the error is. Use parameters and the errors (if any) would become readily apparent. The error thrown would probably be an incorrect syntax telling you were the error is. As it stands, the error could be anywhere and it is a waste of time trying to look at the above code and try to figure out what may be wrong.

  I am not trying to be a d|ck here, but I tell ya. . .  if you brought the above code to me as part of your portfolio on a job interview, the job interview would be over immediately.

  Learn to use parameters.

  Get in the habit of using them religiously!!!

  Even when you think they are are overkill!!!

  that being said. . . try it the right way:

   

  =================================

  string strEditCommand = "UPDATE Post1889 SET ";
  strEditCommand += " LastName = ,  FirstName = ,  Address = , ";
  strEditCommand += " City = , State = , ZipCode = ,";
  strEditCommand += " HomePhone = , EmailAddress = , ";
  strEditCommand += " MembershipStatus = , DateJoined = , ";
  strEditCommand += " NationalDuesPaidDate = ,  NationalDuesChekNum = , ";
  strEditCommand += " PostDuesPaidDate = , PostDuesChekNum =   ";
  strEditCommand += " WHERE SAMS_ID = ";

  using (OleDbConnection con = new OleDbConnection(strEditConnect))
  {
      using (OleDbCommand cmd = new OleDbCommand())
      {
          cmd.Connection.Open();
          cmd.CommandText = strEditCommand;
          cmd.Parameters.Add(new OleDbParameter("Lname", this.textBoxLastName.Text));
          cmd.Parameters.Add(new OleDbParameter("Fname", this.textBoxFirstName.Text));
          cmd.Parameters.Add(new OleDbParameter("Address", this.textBoxMemberAddress.Text));
          cmd.Parameters.Add(new OleDbParameter("City", this.textBoxAddressCity.Text));
          cmd.Parameters.Add(new OleDbParameter("State", this.textBoxAddressState.Text));
          cmd.Parameters.Add(new OleDbParameter("Zip", this.textBoxAddressZipCode.Text));
          cmd.Parameters.Add(new OleDbParameter("Phone", this.textBoxPhoneNumber.Text));
          cmd.Parameters.Add(new OleDbParameter("Email", this.textBoxEmailAddress.Text));
          cmd.Parameters.Add(new OleDbParameter("Status", this.textBoxMembershipStatus.Text));
          cmd.Parameters.Add(new OleDbParameter("JoinDate", this.textBoxDateJoined.Text));
          cmd.Parameters.Add(new OleDbParameter("NatPaidDate", this.textBoxNatlDuesPaidDate.Text));
          cmd.Parameters.Add(new OleDbParameter("NatChekNum", this.textBoxNatlCheckNumber.Text));
          cmd.Parameters.Add(new OleDbParameter("PostPaidDate", this.textBoxPostDuesPaidDate.Text));
          cmd.Parameters.Add(new OleDbParameter("PostChekNum", this.textBoxPostCheckNumber.Text));
          cmd.Parameters.Add(new OleDbParameter("SamID", this.textboxSamsId.Text));
          con.Open();
          cmd.ExecuteNonQuery();
      }
  }

  =================

  Does that work

  
  
  

• Stardust

  Glad I could help.

  and again. . . I don't mean to be an @$$! Its just a major pet peeve of mine!

  You would be suprised how many 'experienced' programmers avoid parameters out of laziness. They say things like 'Why complicate it ' I say, building all that SQL complicates things. Parameterization breaks it down and simplifies it.

  if you are crafty, you can eliminate all that spaghetti that comes from dealing with optional selection criteria.

  starting with a sql statement like

  string sql = "SELECT * FROM ATABLE ";

  and a form that with text boxes that allow a user to specify criteria.

  Give your textboxes the same name as the fields they select against.

  for each textbox that has data, add it to a list<TextBox>, lets call it paramvals.

  if(paramvals.Count > 0)
      sql += " where "
  for (int i = 0; i < paramvals.Count; i++)
      sql += string.format("{ 0 } = ", 
            paramvals[ i ].Name, 
            i < paramvals.Count-1 " and ", "")
  cmd.CommandText = sql;
  foreach(TextBox tb in paramvals.Count; i++)
     cmd.Parameters.Add(tb.Name, tb.Text);

  no spaghetti!

  Make sense

  Now. . .  will take it one step further. I contend that the form code and the underlying operation code could be generated programmatically by looking at the schema of the table/view. wouldn't that be nice, just point at a table and say ' .Net, Generate My Forms for This Table!'

   

  
  
  

• J Griffin

  oops. . .  left out a line. . . sorry typeos. . . cut and paste and cut errors. . .

  never set the connection and called open twice

          cmd.Connection = con;
          cmd.Connection.Open();
          cmd.CommandText = strEditCommand;

  
  
  

• gavwel

  It's starting to make sense.  I'm still a bit confused but in time and working with parameters, I'll be "more at home"

   

  Thanks again for your help. 

  Greg

  
  
  

• Sergei Kantere

  Well, it certainly looks like I've got alot of reading to do on Parameters and Parameterized Queries!.  Thank you for your help in this.  Your solution is now in my "snippet" library and I'm going back to my other apps to change the code in them.

  Thank you;

  Greg "Rhubarb" Walsh

   

  
  
  

• loo

  Your syntax for the INSERT statement is wrong. The SQL should look like:

   

  INSERT INTO Post1889

    (SAMS_ID)

    VALUES ( )

   

  You've got an extra "SAMS_ID = " in the VALUES.

   

  Also, when breaking strings across multiple lines, it's best to:

   

  strAddCommand = "INSERT INTO Post1889 "
                            + "(SAMS_ID " 
                            + ") VALUES ("
                            + "SAMS_ID = ) ";

   

  The compiler can then optimize the concatenation and produce one literal value (i.e. one big long string) rather than having to do it at run-time. (The compiler could theoretically analyze your usage of the strAddCommand variable and determine that it was safe to concatenate at compile-time, but it doesn't do that.) Honestly the perf difference is negligible, but it's still good coding practice to concatentate constants at compile-time as I've shown.
  

  
  
  

• David Eccleston - DX MVP

  exactly!
  
  

• jpaskett

  It sounds to me like the OleDbProvider thinks that there is a parameter in your SQL command. Do any of your inputs contain a question mark, which denotes a parameter in OleDb

   

  Side note: Your application is vulnerable to SQL injection. If any textbox contains:

   

  foo' DROP TABLE Post1889 --

   

  then you're hooped.

   

  I would recommend using a parameterized SQL statement such as:

  UPDATE Post1889

    SET LastName = ,

          FirstName = ,

          etc.

    WHERE SAMS_ID =

   

  Then pass all your textbox values as parameters using OleDbParameter.

  
  
  
• SQL UPDATE - "No value given for one or more required parameters"