syntax error missing operator in query expression c# using access as database

Question

I'm getting syntax error in all my inputs into the textboxes.

In my database all the requirement is string other than the ID which is an autonumber, I try to search for possible answer but all didn't work or maybe I just missed some answer

Here is the error:

Syntax error (missing operator) in query expression ''hasdasd'password = 'h'account_Type='Manager'Name='h'Middle_Name='h'Surname'h'address'h'BirthDate='3/17/1999'Mobile_Number'65465''.

Code:

private void update_Click(object sender, EventArgs e)
{
    DateTime bdate = DateTime.Parse(birthdate.Value.ToShortDateString());
    DateTime currentDate = DateTime.Parse(DateTime.Now.Date.ToShortDateString());

    int age = currentDate.Year - bdate.Year;
    String id = emp_view.SelectedRows[0].Cells[0].Value + String.Empty;
    int id1 = Int32.Parse(id);

    try
    {
        OleDbConnection con = new OleDbConnection();
        con.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\dbms\jollibee.accdb";
        con.Open();

        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = con;
        cmd.CommandText = "update Employee_Details set username = '" + username.Text +
                                                            "'password = '" + password.Text +
                                                            "'account_Type='" + accountType.Text +
                                                            "'Name='" + name.Text +
                                                            "'Middle_Name='" + middlename.Text +
                                                            "'Surname'" + surname.Text +
                                                            "'address'" + address.Text +
                                                            "'BirthDate='" + birthdate.Value.ToShortDateString() +
                                                            "'Mobile_Number'" + mobilenumber.Text +
                                                            "'where ID = '" + id1 + "'";

        if (username.Text.Equals("") ||
            username.Text.Equals("") ||
            password.Text.Equals("") ||
            middlename.Text.Equals("") ||
            surname.Text.Equals("") ||
            address.Text.Equals("") ||
            accountType.Text.Equals("") ||
            mobilenumber.Text.Equals("")
           )
        {
            MessageBox.Show("Please fill all fields.");
            con.Close();
        }
        else if (age < 18)
        {
            MessageBox.Show("You are not allowed to work because you are under age..");
            con.Close();
        }
        else
        {
            cmd.ExecuteNonQuery();
            con.Close();
            MessageBox.Show(username.Text + "is now updated on database.");
            list();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

I would start by fixing your SQL to use parameters. Your current code is vulnerable to SQL injection attacks, and certainly won't do the right thing if a user enters a name with an apostrophe. Using parameters will also make the SQL clearer, and it'll make it obvious where the problem is. (Print out the SQL you're currently using and I think it will be pretty clear that it's not what you want it to be.) — Jon Skeet
– Jon Skeet, Commented Apr 15, 2018 at 8:34
@DaisyShipton vulnerabilities are ok since it is only a school project. — Justine Cortez
– Justine Cortez, Commented Apr 15, 2018 at 8:39
Surely the point of a school project is to learn about the technologies you're dealing with - and one of the most important things to learn about when writing SQL is how to avoid SQL injection attacks. Using parameterized SQL will help every aspect of your code, including making it obvious where your current (multiple) problems are. Why resist that? — Jon Skeet
– Jon Skeet, Commented Apr 15, 2018 at 8:44

PSK · Accepted Answer · 2018-04-15 10:42:08Z

1

In your existing code, there are issues like.

1- Column in update are not separated by ","

2- All string are not separated using quotes ''

You should always avoid writing queries inline by concatenation of string. This will make you code vulnerable to SQL Injection.

To read more about SQL Injections check here

Change your code like following using command parameters.

cmd.CommandText = "update Employee_Details set [username] = @un, [password] = @pw, [account_Type]= @at, [Name] = @nm, [Middle_Name]= @mn, [Surname]= @sn, [address]= @add, [BirthDate] = @bd, [Mobile_Number] = @mn WHERE [Id]=@id";
cmd.Parameters.Add("@un", OleDbType.VarChar).Value = username.Text;
cmd.Parameters.Add("@pw", OleDbType.VarChar).Value = password.Text;
cmd.Parameters.Add("@at", OleDbType.VarChar).Value = accountType.Text;
cmd.Parameters.Add("@nm", OleDbType.VarChar).Value = name.Text;
cmd.Parameters.Add("@mn", OleDbType.VarChar).Value = middlename.Text;
cmd.Parameters.Add("@sn", OleDbType.VarChar).Value = surname.Text;
cmd.Parameters.Add("@add", OleDbType.VarChar).Value = address.Text;
cmd.Parameters.Add("@bd", OleDbType.Date).Value = Convert.ToDateTime(birthdate.Value);
cmd.Parameters.Add("@mn", OleDbType.VarChar).Value = mobilenumber.Text;
cmd.Parameters.Add("@id", OleDbType.VarChar).Value = id1;

Note: You need to correct the datatype based on your table structure as it is now known to me.

edited Apr 15, 2018 at 10:42

answered Apr 15, 2018 at 8:43

PSK

18k5 gold badges35 silver badges44 bronze badges

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

2 Comments

Justine Cortez Over a year ago

i got a new error syntax error in update statement

PSK Over a year ago

Update syntax is correct, anyway there are few keywords, i have updated the command string, you can give a try

Gustav · Accepted Answer · 2018-04-15 09:44:45Z

Your completely malformed SQL should look like:

       cmd.CommandText = "update Employee_Details set " +
                         "username = '" + username.Text + "',"+
                         "[password] = '" + password.Text + "'," +
                         "account_Type = '" + accountType.Text + "'," +
                         "[Name] = '" + name.Text + "'," +
                         "Middle_Name = '" + middlename.Text + "'," +
                         "Surname = '" + surname.Text + "'," +
                         "address = '" + address.Text + "'," +
                         "BirthDate = #" + birthdate.Value.ToString("yyyy'/'MM'/dd") + "#," +
                         "Mobile_Number = '" + mobilenumber.Text + "' " +
                         "where ID = " + id1 + "";

That said, DO use parameters as already explained. Much easier and safer.

Collectives™ on Stack Overflow

syntax error missing operator in query expression c# using access as database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related