Syntax error in UPDATE statement Visual Studio C#.net

Question

I've been trying to figure out the right syntax for the UPDATE statement when coding in C#.net and using OleDbCommand. I feel like what I have is correct, but it definitely isn't because I still am getting the error: "Syntax error in UPDATE statement". My code is posted below for my Update button. I have checked many sites (including Stackoverflow) to see what the correct syntax and standard is for creating SQL statements, but still haven't found the one that doesn't give me a syntax error. Any suggestions would be helpful. Thanks!

private void update_button_Click(object sender, EventArgs e)
    {
        String ssize = "";
        foreach (Control control in Controls)
        {
            if (control != null && control is RadioButton)
            {
                RadioButton radio = control as RadioButton;
                if (radio.Checked == true)
                {
                    ssize = control.Text;
                }
            }
        }
        OleDbConnection myCon = new OleDbConnection(connString);
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "UPDATE Youth SET FName = ?, MI = ?, LName = ?, Street = ?, CityStateZip = ?, PG = ?, HomePN = ?, WorkPN = ?, CellPN = ?, YEmail = ?, PGEmail = ?, Age = ?, DOB = ?, HS = ?, Grade = ?, PrevExp = ?, SSize = ?, Allergies = ?, MedConditions = ?, Avail = ?, FirstDep = ?, SecDep = ?, ThirdDep = ?, Fundraiser_1 = ?, Fundraiser_2 = ?, Fundraiser_3 = ?, Fundraiser_4 = ?, WHERE ID = ?";
        cmd.Parameters.Add("@FName", OleDbType.VarChar).Value = fname_tb.Text;
        cmd.Parameters.Add("@MI", OleDbType.VarChar).Value = mi_tb.Text;
        cmd.Parameters.Add("@LName", OleDbType.VarChar).Value = lname_tb.Text;
        cmd.Parameters.Add("@Street", OleDbType.VarChar).Value = address1_tb.Text;
        cmd.Parameters.Add("@CityStateZip", OleDbType.VarChar).Value = address2_tb.Text;
        cmd.Parameters.Add("@PG", OleDbType.VarChar).Value = pg_tb.Text;
        cmd.Parameters.Add("@HomePN", OleDbType.VarChar).Value = homePN_tb.Text;
        cmd.Parameters.Add("@WorkPN", OleDbType.VarChar).Value = workPN_tb.Text;
        cmd.Parameters.Add("@CellPN", OleDbType.VarChar).Value = cellPN_tb.Text;
        cmd.Parameters.Add("@YEmail", OleDbType.VarChar).Value = yemail_tb.Text;
        cmd.Parameters.Add("@PGEmail", OleDbType.VarChar).Value = pgemail_tb.Text;
        cmd.Parameters.Add("@Age", OleDbType.VarChar).Value = age_tb.Text;
        cmd.Parameters.Add("@DOB", OleDbType.Date).Value = bd_picker.Text;
        cmd.Parameters.Add("@HS", OleDbType.VarChar).Value = hs_tb.Text;
        cmd.Parameters.Add("@Grade", OleDbType.Integer).Value = grade_tb.Text;
        cmd.Parameters.Add("@PrevExp", OleDbType.Integer).Value = prevexp_tb.Text;
        cmd.Parameters.Add("@SSize", OleDbType.VarChar).Value = ssize;
        cmd.Parameters.Add("@Allergies", OleDbType.VarChar).Value = food_tb.Text;
        cmd.Parameters.Add("@MedConditions", OleDbType.VarChar).Value = special_tb.Text;
        cmd.Parameters.Add("@Avail", OleDbType.VarChar).Value = week1_tb.Text + "," + week2_tb.Text;
        cmd.Parameters.Add("@FirstDep", OleDbType.Date).Value = deposit_picker.Text;
        cmd.Parameters.Add("@SecDep", OleDbType.Date).Value = second_picker.Text;
        cmd.Parameters.Add("@ThirdDep", OleDbType.Date).Value = third_picker.Text;
        cmd.Parameters.Add("@Fundraiser_1", OleDbType.Date).Value = fund1Picker.Text;
        cmd.Parameters.Add("@Fundraiser_2", OleDbType.Date).Value = fund2Picker.Text;
        cmd.Parameters.Add("@Fundraiser_3", OleDbType.Date).Value = fund3Picker.Text;
        cmd.Parameters.Add("@Fundraiser_4", OleDbType.Date).Value = fund4Picker.Text;
        cmd.Parameters.Add("@ID", OleDbType.Integer).Value = idTB.Text;

        cmd.Connection = myCon;
        try
        {
            myCon.Open();
            cmd.ExecuteNonQuery();
            myCon.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Problem opening connection!\n" + ex.Message, "Exception");
        }
    }

SSCCE. Specifically, try to bring your code samples down to a more managable size. It's quite possible that in doing so, you would have found the answer to this yourself. — user
– user, Commented Sep 20, 2011 at 14:50
just a suggestion- when debugging syntax errors, work on a smaller set, updating 1 field instead of all of them. it's easier that way. — Beth
– Beth, Commented Sep 20, 2011 at 14:51
@Michael: How do you make this smaller without changing the meaning of the code? — Robert Harvey
– Robert Harvey, Commented Sep 20, 2011 at 14:51
@RobertHarvey, you don't. Instead, like Beth said, you either pretty much rip it all out and start adding things back until it breaks, or you remove parts until it starts working. Either way, you will likely find the error in fairly short order. — user
– user, Commented Sep 20, 2011 at 14:53

The Evil Greebo · Accepted Answer · 2011-09-20 14:48:02Z

4

You've got a comma before the WHERE clause in your sql.

cmd.CommandText = "UPDATE Youth SET FName = ?, MI = ?, LName = ?, Street = ?, 
    CityStateZip = ?, PG = ?, HomePN = ?, WorkPN = ?, CellPN = ?, YEmail = ?, 
    PGEmail = ?, Age = ?, DOB = ?, HS = ?, Grade = ?, PrevExp = ?, SSize = ?, 
    Allergies = ?, MedConditions = ?, Avail = ?, FirstDep = ?, SecDep = ?, 
    ThirdDep = ?, Fundraiser_1 = ?, Fundraiser_2 = ?, Fundraiser_3 = ?, 
    Fundraiser_4 = ?,  <<--- extra comma
    WHERE ID = ?";

answered Sep 20, 2011 at 14:48

The Evil Greebo

7,1753 gold badges31 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandon Over a year ago

wow! thank you!! most likely a copy-paste type of error since the sql statement is so long, but thanks again!

Icarus · Accepted Answer · 2011-09-20 14:49:09Z

2

Your error (or at least one of them) is right here:

Fundraiser_4 = ?, Where ...

remove the , after the ?

answered Sep 20, 2011 at 14:49

Icarus

64k14 gold badges102 silver badges116 bronze badges

Collectives™ on Stack Overflow

Syntax error in UPDATE statement Visual Studio C#.net

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related