CODEWITHSUNDEEP
c#database
LHammy
LHammy

Reputation: 17

Update one boolean field in Access Database c#

I have been beating my head against a wall and keep getting a vague error "Syntax error in UPDATE statement". Can anyone please tell me what is wrong with my update statement?

try 
{
    OleDbConnection ECon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\zoofari\zoofari\zoofari\bin\Debug\MainData.accdb");
    OleDbDataAdapter EdAdapt = new OleDbDataAdapter();
    EdAdapt.UpdateCommand = new OleDbCommand("UPDATE tbl_Results SET @Q1-Easy WHERE Username = '" +txtUname.Text+ "'", ECon);
    EdAdapt.UpdateCommand.Parameters.Add("@Q1-Easy", OleDbType.Boolean).Value = true;

    ECon.Open();
    EdAdapt.UpdateCommand.ExecuteNonQuery();
    ECon.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

Upvotes: 0

Views: 1821

Answers (2)

Steve
Steve

Reputation: 216293

Where is the field name to update? The syntax required for the update command is 

UPDATE <tablename> SET <Field> = <value> WHERE <keyfield> = <value>

your query instead has no field to update

 EdAdapt.UpdateCommand = new OleDbCommand("UPDATE tbl_Results" + 
                        " SET FIELDNAME= @Q1-Easy WHERE Username = '" +txtUname.Text+ "'", ECon);
                              ^^^^^^^^^^

Note also that you should never use string concatenation to build sql commands.
Use always a parametrized query. (You have already a parameter in this query, why not use another one?)

Seeing your comment below about the field name then your code could be changed in this way

string sqlText = "UPDATE tbl_Results SET [Q1-Easy] = ? WHERE Username = ?";
using(OleDbConnection ECon = new OleDbConnection(.....))
using(OleDbCommand cmd = new OleDbCommand(sqlText, ECon))
{
   ECon.Open();
   cmd.Parameters.Add("@Q1Value", OleDbType.Boolean).Value = true;
   cmd.Parameters.Add("@uname", OleDbType.VarChar).Value = txtUname.Text;
   cmd.ExecuteNonQuery();
}

I have added the using statement to be sure that the connection is closed and disposed when done and I have enclosed in square brackets the field name because I am not sure that the - is accepted as a valid character for field names.

Note also that in OleDb the parameters placeholder could be a simple ? but it is very important to add them in the collection accordingly to the order in which the placeholder appears in the text. OleDb doesn't recognize the parameter placeholders by name.

Upvotes: 2

Andrew Rasmussen
Andrew Rasmussen

Reputation: 15099

Not sure if @Q1-Easy is a field name or a value, but you need to set a field to something:

UPDATE table SET field = value WHERE field = value;

You've got the first "field = value" part wrong.

Upvotes: 0

Related Questions