CODEWITHSUNDEEP
c#sqlsql-serverdatabase
Dr Archer
Dr Archer

Reputation: 348

Inserting new row in SQL database unable to because of primary key

Whenever I try to insert a new row into this table, it gives me the error that I cannot modify the column (because it is the primary key). So i exclude it from the query, but then it returns that the query and the number of columns is different. How can I add a row, with a primary key?

Here's the code to insert:

var getmessages = new SqlCeCommand("Insert into chat values('" + txtSend.Text + "', '" + Environment.UserName + "', '" + user + "', '" + Environment.UserName + "', '" + DateTime.Now.ToShortTimeString() + "', '" + DateTime.Now.ToString() + "', '"+UID+"')", con);

Here's the primary key that I'm adding via query (as it's an update to the database)...

alter table chat add c_id int identity primary key

And then it throws me the error enter image description here

And if I remove the (UID) from the insert query I get this error...

enter image description here

Upvotes: 0

Views: 2852

Answers (4)

Nitu Bansal
Nitu Bansal

Reputation: 3856

You need to define parameter also with values in your query like below.

  insert into table(f1,f2,f3) values(v1,v2,v3)

Upvotes: 0

Dour High Arch
Dour High Arch

Reputation: 21722

You have a serious SQL error in your code; you have to use parameters and specify each column:

var insert = new SqlCeCommand("Insert into chat (sendCol, firstNameCol, secondNameCol, firstTimeCol, secondTimeCol, uidCol) values (@send, @name, @name, @time, @time, @uid)";
insert.Parameters.Add(new SqlCeParameter("@send", txtSend.Text));
insert.Parameters.Add(new SqlCeParameter("@name", Environment.UserName));
insert.Parameters.Add(new SqlCeParameter("@time", DateTime.Now));
insert.Parameters.Add(new SqlCeParameter("@uid", Something));

Upvotes: 0

ChrisLively
ChrisLively

Reputation: 88092

You need to tell SQL CE exactly what columns you are inserting into.

The first problem isn't because it's a primary key. It's because the key is an identify field and auto generated for you. So, let's move on.

The Second problem is simply that you are not specifying the columns you are inserting into.

The insert should look like:

insert into chat(field1, field2, field3) values('val1', 'val2', 'val3')

Notice the field names. You'll have to put the actual field names you have there.

That said, the code is still pretty bad. Although the above works, you really really really should parameterize your queries. Even assuming this program is never actually released into the real world and assuming that you could care less if someone hacks into it... It's still good practice to do things the right way. Oh.. and you wouldn't have problems with ' marks.. which would cause your app to crash.

Upvotes: 5

Szymon
Szymon

Reputation: 43023

You need to specify all columns apart from the primary key in your query:

Insert into chat (column1, column2, ...) values (...)

Upvotes: 1

Related Questions