1

I m working on a project in which I have to use Access Database out of all queries it keeps showing up this Exception

System.Data.OleDb.OleDbException was unhandled
Message = Syntax error in UPDATE statement.
Source = Microsoft Office Access Database Engine
ErrorCode= - 2147217900

In the following query 

public static string updateDailyBalance = "UPDATE DailyBalance SET [{0}] = {1} WHERE [CustomerID] = {2} & [PurchaseMonth] = {3}";

and in the following source code

for (int i = 0; i < dsCustomerBal.Tables[0].Rows.Count; i++)
{
    //{0} = Day, {1} = BalancePoints, {2} = CustomerID, {3} = yyyyMM
    string strQ = Constants.updateDailyBalance;
    strQ = strQ.Replace("{0}", l.ToString());
    strQ = strQ.Replace("{1}", dsCustomerBal.Tables[0].Rows[i]["Bal"].ToString());
    strQ = strQ.Replace("{2}", dsCustomerBal.Tables[0].Rows[i]["CustomerID"].ToString());
    strQ = strQ.Replace("{3}", _LastUpdatedDate.ToString("yyyyMM"));
    Database db1 = DatabaseFactory.CreateDatabase("Deltin");
    DbCommand dbComm1 = db1.GetSqlStringCommand(strQ);
    dbComm1.CommandTimeout = 0;
    int j = db.ExecuteNonQuery(dbComm1);
}

in the line

 int j = db.ExecuteNonQuery(dbComm1);
Improve this question
2
  • Did you debug your code? What is the value of strQ before you execute it? Commented Dec 9, 2014 at 7:36
  • Dudes. learn string.format. YOu can replace all your replace statements with ONE string.format. Better yet use - parameters. BUt this sring maipulation screams "I have no clue about what .NET classes exist". Commented Dec 9, 2014 at 7:49

3 Answers 3

Reset to default
2

There might be other things can be wrong but first thing I see, there is no & operator in WHERE clause.

I think you need to use it like;

WHERE [CustomerID] = {2} AND [PurchaseMonth] = {3}

But more important, instead of string replacing and concatenation, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Define your column values (not names) as a parameter, add them in your for loop as a OleDbParameterCollection, execute your query and clear your parameters with OleDbParameterCollection.Clear() before next iteration.

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

2 Comments

The syntax error probably is caused by a string value passed without proper quoting. Again parameters are the answer
@Steve Yes, but since OP didn't told us what their column types and what are the values, we never know :)
0

As always when doing this type of games - check the full output. The query you send is not the query you have in your code an dcopy/pasting that would really show you the error.

THere is no & in SQL. Which results in a syntax error.

Also learn about .NET base classes. Besides parameters being the answer to avoid SQL injection - your string manipulation is ignorant towards String.Format which would allow you to replace all the replace commands with ONE run. Funny enough as you already use the string.format syntax in your sql string.

That said: This is an invitation to abuse your database due to gross neglect. Read up on SQL Injections and use parameters.

Improve this answer

Comments

0

Hello everyone I solved my question it had to do with null entries in the table that is why update was giving syntax error .Thanks for the tip on SQL injection .Cheers!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.