Update one boolean field in Access Database c#

Question

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);
}

Steve · Accepted Answer · 2013-04-04T20:35:18.943

2

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.

edited Apr 04 '13 at 20:35

answered Apr 04 '13 at 20:28

Steve

213,761
22
232
286

Hi Steve, Q1-Easy is the fieldname, I'm trying to change the value of it from false to true. Is this the wrong way to go about it? – LHammy Apr 04 '13 at 20:30
Sorry didn't mean to post so quick, is this update statment correct? EdAdapt.UpdateCommand = new OleDbCommand("UPDATE tbl_Results" + "SET @Q1-Easy = true WHERE Username = '" +txtUname.Text+ "'", ECon); – LHammy Apr 04 '13 at 20:38
No, true doesn't work. You pass -1 if you want to put the value directly in the string (In Access while SqlServer wants 1) . As I have said. USE always a parametrized query. Look at [Sql Injection problem here](http://stackoverflow.com/questions/332365/how-does-this-sql-injection-work) – Steve Apr 04 '13 at 20:41
thank you so much Steve, I amended my code as you said and it's working! – LHammy Apr 04 '13 at 20:46
Your note about OleDb ignoring the names of the placeholders are what helped me. Why does OleDb even allow you to name the parameters if it doesn't take any notice of them in the sql? – Mark Farmiloe Feb 04 '16 at 12:17
Well, the command text is passed as is to the database engine with no intervention from the provider, while the command parameters collection needs to have a name for each parameter albeit in OleDb you could use the same name for all parameters. Probably it is required by the base classes and maintain consistency with a baseline for all providers. (Just my guess) – Steve Feb 04 '16 at 12:24

score 0 · Answer 2 · answered Apr 04 '13 at 20:28

0

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.

answered Apr 04 '13 at 20:28

Andrew Rasmussen

14,912
10
45
81

Update one boolean field in Access Database c#

2 Answers2