how to refill a specific row in Data Table in C# asp net

Question

hello everyone. i have a data table, tblrating and i will refill a specific row in it. i have selected that row and hen insert the values, wihtout any result???!!! the codes are as follow:

sxtring q = string.Format("select * from tblrating where Nr='{0}'", rew_n[0]);
command.CommandText = q;

int amountOfUsers = Convert.ToInt32(command.ExecuteScalar());
if (amountOfUsers >= 1) //the row exist
{
int rew = (j + rew_n[3] * (rew_n[1] - 1)) / rew_n[1];
q = string.Format(@"INSERT INTO tblrating (Email,Title,Visitors,rating,review,Nr)VALUES
('" + adEmail.Text + "','" + s[1].ToString() + "','" + rew_n[1]++ + "','" + j + "','" + rew + "','" + rew_n[0] + "')");

command.CommandText = q;
command.ExecuteNonQuery();
}

i tried with:

q = string.Format(@"INSERT INTO tblrating (Email,Title,Visitors,rating,review,Nr)VALUES
('" + adEmail.Text + "','" + s[1].ToString() + "','" + rew_n[1]++ + "','" + j + "','" + rew + "','" + rew_n[0] + "') where Nr='{0}'", rew_n[0]);

but got error: i appreciate any help... kind regards

Amit Mohanty · Answer

I think here you're trying to update a specific row in the tblrating table if it already exists based on the value of Nr , and if it doesn't exist, insert a new row. However, your SQL syntax seems to be a bit mixed up. string q = string.Format("SELECT COUNT(*) FROM tblrating WHERE Nr='{0}'", rew_n[0]); command.CommandText = q; int amountOfUsers = Convert.ToInt32(command.ExecuteScalar()); if (amountOfUsers >= 1) //the row exists { int rew = (j + rew_n[3] * (rew_n[1] - 1)) / rew_n[1]; q = string.Format(@"UPDATE tblrating SET Email='" + adEmail.Text + "', Title='" + s[1].ToString() + "', Visitors='" + rew_n[1]++ + "', rating='" + j + "', review='" + rew + "' WHERE Nr='" + rew_n[0] + "'"); } else { int rew = (j + rew_n[3] * (rew_n[1] - 1)) / rew_n[1]; q = string.Format(@"INSERT INTO tblrating (Email, Title, Visitors, rating, review, Nr) VALUES ('" + adEmail.Text + "','" + s[1].ToString() + "','" + rew_n[1]++ + "','" + j + "','" + rew + "','" + rew_n[0] + "')"); } command.CommandText = q; command.ExecuteNonQuery();

Lokesh Varman · Answer

It seems like you are trying to update an existing row in the "tblrating" table based on the condition that the row with the specified "Nr" value exists. To achieve this, you should use an UPDATE statement instead of an INSERT statement. The UPDATE statement is used to modify existing records in a table. Here's how you can modify your code to use an UPDATE statement: string q = string.Format("SELECT * FROM tblrating WHERE Nr='{0}'", rew_n[0]); command.CommandText = q; int amountOfUsers = Convert.ToInt32(command.ExecuteScalar()); if (amountOfUsers >= 1) // The row exists { int rew = (j + rew_n[3] * (rew_n[1] - 1)) / rew_n[1]; q = string.Format(@"UPDATE tblrating SET Visitors = Visitors + 1, rating = '" + j + @"', review = '" + rew + @"' WHERE Nr = '" + rew_n[0] + "'"); command.CommandText = q; command.ExecuteNonQuery(); }

Cr Bhargavi · Answer

It looks like you're trying to perform an INSERT operation with a WHERE clause, which is not the correct syntax. An INSERT statement is used to add new rows to a table, and it doesn't have a WHERE clause since it's meant to create new records. If you want to update an existing row in the table, you should use the UPDATE statement. string q = string.Format("SELECT * FROM tblrating WHERE Nr='{0}'", rew_n[0]); command.CommandText = q; int amountOfRows = Convert.ToInt32(command.ExecuteScalar()); if (amountOfRows >= 1) // The row exists { int rew = (j + rew_n[3] * (rew_n[1] - 1)) / rew_n[1]; q = string.Format(@"UPDATE tblrating SET Email = '{0}', Title = '{1}', Visitors = '{2}', rating = '{3}', review = '{4}' WHERE Nr = '{5}'", adEmail.Text, s[1].ToString(), rew_n[1]++, j, rew, rew_n[0]); command.CommandText = q; command.ExecuteNonQuery(); }