Updating Access Table From C# code

Question

I want to update a field based on a condition
if age <25 then class = 10 else class = 20;
so far I have tried these

 cmd = new OleDbCommand("update workClass Set Class = 10  when " + ds.Tables["no"].Rows[i][1] + " < 25   else Set Class =  20 ", con);
 cmd.ExecuteNonQuery();

second

cmd = new OleDbCommand("update workClass Set Class = 10  where" + ds.Tables["no"].Rows[i][1] + " < 25   else Set Class =  20 ", con);
     cmd.ExecuteNonQuery();

I get this error message
note the ds.tables return 33in the first iteration so that bit works fine

Syntax error (missing operator) in query expression '10 when 33 < 25 else Class = 20'.

Sorry, this does not make sense. Why are you essentially hard coding 33 and comparing it to 25? Is this actually coming from each record and are you doing this is some sort of loop? — Igor
– Igor, Commented May 23, 2018 at 12:57
Maybe you are trying to do this across the board for all records and for each record compare the records value in column X to 25? It would help if you included the code around these 2 lines. — Igor
– Igor, Commented May 23, 2018 at 13:00

Steve · Accepted Answer · 2018-05-23 12:57:32Z

1

There is no WHEN clause in the JET engine that powers the Microsoft Access Database.
You need to use the IIF function like this

cmd = new OleDbCommand(@"update workClass Set Class = IIF(" + 
    ds.Tables["no"].Rows[i][1] + " < 25, 10, 20)", con);

However I suggest you to use a parameterized query to avoid possible parsing error and sql Injection

cmd = new OleDbCommand(@"update workClass Set Class = IF(@value < 25, 10, 20)", con);
cmd.Parameters.Add("@value", OleDbType.Integer).Value = 
    Convert.ToInt32(ds.Tables["no"].Rows[i][1]);

answered May 23, 2018 at 12:57

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

Igor Over a year ago

I have a feeling the OP is using this in a loop and reading the value in a data table and then applying it to an update. I think there is much more wrong with the code but that code is not included in the question....

Steve Over a year ago

@Igor perhaps you are correct but, as it stands, his problem (the syntax error) is caused by an unknown (to access) keyword WHEN. Probably we could optimize the code if we know more.

Skeptical Ant Over a year ago

yes I did an awful mistake by using a loop I was working with insert earlier and that confused me.. never the less the problem wasn't that.. @Igor

Igor · Accepted Answer · 2018-05-23 13:06:45Z

I have a feeling this is what you are after.

cmd = new OleDbCommand("UPDATE workClass SET [Class] = IIF([Age] < 25, 10, 20)", con);
cmd.ExecuteNonQuery();

What is missing though is a descriminator so you only update a specific record or set of records based on a filter. As written the query you have and this one updates all records each time it is executed. To add the where clause be sure to add the filter using a parameter and do not use string concatenation.

Example:

cmd = new OleDbCommand("UPDATE workClass SET [Class] = IIF([Age] < 25, 10, 20) WHERE [someId] = ?", con);
cmd.Parameters.Add("@someId", OleDbType.Integer).Value = yourIdValue;
cmd.ExecuteNonQuery();

It is important to note that parameters are ordered in Ms Access and not named. The first parameter encountered in the query must correspond to the first parameter in the parameter collection.

Collectives™ on Stack Overflow

Updating Access Table From C# code

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related