Error Updating a row in MS Access based on AutoNumber? [C#]

[Shaitan00]

I am using Microsoft Access 2003 as a Database and I have encountered a really odd problem...

I am trying to UPDATE a row based on the [iD] column which is of type AUTONUMBER - sadly this isn't working at all.

 

The Database itself is as follows:

FileName: C:\DB.mdb

TableName = Players

Column: ID - Autonumber

Column: Players - Text

Column: Total - Text

 

for (int nPlayer = 1; nPlayer < nPlayersCount+1; nPlayer++)
{
oDB.Write("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()

 

But if I try the following it works perfectly fine:

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = 1");

 

So the problem resides with my "WHERE [iD] = '" + nPlayer + "'" part, so I tried all the following combinations to see if one would work

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = '" + nPlayer.ToString() + "'");

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = 'nPlayer'");

None of them work, they all seem to generate the SAME error message.

 

Any help, hints, or advice would be greatly appreciated

Thanks,

[kejpa]

But if I try the following it works perfectly fine:

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = 1");

 

So the problem resides with my "WHERE [iD] = '" + nPlayer + "'" part, so I tried all the following combinations to see if one would work

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = '" + nPlayer.ToString() + "'");

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = 'nPlayer'");

You're soo close....

Datatype mismatch, you're assigning an integer field with at string since you enclose nPlayer in quotes, try

oDB.Write("UPDATE [Players] SET [Players] = '" + sPlayerName + "' WHERE [iD] = " + nPlayer );

 

HTH

/Kejpa

[PlausiblyDamp]

Or, at the risk of sounding like a stuck record, if you had used a parameterised query rather than relying on string concatenation this wouldn't have been a problem.

 

String concatenation is an error prone and insecure way to do SQL.