Update command in c# not updating database
I have found a work around that solved this particular problem: the data from the database is loaded into one set of controls, and the update information is enetred into a separate set of controls. Thanks for the comments; the application is working.
Trying to update a database row. I wrote the following code based on what I found on several fora/websites. he code is not updating the database.
After trying anything to fix this error I have discovered the following: the data开发者_JAVA技巧base will update properly if the value of the @editFirstName is a literal, as in
UpdateCmd.Parameters["@editFirstName"] = "George";
I also tried
UpdateCmd.Parameters["@editFirstName"] = "'" + editFirstNameContent.Text + "'";
which did not work.
I also tried to put the TextBox data into a variable and use the variable:
string firstNameValue = editFirstNameContent.Text;
UpdateCmd.Parameters["@editFirstName"] = firstNameValue;
which did not work.
Therefore, the UPDATE command is apparently updating the databade. However, apparently the Parameters.Value line is not reading the data in the TextBox, and so it is not updating with the changed value.
protected void editCustomerButton_Click(object Sender, EventArgs e)
{
string connString = WebConfigurationManager.ConnectionStrings["proofreadThePlanetConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connString))
{
connection.Open();
string updateString = "UPDATE tblCustomerInfo SET customerID=@editCustomerID, customerFirstName=@editFirstName, customerLastName=@editLastName, customerEmail=@editEmail WHERE customerID=" + Request.QueryString["EID"];
SqlCommand UpdateCmd = new SqlCommand(updateString, connection);
UpdateCmd.Parameters.Add("@editCustomerID", SqlDbType.Int);
UpdateCmd.Parameters["@editCustomerID"].Value = editCustomerIDContent.Text;
UpdateCmd.Parameters.Add("@editFirstName", SqlDbType.VarChar, 25);
UpdateCmd.Parameters["@editFirstName"].Value = editFirstNameContent.Text;
UpdateCmd.Parameters.Add("@editLastName", SqlDbType.VarChar, 50);
UpdateCmd.Parameters["@editLastName"].Value = editLastNameContent.Text;
UpdateCmd.Parameters.Add("@editEmail", SqlDbType.VarChar, 75);
UpdateCmd.Parameters["@editEmail"].Value = editEmailContent.Text;
UpdateCmd.ExecuteNonQuery();
}
}
I'm not sure how MS SQL Server handles auto-commit, but in case the auto-commit mode is not turned on, you'll need to commit your transaction manually (UpdateCmd.Transaction.Commit
).
SQL Profiler comes with SQL Management Studio. Run this while executing your code to see what command is actually being executed.
Here's a tutorial on how to use the profiler.
http://sqlserverpedia.com/wiki/Using_SQL_Server_Profiler
I would check that
Request.QueryString["EID"]
does indeed contain a correct customerId value, as this could cause the statement to run but no data to be updated, if you are trying to update a row which does not exist.
To ensure the parameters values are being set correctly you could try the following format;
command.Parameters.Add("@editCustomerID", SqlDbType.Int).Value = editCustomerIDContent.Text;
As suggested by Ash, the best way to find out exactly what is happening would be to run Profiler against the database while the command is being executed (Note: This can have a performance impact and should be done on a Test/Dev system or out-of-hours)
精彩评论