locked
SQL UPDATE command returning syntax error RRS feed

  • Question

  • Hi everyone, 

    While sending an update query to my database, I'm getting the error:

    System.Data.OleDb.OleDbException (0x80040E10): No value given for one or more required parameters.
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at TrackingForm.frmTrackingSheet.updateTaxPayer() in c:\Users\ItsFido\Documents\Visual Studio 2012\Projects\TrackingForm\TrackingForm\frmTrackingSheet.cs:line 525
       at TrackingForm.frmTrackingSheet.btnSave_Click(Object sender, EventArgs e) in c:\Users\ItsFido\Documents\Visual Studio 2012\Projects\TrackingForm\TrackingForm\frmTrackingSheet.cs:line 465
    

    My code is:

                    sql = string.Format("UPDATE TrackingSheet SET [HAS SPOUSE]=?, [SPOUSESSN]=?, [TNAME]=?, [ADDRESS]=?, [CITY]=?, [STATE]=?, [ZIP]=?, [HAS NEW ADDRESS]=?, [NEW ADDRESS]=?, [NEW CITY]=?, [NEW STATE]=?, [NEW ZIP]=?, [DATE FILED]=?, [YEARS FILED]=?, [TAX DUE]=?, [AMOUNT BILLED]=?, [DATE BILL SENT]=?, [TOTAL PAID]=?, [BALANCE]=?, [NAT]=?, [NONR]=?, [ACT]=?, [INACT]=?, [DATEINACT]=?, [INACTREASON]=?, [NOT REQ REASON]=?, [NOTES]=?, [AUDITOR]=? WHERE [SSN]=?;");
                    dbConn = new OleDbConnection(sConnection);
                    dbConn.Open();
                    dbCmd = new OleDbCommand();
                    dbCmd.CommandText = sql;
                    dbCmd.Parameters.Add(new OleDbParameter("HAS SPOUSE",checkboxToBool(chkSpouse.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("SPOUSESSN",txtSpouseSSN.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("TNAME",txtName.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("ADDRESS",txtStreetAddress.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("CITY",txtCity.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("STATE", cbxState.SelectedIndex));
                    dbCmd.Parameters.Add(new OleDbParameter("ZIP",txtZip.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("HAS NEW ADDRESS",checkboxToBool(chkSpouse.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("NEW ADDRESS", txtNewAddress.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("NEW CITY",txtNewCity.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("NEW STATE",cbxNewState.SelectedIndex));
                    dbCmd.Parameters.Add(new OleDbParameter("NEW ZIP",txtZip.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("DATE FILED",txtDate.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("YEARS FILED",txtYear.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("TAX DUE",txtTax.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("AMOUNT BILLED",txtAmountBilled.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("DATE BILL SENT",txtDateSent.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("TOTAL PAID",txtTotalPaid.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("BALANCE",txtBalance.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("NAT",checkboxToBool(chkMoreTime.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("NONR",checkboxToBool(chkMoreTime.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("ACT",rbToBool(rbtActive.Checked)));
                    dbCmd.Parameters.Add(new OleDbParameter("INACT",rbToBool(rbtInactive.Checked)));
                    dbCmd.Parameters.Add(new OleDbParameter("DATEINACT",txtInactive.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("INACTREASON",txtInactiveReason.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("NOT REQ REASON",txtNotReq.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("NOTES",txtNotes.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("AUDITOR",tData.getUser()));
                    dbCmd.Parameters.Add(new OleDbParameter("SSN",txtSSN.Text));
                    dbCmd.Connection = dbConn;
                    dbCmd.ExecuteNonQuery();
    
    So every Parameter is defined, but it keeps saying it's not. Any ideas?

    Friday, June 14, 2013 6:03 PM

Answers

  • First, I have to correct what I said earlier. Apparently you original Syntax was right, for OleDB. As Oralce for whatever wierd reason uses positional Parameters.

    But it will also accept named parameters but ignores the names and only looks at the postion:

    http://stackoverflow.com/questions/2675610/how-to-update-a-table-using-oledb-parameters

    At least the restructuring shows us that the number of Parameters in Query and the Add Statements overlaps (wich is hard to see with that many).

    One idea wich perhaps helps you to locate wich Parameter causes the Problem:

    So far you used untyped OlebDBParameters. Wich means any typechecks are done somewhere into the actual processing. Unfortuantely there is no Constructor that accepts a type and a value, so you have to use this minor trick:

    command.Parameters.Add("NameOfParameter", OleDbType.Char).Value = "a";

    Here is the list of avalible types:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

    If you need an size/precision value, you have to use the Constructor that accepts a thrid Int32 Parameter.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2


    • Edited by Christopher84 Tuesday, June 18, 2013 2:31 PM
    • Marked as answer by Bob Shen Friday, June 21, 2013 9:37 AM
    Tuesday, June 18, 2013 2:30 PM
  • Hi,

    Be sure to check if TextBoxes have values in them. When TextBox.Text is null, the parameter is NOT given the DBNull value. You should do this yourself. You can use the conditional operator to do this pretty fast:

    dbCmd.Parameters.Add(new OleDbParameter("@2",txtSpouseSSN.Text == null ? DBNull.Value, txtSpouseSSN.Text));

    Hope this helps

    @Dzuniman That's not true when using Parameters. Those will take care of that for you.


    Please mark the best replies as answers
    Blog: bloggingabout.net/blogs/rick
    Twitter: @rickvdbosch

    Wednesday, June 19, 2013 6:18 AM

All replies

  • You might want to break point at the beginning and check each variable you're using as a parameter. Some could be empty (rbToBool, txtTax.Text, txtYear etc)
    Friday, June 14, 2013 6:28 PM
  • I have everything writing to the output window (via system.console.writeline()) and everything has an output except the fields that are intentionally left blank.
    Friday, June 14, 2013 6:44 PM
  • When initializing the 'sql' command variable, leave out the String.Format() method. Initialize it instead with a simple string.

    sql = "UPDATE TrackingSheet SET ...;";

    The String.Format() method should be used with format items enclosed in braces '{}' (here is an example).

    Kind regards,

    wizend

    Friday, June 14, 2013 9:18 PM
  • Are you sure that the field that you leave blank are not required?  Does the table in the database allow null for those field?

    Friday, June 14, 2013 9:29 PM
  • That syntax you use is not making any sense for me either:

    SET [HAS SPOUSE]=?

    You then try to repalce [HAS SPOUSE] with a boolean value, wich would result in:

    SET true=?

    or

    SET false=?

    the correct syntax is:

    SET [Name of the SQL Coloum to be set] = [value to set the column too]


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2

    Friday, June 14, 2013 9:31 PM
  • Has Spouse is the name of the column, and  checkBoxToBool is set up to return 1 or 0 :/
    Friday, June 14, 2013 10:27 PM
  • Then you use the syntax totally wrong. Also having a column or table name with space in it is a bad idea, it is more work with every query. For something the user never even sees or knows exist.

    The proper syntax after processing the SQL-Parameters would have to be:

    SET [HAS SPOUSE] = true;
    SET [HAS SPOUSE] = false;

    The way to use SQL-Parameters is:

    http://www.dotnetperls.com/sqlparameter

    SqlCommand command = new SqlCommand("SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection);
    command.Parameters.Add(new SqlParameter("Name", dogName));

    Using the first value of your code as example, it would have to be written:

    sql = "UPDATE TrackingSheet SET [HAS SPOUSE]=
    @FirstParameterWichIsUnrelatedToTheColumnname,..."
    //some code omitted
    dbCmd.Parameters.Add(new OleDbParameter(
    "FirstParameterWichIsUnrelatedToTheColumnname",
    checkboxToBool(chkSpouse.CheckState)));


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2



    Saturday, June 15, 2013 9:13 AM
  • Christopher84,

    I read what you wrote, and i made a few changes. I have to leave the database with spaces, due to my supervisor is going to be looking though it.

             
      if (taxPayerInDB) 
                {
                    sql = "UPDATE TrackingSheet SET [HAS SPOUSE]=@1, [SPOUSESSN]=@2, [TNAME]=@3, [ADDRESS]=@4, [CITY]=@5, [STATE]=@6, [ZIP]=@7, [HAS NEW ADDRESS]=@8, [NEW ADDRESS]=@9, [NEW CITY]=@10, [NEW STATE]=@11, [NEW
     ZIP]=@12, [DATE FILED]=@13, [YEARS FILED]=@14, [TAX DUE]=@15, [AMOUNT BILLED]=@16, [DATE BILL SENT]=@17, [TOTAL PAID]=@18, [BALANCE]=@19, [NAT]=@20, [NONR]=@21, [ACT]=@22, [INACT]=@23, [DATEINACT]=@24, [INACTREASON]=@25, [NOT REQ REASON]=@26, [NOTES]=@27,
     [AUDITOR]=@28 WHERE [SSN]=@29;";
                    dbConn.Open();
                    dbCmd = new OleDbCommand();
                    dbCmd.CommandText = sql;
                    dbCmd.Connection = dbConn; 
                    dbCmd.Parameters.Add(new OleDbParameter("@1",checkboxToString(chkSpouse.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("@2",txtSpouseSSN.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@3",txtName.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@4",txtStreetAddress.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@5",txtCity.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@6", cbxState.SelectedIndex));
                    dbCmd.Parameters.Add(new OleDbParameter("@7",txtZip.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@8",checkboxToString(chkNewAddress.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("@9", txtNewAddress.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@10",txtNewCity.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@11",cbxNewState.SelectedIndex));
                    dbCmd.Parameters.Add(new OleDbParameter("@12",txtZip.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@13",txtDate.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@14",txtYear.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@15",txtTax.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@16",txtAmountBilled.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@17",txtDateSent.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@18",txtTotalPaid.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@19",txtBalance.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@20",checkboxToString(chkMoreTime.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("@21",checkboxToString(chkNonResident.CheckState)));
                    dbCmd.Parameters.Add(new OleDbParameter("@22",rbToString(rbtActive.Checked)));
                    dbCmd.Parameters.Add(new OleDbParameter("@23",rbToString(rbtInactive.Checked)));
                    dbCmd.Parameters.Add(new OleDbParameter("@24",txtInactive.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@25",txtInactiveReason.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@26",txtNotReq.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@27",txtNotes.Text));
                    dbCmd.Parameters.Add(new OleDbParameter("@28",tData.getUser()));
                    dbCmd.Parameters.Add(new OleDbParameter("@29",txtSSN.Text)); 
                    dbCmd.ExecuteNonQuery();
                    dbConn.Close();
                }


    i updated the sql string but i'm still getting the same error: No value given for one or more parameters.


    • Edited by ItsFido Monday, June 17, 2013 5:15 PM
    Monday, June 17, 2013 5:14 PM
  • Any of the 29 values could be invalid. Some of the values could be null. Strings could be empty.

    That is an awfull lot of parameters/columns for any Database Table.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2

    Tuesday, June 18, 2013 9:45 AM
  • First, I have to correct what I said earlier. Apparently you original Syntax was right, for OleDB. As Oralce for whatever wierd reason uses positional Parameters.

    But it will also accept named parameters but ignores the names and only looks at the postion:

    http://stackoverflow.com/questions/2675610/how-to-update-a-table-using-oledb-parameters

    At least the restructuring shows us that the number of Parameters in Query and the Add Statements overlaps (wich is hard to see with that many).

    One idea wich perhaps helps you to locate wich Parameter causes the Problem:

    So far you used untyped OlebDBParameters. Wich means any typechecks are done somewhere into the actual processing. Unfortuantely there is no Constructor that accepts a type and a value, so you have to use this minor trick:

    command.Parameters.Add("NameOfParameter", OleDbType.Char).Value = "a";

    Here is the list of avalible types:

    http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

    If you need an size/precision value, you have to use the Constructor that accepts a thrid Int32 Parameter.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2


    • Edited by Christopher84 Tuesday, June 18, 2013 2:31 PM
    • Marked as answer by Bob Shen Friday, June 21, 2013 9:37 AM
    Tuesday, June 18, 2013 2:30 PM
  • Remember that strings have to be in quotes e.g 
    "'" + txtName.Text +"'"
    So you might consider changing your code.
    Wednesday, June 19, 2013 5:37 AM
  • Hi,

    Be sure to check if TextBoxes have values in them. When TextBox.Text is null, the parameter is NOT given the DBNull value. You should do this yourself. You can use the conditional operator to do this pretty fast:

    dbCmd.Parameters.Add(new OleDbParameter("@2",txtSpouseSSN.Text == null ? DBNull.Value, txtSpouseSSN.Text));

    Hope this helps

    @Dzuniman That's not true when using Parameters. Those will take care of that for you.


    Please mark the best replies as answers
    Blog: bloggingabout.net/blogs/rick
    Twitter: @rickvdbosch

    Wednesday, June 19, 2013 6:18 AM
  • He is using Parameter Syntax. Wich is the better way in every case.

    Building queries via string conaction has more chances to fail, and opens your code wide for SQL-Injections.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2

    Wednesday, June 19, 2013 10:51 AM