SqlCe Where clause problem

Question

I have a simple sqlce database, trying to update a row in a table. The following command works:

UPDATE ConsoleUsage SET TotalCircuits = 123

But the above command updates all rows, so what I want is this:

UPDATE ConsoleUsage SET TotalCircuits = 123 WHERE Index = 912

The intent is to update a single row in the table. But this command fails, giving the following cryptic error message (here using MS suggested format):

Error Code: 80040E37
Message   :  [ UPDATE ConsoleUsage SET TotalCircuits = 123 WHERE Index = 912 ]
Minor Err.: 0
Source    : SQL Server 2005 Mobile Edition ADO.NET Data Provider
Err. Par. : UPDATE ConsoleUsage SET TotalCircuits = 123 WHERE Index = 912

The "Index" column is an index to the table, I've checked all spelling and I've tried this both with and without cmd.CommandType = CommandType.TableDirect; and cmd.IndexName = "MainIndex";

What am I missing?

Answer 1

I think "Index" is a reserved word. Try surrounding it with brackets:

UPDATE ConsoleUsage SET TotalCircuits = 123 WHERE [Index] = 912

Answer 2

Without knowing the table structure (could you post the create script for the table?) here are a few things I would try:

put square brackets ([]) around all columns and table name to escape them out, just in case put the schema name of the table to the table, dbo.[ConsoleUsage] instead of [ConsoleUsage] check to see if [Index] is a char field or not, use '912' instead of 912 if it is

Try converting statement a little: UPDATE T SET TotalCircuits = 123 FROM dbo.[ConsoleUsage] T WHERE T.[Index] = 912