Syntax Error in Update Statement C# Microsoft Access

Question

My result in I get in my console.

UPDATE [Client] SET username ='asd', password ='asd', address ='asddd', referenceno ='12345' WHERE id = 27

When I write a query in my MS Access Database, it is working fine.

I have no idea why this error appears whenever I tried to update my data into the database.

    private void buttonUpdate_Click(object sender, EventArgs e) // user click on button update
    {
        if (cbTable.Text.Equals("User"))  
        {
            string query = "";
            query += "username ='" + textBoxUsername.Text.ToString() + "' ,"; //query
            query += "password ='" + textBoxPassword.Text.ToString() + "' ,"; //query
            query += "contact ='" + ContactNo.Text.ToString() + "' ,"; //query
            query += "ref_no = " + textBoxReferenceno.Text.ToString() + " WHERE id = " + Convert.ToInt32(textBoxId.Text.ToString()); //query
            try
            {
                new controllerclass().updateDatabase("User", query); //update database
                Console.WriteLine(query);

                Console.WriteLine("Saved");
                MessageBox.Show("User profile has been updated.", "Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
                loadDatabaseUser();
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }
        }
    }



    //After users enter the update button, this function will be used.
    public bool updateDatabase(string type, string query) //update database function
    { 
        try
        {
            OleDbCommand cmd = new OleDbCommand(); //open connection
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "UPDATE [" + type + "] SET " + query;
            cmd.Connection = conn;
            Console.WriteLine("UPDATE [" + type + "] SET " + query);
            cmd.ExecuteNonQuery(); //execute command
            closeConnection();
            return true;
        }
        catch (Exception e)
        {
            closeConnection(); // close connection
            Console.WriteLine(e.Message); //writeline to console
            return false;
        }
    }

Your code is vulnerable to SQL injection. Consider using parametrized queries instead. — Darin Dimitrov
– Darin Dimitrov, Commented Feb 16, 2014 at 17:48

Steve · Accepted Answer · 2014-02-16 18:08:33Z

PASSWORD is a reserved keyword in Microsoft Access. You need to encapsulate it with square brackets

 query += "[password] ='"

Said that, let me give and advice here. Change as soon as possible this architecture that force you to write string concatenations to build sql queries. The only way to create and use a command text is through a parameterized query

Looking better at your query, also USER (the table name) is a reserved keyword

So let me show a different approach

string query = @"username =?, [password] = ?, contact =?
                ref_no = ? WHERE id = ?";
List<OleDbParameter> parameters = new List<OleDbParameter>();
parameters.Add(new OleDbParameter()
      {ParameterName = "@p1, OleDbType = OleDbType.VarChar, 
       Value = txtBoxUsername.Text});
parameters.Add(new OleDbParameter()
      {ParameterName = "@p2, OleDbType = OleDbType.VarChar, 
       Value = textBoxPassword.Text});
parameters.Add(new OleDbParameter()
      {ParameterName = "@p3, OleDbType = OleDbType.VarChar, 
       Value = ContactNo.Text});
parameters.Add(new OleDbParameter()
      {ParameterName = "@p4, OleDbType = OleDbType.Integer, 
       Value = Convert.ToInt32(textBoxReferenceno.Text)});
parameters.Add(new OleDbParameter()
      {ParameterName = "@p5, OleDbType = OleDbType.Integer, 
       Value = Convert.ToInt32(textBoxId.Text)});

new controllerclass().updateDatabase("[User]", query, parameters); 
....

public bool updateDatabase(string type, string query, List<OleDbParameter>parameters) 
{ 
    try
    {
        OleDbCommand cmd = new OleDbCommand(); //open connection
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "UPDATE [" + type + "] SET " + query;
        cmd.Connection = conn;
        cmd.Parameters.AddRange(parameters.ToArray());
        cmd.ExecuteNonQuery(); //execute command
        closeConnection();
        return true;
    }
    ....
}

Still I think that a _generic_doing_it_all_database_work_for_me method_ is not a good practice because there are too many cases to be covered. At least using a parameterized query would help to avoid Sql Injection and parsing problems

Collectives™ on Stack Overflow

Syntax Error in Update Statement C# Microsoft Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related