I am getting error as "cannot update ID" in update table.plz help me resolving this

Question

0.00/5 (No votes)

See more:

I am getting error as "cannot update ID" in update table.plz help me resolving this

What I have tried:

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Submit")
{
using (OleDbConnection con1 = new OleDbConnection(con))
{
con1.Open();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con1;
//string query1 = ("insert into Reports (ID,Comments,marks,Reviewer,ReviewerDate) values(@id,@comments,@marks,'" + Environment.UserName + "',Now)");
string query1 = ("Update Reports set ID = @id,Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where(ID = @id)");
cmd.Parameters.AddWithValue("@id", dataGridView1.Rows[i].Cells[0].Value);
cmd.Parameters.AddWithValue("@comments", dataGridView1.Rows[i].Cells[5].Value);
cmd.Parameters.AddWithValue("@marks", dataGridView1.Rows[i].Cells[6].Value);
cmd.Parameters.AddWithValue("@rvr", dataGridView1.Rows[i].Cells[8].Value);
cmd.Parameters.AddWithValue("@rvrd", dataGridView1.Rows[i].Cells[9].Value);

cmd.CommandText = query1;
cmd.ExecuteNonQuery();

}
MessageBox.Show("Comments Submitted", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

Posted 7-Jun-18 4:33am

Prateek gsharma

Updated 7-Jun-18 5:00am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-06-07T04:59:00

Solution 2

In addition to what Nathan says, think about it: even if you could update the ID value, is there any point?

Update Reports set ID = @id,Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where(ID = @id)

Given that the WHERE clause ensures that only rows with the ID set to a specific value will be altered, what effect would trying to set the ID to that specific value have?

Posted 7-Jun-18 4:59am

OriginalGriff

Comments

Prateek gsharma 8-Jun-18 0:53am

string query1 = ("Update Reports set Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where (ID = @id)");

In the access database
ID data type is AutoNumber
ID field size Long Integer
New values : Increment

But above query is not making any changes to perticular column/Row
is there any other query to make changes in database?

Prateek gsharma 8-Jun-18 7:03am

moreover i am using this query in Datagridview where i have change the values & update it in database.

Nathan Minier · Answer 2 · 2018-06-07T04:50:00

Solution 1

Because the ID is the row identity, you're not going to be able to change it like this. Fix this line:

C#

string query1 = ("Update Reports set Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where(ID = @id)");

Posted 7-Jun-18 4:50am

Nathan Minier

Comments

Prateek gsharma 7-Jun-18 12:03pm

if i do so....it is not updating in database.

Nathan Minier 7-Jun-18 12:06pm

Okay, are you getting an error at this juncture?

Prateek gsharma 7-Jun-18 12:16pm

no error.but only earlier values are displaying

Nathan Minier 7-Jun-18 12:34pm

Make sure that the ID parameter is being passed correctly, and properly reflects the ID type in the database.

Prateek gsharma 7-Jun-18 12:58pm

i passed it correctly but it is not updating any values

Nathan Minier 7-Jun-18 13:18pm

Okay, does the account that is running the app have update rights?

Prateek gsharma 7-Jun-18 13:20pm

yes

Nathan Minier 7-Jun-18 13:41pm

Allright, then you need to troubleshoot your SQL and do a step-through on your code. The most likely scenario for no exception/no update is that the "WHERE" clause of the statement is not finding anything, but it you're certain that ID is being passed correctly and that ID exists in the database, I'm not sure what else it would be based on your code sample.

Prateek gsharma 8-Jun-18 0:52am

string query1 = ("Update Reports set Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where (ID = @id)");

In the access database
ID data type is AutoNumber
ID field size Long Integer
New values : Increment

But above query is not making any changes to perticular column/Row
is there any other query to make changes in database?

Nathan Minier 8-Jun-18 7:07am

You didn't mention that it's an Access database, so it might behave differently. That string should be fine if it works like every other SQL implementation.

Prateek gsharma 8-Jun-18 7:23am

it works like SQL only.as i am updating data in Datagriview using button in datagridview

Prateek gsharma 8-Jun-18 7:04am

moreover i am using this query in Datagridview where i have change the values & update it in database.

MadMyche · Answer 3 · 2018-06-07T05:02:00

Solution 3

The most likely problem is that ID is most likely the Identity column for the table.
If you look at the actual SQL statement itself

SQL

Update    Reports
set       ID = @id
,         Comments = @comments
,         marks = @marks
,         Reviewer = @rvr
,         ReviewerDate = @rvrd
Where    (ID = @id)

You may scratch your head when you think about the logic-- change the ID on the selected row when the ID matches the one provided; Why would you need to change the value to what it already is becomes the question.
The answer is you don't need to; and by not attempting to change it you will not get the exception message you received.

So all you need to do is remove that portion of the SQL Command...

SQL

Update    Reports
set       Comments = @comments
,         marks = @marks
,         Reviewer = @rvr
,         ReviewerDate = @rvrd
Where    (ID = @id)

... and then you should be good to go.
Presuming that ID is the identity column naturally

Posted 7-Jun-18 5:02am

MadMyche

Comments

Prateek gsharma 7-Jun-18 12:08pm

now i have done like above but i unable to update the changes to my row.

Prateek gsharma 7-Jun-18 12:12pm

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Submit")
{
using (OleDbConnection con1 = new OleDbConnection(con))
{
con1.Open();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con1;
//string query1 = ("insert into Reports (ID,Comments,marks,Reviewer,ReviewerDate) values(@id,@comments,@marks,'" + Environment.UserName + "',Now)");
string query1 = ("Update Reports set Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where (ID = @id)");
cmd.Parameters.AddWithValue("@id", dataGridView1.Rows[i].Cells[0].Value);
cmd.Parameters.AddWithValue("@comments", dataGridView1.Rows[i].Cells[5].Value);
cmd.Parameters.AddWithValue("@marks", dataGridView1.Rows[i].Cells[6].Value);
cmd.Parameters.AddWithValue("@rvr", dataGridView1.Rows[i].Cells[8].Value);
cmd.Parameters.AddWithValue("@rvrd", dataGridView1.Rows[i].Cells[9].Value);

cmd.CommandText = query1;
cmd.ExecuteNonQuery();

}
MessageBox.Show("Comments Submitted", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

Prateek gsharma 7-Jun-18 12:14pm

even though made changes to my query.....values are not updated.

no change in values.this query is not updating any values

MadMyche 7-Jun-18 12:58pm

Have you tried this directly on SQL?

Prateek gsharma 8-Jun-18 0:53am

string query1 = ("Update Reports set Comments = @comments,marks = @marks,Reviewer = @rvr,ReviewerDate = @rvrd Where (ID = @id)");

In the access database
ID data type is AutoNumber
ID field size Long Integer
New values : Increment

But above query is not making any changes to perticular column/Row
is there any other query to make changes in database?

Prateek gsharma 8-Jun-18 7:04am

moreover i am using this query in Datagridview where i have change the values & update it in database.

Prateek gsharma 7-Jun-18 13:21pm

no one able to solve my issue?

MadMyche 8-Jun-18 10:02am

Now that I know we are working with an AccessDB, this points to a possible problem with the code => You are using the OleDB library to connect to the DB, which does not support named parameters; all parameters should be ? (question marks) and the parameters need to be added in the exact same order as they referenced in the command.
https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx