CODEWITHSUNDEEP
c#ms-access
Tom
Tom

Reputation: 175

How to use an Auto Number field in Access DB in UPDATE statement in c#?

I met this error when I used this as my OleDbCommand

"UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [ID] = '" + nPlayer + "'"

Error:
Database Error - Unable to Write:
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

this ID in the WHERE clause is an Auto number field in access database nPlayer is integer variable

this works when the value is hard coded like this ex- if value of nPlayer variable is 3 then 

"....... WHERE [ID] =  3"

can some one tell me how to correct this error

Upvotes: 0

Views: 2178

Answers (2)

rfcdejong
rfcdejong

Reputation: 2320

U should not concat your query, but instead use dbparameters.. Something like this

string sql = "UPDATE [Players] SET [Players] = ? WHERE [ID] = ?";
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.Add("@playerName", OleDbType.VarChar, 20).Value = sPlayerName;
cmd.Parameters.Add("@playerId", OleDbType.Integer).Value = 3;

conn.Open();
cmd.ExecuteNonQuery();

Why? 2 Reasons

1) SQL Injection when a player would be able to enter a SQL statement into his name. For a reference see http://en.wikipedia.org/wiki/SQL_injection

2) Performance, the query will be the same and the SQL optimizer does not have to re-analyse the query plan for it. For a reference see http://www.codeproject.com/Articles/16994/Use-SQL-Parameters-to-Overcome-Ad-Hoc-Performance

Upvotes: 1

balexandre
balexandre

Reputation: 75073

It's quite easy

Data type mismatch in criteria expression.

This means that you are passing a string and you should pass a number

In this case, you should not use the single quote in the field, so the WHERE clause needs to change from this

..."WHERE [ID] = '" + nPlayer + "'"

to this

..."WHERE [ID] = " + nPlayer

Upvotes: 3

Related Questions