CODEWITHSUNDEEP
c#sqlsql-server
user2786856
user2786856

Reputation:

C# update SQL Server table

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.

Upvotes: 3

Views: 59608

Answers (4)

Aun JeeRut
Aun JeeRut

Reputation: 1

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

 

Upvotes: -1

Khazratbek
Khazratbek

Reputation: 1656

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);

Upvotes: 0

Steve
Steve

Reputation: 216243

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;
}

Upvotes: 4

rcs
rcs

Reputation: 7187

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";

Upvotes: 4

Related Questions