Cant insert values into sql server ce database

httpete

I get an exception when I try to add a row to my database with the following code. The exception says 'The number of columns in the query and the table must match. [ Number of columns in query = 5, Number of columns in table = 6 ]'

There are 6 columns in the table because I have column named 'id' as my primary key. I have it set to -
Identity: True
Identity Increment: 1
Identity Seed: 1


So why won't this code work, should't the primary key get inserted automatically when I am inserting these values?

using (SqlCeCommand com = new SqlCeCommand(@"INSERT INTO cars
                    VALUES(@make, @year, @color, @mileage, @price)", con))
                {
                    com.Parameters.AddWithValue("@make", car.Make);
                    com.Parameters.AddWithValue("@year", car.Year);
                    com.Parameters.AddWithValue("@color", car.Color);
                    com.Parameters.AddWithValue("@mileage", car.Mileage);
                    com.Parameters.AddWithValue("@price", car.Price);
                    com.ExecuteNonQuery();
                }

Beano

you need to specify the names of the columns you are inserting into. What you have their only works if you are inserting a value into all columns. your sql hould look like :

INSERT INTO cars (make, year, color, mileage, price)
VALUES(@make, @year, @color, @mileage, @price)

Malice

I've never used SQL CE but the syntax for SQL Server 2008 suggests that what the thread starter is proposing should work. See the definition here

(column_list)

Is a list of one or more columns in which to insert data. column_list must be enclosed in parentheses and delimited by commas.

If a column is not in column_list, the Database Engine must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. The Database Engine automatically provides a value for the column if the column:

Has an IDENTITY property. The next incremental identity value is used.

Has a default. The default value for the column is used.

Has a timestamp data type. The current timestamp value is used.

Is nullable. A null value is used.

Is a computed column. The calculated value is used.

column_list and a values list must be used when explicit values are inserted into an identity column, and the SET IDENTITY_INSERT option must be ON for the table.

I've just checked against an SQL Server 2008 database here and the following query worked:

[PHP]INSERT INTO EmailLog
VALUES ('1B63CCFD-4BD4-440C-BFD2-6433116A5130',
'Vendor Update Test',
'2011-08-15 13:32:03.497',
'support@example.com',
'destination@example.com',
'TEST',
1) [/PHP]

Without getting into the specifics of how my table is set up, the key point is that there is a LogID column defined as the primary key which you'll see I haven't specified nor have I provided a list of columns to match the values to.

In case it's any help this is the column definition for LogID. Maybe there's something in there that's different to the definition of your ID column?

Beano

fair point Malice. Serves me right for thinking about tech stuff on a sunday evening.

Malice

Beano wrote: »

fair point Malice. Serves me right for thinking about tech stuff on a sunday evening.

Well you were right to a point and either way, I still can't see where httpete's error is coming from. Hopefully there's just something different in the column definition.

httpete

My primary key is up exactly the same way. Beano's suggestion worked so maybe its just something do with compact edition. I think the documentation available on compact edition is absolutely terrible, Im searching all over the net and I can only find little snippets here and there, its really slowing me down.

And one related question I would love if I could get some advice on -
When I am declaring the string (nvarchar) data types for my columns I am not sure what length to make them. Should I use multiples of 16 or 32?

httpete

My primary key is set up exactly the same way. Beano's suggestion worked so maybe its just something do with compact edition. I think the documentation available on compact edition is absolutely terrible, Im searching all over the net and I can only find little snippets here and there, its really slowing me down.

And one related question I would appreciate if I could get some advice on -
When I am declaring the string (nvarchar) data types for my columns I am not sure what length to make them. Should I use multiples of 16 or 32? Or is it fine to make them any arbitrary size?

Beano

httpete wrote: »

My primary key is set up exactly the same way. Beano's suggestion worked so maybe its just something do with compact edition. I think the documentation available on compact edition is absolutely terrible, Im searching all over the net and I can only find little snippets here and there, its really slowing me down.

And one related question I would appreciate if I could get some advice on -
When I am declaring the string (nvarchar) data types for my columns I am not sure what length to make them. Should I use multiples of 16 or 32? Or is it fine to make them any arbitrary size?

not to be funny but you make as big as they need to be. i'm not aware of any benefit of using multiples of 16 or 32.