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

S

Shaitan00

Well-known member
Joined
Aug 11, 2003
Messages
343
Location
Hell
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 isnt working at all.

The Database itself is as follows:
FileName: C:\DB.mdb
TableName = Players
Column: ID - Autonumber
Column: Players - Text
Column: Total - Text

Code: 
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,
 
Shaitan00 said:
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");
Click to expand...
Youre soo close....
Datatype mismatch, youre 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
 
Or, at the risk of sounding like a stuck record, if you had used a parameterised query rather than relying on string concatenation this wouldnt have been a problem.

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