CODEWITHSUNDEEP
c#winformsoledb
Bon
Bon

Reputation: 309

Syntax error in UPDATE statement OleDb Exception in C#

I already double check my SQL Statement and it seems that my SQL Statement is Error. I don't why it doesn't work. My SQL Statement is correct and It resulted to this OleDBException.

Here is the code

public void updateAccount(Int32 accountid, String username, String password, String isdisable)
{
     con.ConnectionString = db.konek();
     String sql = "UPDATE accounts SET username = @username, password = @password, isdisable = @isdisable WHERE accountid = @accountid";
     try
     {
         con.Open();
         OleDbCommand cmd = new OleDbCommand(sql, con);
         cmd.Parameters.AddWithValue("@username", username);
         cmd.Parameters.AddWithValue("@password", password);
         cmd.Parameters.AddWithValue("@isdisable", isdisable);
         cmd.Parameters.AddWithValue("@accountid", accountid);
         cmd.ExecuteNonQuery();
     }
     finally
     {
         con.Close();
     }
}

Screenshots of my MS Access Table enter image description here

Exception Screenshot enter image description here

Upvotes: 0

Views: 6754

Answers (3)

josef
josef

Reputation: 5959

the code for update is for storedprocedures in SQL server and may not work with access DB:

        string _Update_Emp = "UPDATE AlbahraniNetwork SET FirstName=@FirstName,SecondName=@SecondName,LastName=@LastName,Phone=@Phone,Cell=@Cell,Email=@Email,Address=@Address where FirstName=@FirstName";

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);
        if (!appPath.EndsWith("\\"))
            appPath += "\\";
        _Conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");

        //_Conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb";
        _Conn.Open();
        OleDbCommand _Update_Command = new OleDbCommand(_Update_Emp, _Conn);

        _Update_Command.Parameters.AddWithValue("@FirstName", FirstName.Text);
        _Update_Command.Parameters.AddWithValue("SecondName", SecondName.Text);
        _Update_Command.Parameters.AddWithValue("@LastName", LastName.Text);
        _Update_Command.Parameters.AddWithValue("@Phone", Phone.Text);
        _Update_Command.Parameters.AddWithValue("@Cell", Cell.Text);
        _Update_Command.Parameters.AddWithValue("@Email", Email.Text);
        _Update_Command.Parameters.AddWithValue("@Address", Address.Text);
        _Update_Command.ExecuteNonQuery();

Make it simple and instead use:

  string _Update_Emp = "UPDATE AlbahraniNetwork SET " +
       FirstName=\"" + FirstName.Text + "\"" +
       ",SecondName=\"" + SecondName.Text  + "\"" +
       ",LastName=\""+ LastName.Text  + "\"" +
       ",Phone=\""+ Phone.Text + "\"" +
       ",Cell=\"" + Cell.Text + "\"" +
       ",Email=\"" + Email.Text + "\"" +
       ",Address=\"" + Address.Text + "\"" +
       " where FirstName=\"" + FirstName.Text +";";

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);
        if (!appPath.EndsWith("\\"))
            appPath += "\\";
        _Conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");

        //_Conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb";
        _Conn.Open();
        OleDbCommand _Update_Command = new OleDbCommand(_Update_Emp, _Conn);

Upvotes: 1

Steve
Steve

Reputation: 216352

You are using a MICROSOFT JET reserved word PASSWORD.
This is the origin of your syntax error.

You should encapsulate your sql command with square brackets

 String sql = "UPDATE accounts SET username = @username, [password] = @password, "  + 
              "isdisable = @isdisable WHERE accountid = @accountid";

And for the named parameters problem.
As far as Microsoft Access is concerned, the provider Microsoft.ACE.OLEDB.12.0 allows you to use the syntax above compatible with SqlServer. However you should respect the correct order of parameter placeholders when inserting your parameters in the OleDbParameter collection of the OleDbCommand

Upvotes: 5

Tobsey
Tobsey

Reputation: 3400

When you add a named parameter like that, the text is not replaced in the sql query. You need to use "?" in the sql string and then the parameters will be replaced:

string sql = "UPDATE accounts SET username = ?, password = ?, isdisable = ? WHERE accountid = ?"

What you are doing there with the named parameters only works for procedures, not text.

If your command was set as follows:

cmd.CommandType = CommandType.StoredProcedure

then named parameters would work. However you are using CommandType.Text, which is the default, and therefore you need to use the "?" in order to get the OLEDB driver to use your parameters.

Upvotes: 2

Related Questions