3

I've tried to write an update statement that will update some information in my SQL Server table

Here is my code so far, I can't see the issue.

protected void Page_Load(object sender, EventArgs e) { FirstNameEdit.Text = Session["FirstName"].ToString(); LastNameEdit.Text = Session["LastName"].ToString(); } protected void SubmitEdit_Click(object sender, EventArgs e) { if (FirstNameEdit.Text == "") { StatusMessage.Text = "Indtast venligst dit fornavn. "; } else { if (LastNameEdit.Text == "") { StatusMessage.Text = "Indtast venligst dit efternavn. "; } else { try { SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Break;Integrated Security=True"); SqlCommand command = new SqlCommand("SELECT * FROM Users", connection); command.Connection.Open(); string querystr = "UPDATE Users SET User_FirstName='@User_FirstName', User_LastName='@User_LastName' WHERE User_ID='@User_ID'"; SqlCommand query = new SqlCommand(querystr, connection); string User_ID = Session["ID"].ToString(); string User_FirstName = FirstNameEdit.Text; string User_LastName = LastNameEdit.Text; query.Parameters.Add("@User_ID", User_ID); query.Parameters.Add("@User_FirstName", User_FirstName); query.Parameters.Add("@User_LastName", User_LastName); query.ExecuteNonQuery(); string FirstName = FirstNameEdit.Text; Session.Add("FirstName", FirstName); string LastName = LastNameEdit.Text; Session.Add("LastName", LastName); StatusMessage.Text = "Din profil er opdateret"; command.Connection.Close(); } catch { StatusMessage.Text = "Noget er galt, prøv lidt senere"; } } } }

I have also searched a lot, but it's exactly the same I find. Maybe it's something about the SQL query.

Improve this question
3
  • What errors do you get? Also you shouldn't need the ' wrapping your parameters it should just be User_FirstName=@User_FirstName Commented Nov 1, 2013 at 8:56
  • 1
    So, What do you want from us ????????? Commented Nov 1, 2013 at 9:00
  • Haha, as I said, I think there is something wrong with the sql query, but it's fixed now! Well, there is one more thing, it dosen't update. Commented Nov 1, 2013 at 9:18

4 Answers 4

Reset to default
4

What is the issue exactly?

I think when specifying the parameter using parameterized query, you don't need to use quote anymore, hence

string querystr = "UPDATE Users SET User_FirstName='@User_FirstName', User_LastName='@User_LastName' WHERE User_ID='@User_ID'";

should become

string querystr = "UPDATE Users SET User_FirstName=@User_FirstName, User_LastName=@User_LastName WHERE User_ID=@User_ID";
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

What is the User_ID column data type? Perhaps as @Steve said, you may need to convert to int if it is a numeric field.
If that's so, can you try replacing string User_ID = Session["ID"].ToString(); to int User_ID = Convert.ToInt32(Session["ID"].ToString()); ?
4

Don't enclose the parameters placeholders with single quotes. In that way they becomes simply string literals and your query can't work

 string querystr = "UPDATE Users SET User_FirstName=@User_FirstName, " + "User_LastName=@User_LastName WHERE User_ID=@User_ID";

Another thing to consider. Are you sure that your User_ID field is a string (text, nvarchar, varchar) field in the database?. If it is a numeric field then you need to convert the value passed as parameter for that field to a numeric value (I.E. Use Convert.ToInt32)

So, if this is true, the code could be changed as

string User_ID = Session["ID"].ToString(); string User_FirstName = FirstNameEdit.Text; string User_LastName = LastNameEdit.Text; query.Parameters.AddWithValue("@User_ID", Convert.ToInt32(User_ID)); query.Parameters.AddWithValue("@User_FirstName", User_FirstName); query.Parameters.AddWithValue("@User_LastName", User_LastName); query.ExecuteNonQuery();

I have used the AddWithValue following the recommendation in MSDN that says

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object. The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value. Use AddWithValue whenever you want to add a parameter by specifying its name and value.

However, the advantage in the implicit conversion it's also the disadvantage since the conversion may not be optimal. I can give as reference this very thorough article

Another note to keep in mind. Do not swallow exceptions. In your code, the generic message that advise the user of a failure is not enough to let you understand what's going wrong. You should replace your code with

try { ...... } catch(Exception ex) { // I let you translate... :-) StatusMessage.Text = "Noget er galt, prøv lidt senere " + "Error message=" + ex.Message; }
Improve this answer

6 Comments

my table structure is User_ID User_Username User_Password User_FirstName User_LastName
User_ID has INT as datatype
Then it is better to use the appropriate conversion. I will show an example in my answer above
Cool, I've just started coding c# so it's good to know, thank you. But unfortunately it didn't work :(
Did you get any error message? Did the Session["ID"] really contains a number?
|
0

First of all, as other people said - don't use single quota in the query, you don't need that.

Secondly: when you are defining Parameters to your command, you may use two variants:

1) query.Parameters.AddWithValue("@User_FirstName", User_FirstName);

or

2) query.Parameters.Add("@User_FirstName", SqlDbType.VarChar).Value = User_FirstName;

You can't use query.Parameters.Add("@User_FirstName", User_FirstName);

Improve this answer

Comments

-1 
string queryStr = "UPDATE SET USER (account, user, password, permission_level)" + "VALUES ('" + tbxAccount.Text + "', '" + tbxUsername.Text + "', '" + tbxPassword.Text + "', '" + tbxPermission.Text + "');";

 

Improve this answer

Comments