Why does this UPDATE command not update the data, even though there is no error?

Question

I have a users table having fields uname, ushortname and pswd as well ucode as primary key. The following UPDATE is not working:

protected void Page_Load(object sender, EventArgs e)
    {

        string uname = Request.QueryString["uname"];
        string ucode = Request.QueryString["ucode"];
        if (!string.IsNullOrEmpty(uname))
        {
            SqlCommand cmd = new SqlCommand("select * from users WHERE ucode=@ucode", conn);
            SqlDataAdapter dadapter = new SqlDataAdapter();
            conn.Open();
            txtUserName.ReadOnly = false;
            txtUserShortName.ReadOnly = false;
            txtPassword.ReadOnly = false;
            dadapter.SelectCommand = cmd;
            cmd.Parameters.Add(new SqlParameter("@ucode", ucode));
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                LblUcode.Text = dr["ucode"].ToString();
                txtPassword.Text=dr["pswd"].ToString();
                txtUserShortName.Text = dr["ushortname"].ToString();
                txtUserName.Text = dr["uname"].ToString();
                            }
            dr.Close();
            conn.Close();
        }
    }    
SqlCommand cmd = new SqlCommand("update users Set uname=@Uname,ushortname=@Ushortname,pswd=HASHBYTES('MD5',@Pswd) where ucode=@UCODE", conn);
    cmd.Parameters.AddWithValue("@Uname", txtUserName.Text);
    cmd.Parameters.AddWithValue("@Ushortname", txtUserShortName.Text);
    cmd.Parameters.AddWithValue("@Pswd", txtPassword.Text);
    cmd.Parameters.AddWithValue("@UCODE", LblUcode.Text);
    cmd.ExecuteNonQuery();
    cmd.Dispose();

There is no error, the data simply isn't being updated.

`ExecuteNonQuery` code return by statement if will return 1 if it updates.. Try to Execute statement in ssms — Dgan, Nov 19 '14 at 06:10
if no error and no updation then there is something wrong in your where clause.Check that is it satisfying the where clause condition or not? — yogi970, Nov 19 '14 at 06:22
I have checked using breakpoints and the value getting in where clause is correct. — prachi, Nov 19 '14 at 06:32
debug it and add breakpoint to query and copy the final query and run it directly in your database and see is it working or not? — yogi970, Nov 19 '14 at 06:34
@prachi `update users Set uname='test user',ushortname='tinku',pswd=HASHBYTES('MD5','testpwd') where ucode='12'` you ran this type of query in db and it worked? — yogi970, Nov 19 '14 at 06:39
Password security recommendation #1: Instead of performing the MD5 hashing of the password at the DB server, do it at the client-side (that is, in your C# code). Because right now, you are sending the password to the DB across the network (possibly over an unencrypted connection). — stakx - no longer contributing, Nov 19 '14 at 06:46
Password security recommendation #2: Instead of just hashing the password, [salt](http://en.wikipedia.org/wiki/Salt_(cryptography) "see e.g. Wikipedia article") it as well. — stakx - no longer contributing, Nov 19 '14 at 06:49
That I will do @stakx later, but now the problem is their is no updation in data. — prachi, Nov 19 '14 at 06:52
**1.** Show us *all* relevant code, including how & where you create `conn` (remove sensitive data from the connection string, though) and how & where you open it before you execute the query. **2.** Speaking of the connection string: Are you connecting to a full SQL Server instance, or to a (SQL Server Compact) database file? **3.** *How* do you check whether the data has been updated or not? — stakx - no longer contributing, Nov 19 '14 at 06:58
@prachi: That cannot possibly be your actual code, because it is syntactically invalid (and would therefore not compile nor run): You cannot have statements (the last 7 lines of code) outside of a method. -1 for repeatedly wasting everyone's time by posting misleading and/or incomplete information. — stakx - no longer contributing, Nov 19 '14 at 08:04

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

Try this:-

using(SqlConnection conn = new SqlConnection(CS))
{
   using (SqlCommand cmd = new SqlCommand("update users Set uname=@Uname,ushortname=@Ushortname,pswd=HASHBYTES('MD5',@Pswd) where ucode=@UCODE ", conn))
  {
    cmd.Parameters.AddWithValue("@Uname", txtUserName.Text);
    cmd.Parameters.AddWithValue("@Ushortname", txtUserShortName.Text);
    cmd.Parameters.AddWithValue("@Pswd", txtPassword.Text);
    cmd.Parameters.AddWithValue("@UCODE", UCODE);
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}

You are missing @ in Ucode. Also, its a good practice to wrap your code inside using block check this.

edited May 23 '17 at 10:24

Community

1
1

answered Nov 19 '14 at 06:08

Rahul Singh

21,585
6
41
56

I have that statement in my code but missed to place here – prachi Nov 19 '14 at 06:10
1

@prachi - Have you debugged your code? If it is running properly and still data is not getting updated, Please run `SQL Profiler` and check what query is actually getting executed in database. – Rahul Singh Nov 19 '14 at 06:30
I have debugged my code and the query is properly executing in database. – prachi Nov 19 '14 at 06:40
@prachi - Have you checked your profiler? What query it is issuing to database? – Rahul Singh Nov 19 '14 at 06:43
while debugging the textboxes shows the value which was already their and not the updated one. – prachi Nov 19 '14 at 06:57
On page load I have assigned values to textbox,after editing that value on form the new value does not retained and I am getting the old values which I have assigned on page load . – prachi Nov 19 '14 at 07:01
@prachi - Okay I agree but what I am saying is, if your code is passing correct values but then too your DB is not getting updated. You need to run SQL profiler while updating the data through APP and check what query is issued. If you don't know how to use profiler check this:- http://msdn.microsoft.com/en-in/library/ff650699.aspx – Rahul Singh Nov 19 '14 at 07:05
@prachi - ohk..Show how you are binding the textboxes in that case. – Rahul Singh Nov 19 '14 at 07:07
I have updated my code please go through it,actually when I click edit button it goes to another page where through dropdown I select the uname and passed it back through querystring. – prachi Nov 19 '14 at 07:17

score 0 · Answer 2 · edited Nov 19 '14 at 06:51

0

See the edited below code:-

if (!PostBack)
{
   string databaseconnectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString;
   string sql = "update users Set uname=@Uname,ushortname=@Ushortname,pswd=HASHBYTES('MD5',@Pswd) where ucode=@UCODE";    
   using (SqlConnection conn = new SqlConnection(databaseconnectionstring))
   {
     conn.Open();
     using (SqlCommand cmd= new SqlCommand())
     {
        cmd.CommandText=sql;
        cmd.Connection=databaseconnectionstring;
        cmd.Parameters.AddWithValue("@Uname",txtUserName.Text );
        cmd.Parameters.AddWithValue("@Ushortname",txtUserShortName.Text );
        cmd.Parameters.AddWithValue("@Pswd",txtPassword.Text );
        cmd.Parameters.AddWithValue("@UCODE", UCODE);
        cmd.ExecuteNonQuery();
        conn.Close();
        cmd.Dispose();
     }
  }
}

edited Nov 19 '14 at 06:51

Rajesh

1,600
5
33
59

answered Nov 19 '14 at 06:11

Nad

4,605
11
71
160

I have declared connection string in my class itself. – prachi Nov 19 '14 at 06:13
@prachi: see the updated code, let me know if it works or not – Nad Nov 19 '14 at 06:23
I have seen the code and updated as suggested but still not working @Nadeem – prachi Nov 19 '14 at 06:30
@NadeemKhan what made you to add `if(!ISpostBack)` in your answer ? :P – yogi970 Nov 19 '14 at 06:32
I have Used if (!IsPostBack) condition,still not working @Nadeem – prachi Nov 19 '14 at 06:35
@prachi `if (!IsPostBack) ` is not the solution of this. check your query in db directly and see changes – yogi970 Nov 19 '14 at 06:37
@yogi970:You need to check if(!IsPostBack). Or you will always override your data. – Nad Nov 19 '14 at 06:39
@prachi: See the modified code, if this doesn't work, you have to check your Database properly. – Nad Nov 19 '14 at 06:41
@NadeemKhan it is update query not the insert query and if it updates for first time or multiple time the result will be same ,yes it will override the data with **same data** and she no where mentioned that she is using this code in page load. so not much sense to add post back – yogi970 Nov 19 '14 at 06:44
@yogi970: Agreed with you.! – Nad Nov 19 '14 at 06:45

score 0 · Answer 3 · answered Nov 19 '14 at 06:17

0

You need to make sure the parameter names match exactly what they do in your sql string.

cmd.Parameters.AddWithValue("@UCODE", UCODE);

Also, either wrap all of that in a using or try/finally to dispose of the connection. I'm assuming you've already got code to open the connection before trying to execute it.

answered Nov 19 '14 at 06:17

Barry Colebank Jr

1,939
2
16
16

yes i have tried using the above statement also i.e. taking @UCODE scalar variable in cmd.Parameters.AddWithValue("@UCODE", UCODE); – prachi Nov 19 '14 at 06:20
What's the exact error? Or is it not doing anything in the update? – Barry Colebank Jr Nov 19 '14 at 06:43
It is not doing anything in update.But their is no error – prachi Nov 19 '14 at 06:44
I can tell you that it WILL need the @UCODE in order to work eventually. Are you sure there is a row in the table that would cause an update to occur? – Barry Colebank Jr Nov 19 '14 at 06:46
Yes there is a row in table with correct UCODE value – prachi Nov 19 '14 at 06:53
Are you confirming this in the database or through your web app? – Barry Colebank Jr Nov 19 '14 at 07:04

prachi · Answer 4 · 2014-11-19T08:24:28.777

protected void Page_Load(object sender, EventArgs e)
        {        
            string bsname = Request.QueryString["bsname"];
            string bscode = Request.QueryString["bscode"];
            if (!string.IsNullOrEmpty(bsname))
            {

               if (string.IsNullOrEmpty(txtBsName.Text))
               {
                 SqlCommand cmd = new SqlCommand("select * from bs WHERE bscode=@bscode", conn);
                 SqlDataAdapter dadapter = new SqlDataAdapter();
                 conn.Open();
                 txtBsName.ReadOnly = false;
                 dadapter.SelectCommand = cmd;
                 cmd.Parameters.Add(new SqlParameter("@bscode", bscode));
                 SqlDataReader dr = cmd.ExecuteReader();
                 while (dr.Read())
                 {
                    lblBsCode.Text = dr["bscode"].ToString();
                    txtBsName.Text = dr["bsname"].ToString();

                 }
                 dr.Close();
                 conn.Close();
              }
           }
       }
 protected void btnSave_Click(object sender, EventArgs e)
    {
           conn.Open();
           string UCODE = LblUcode.Text;
           SqlCommand cmd = new SqlCommand("update users Set uname=@Uname,ushortname=@Ushortname,pswd=HASHBYTES('MD5',@Pswd) where ucode=@UCODE", conn);

           cmd.Parameters.AddWithValue("@Uname",txtUserName.Text );
           cmd.Parameters.AddWithValue("@Ushortname",txtUserShortName.Text );
           cmd.Parameters.AddWithValue("@Pswd",txtPassword.Text );
           cmd.Parameters.AddWithValue("@UCODE", UCODE);
           cmd.ExecuteNonQuery();
           cmd.Dispose();

           ShowMessage("Company Data update Successfully......!");

           clear();
}

Thank you all its working now with the help of above code.

Is your Update inside Page_Load or separate function – Rajesh Nov 19 '14 at 07:55 — Rajesh, Nov 19 '14 at 07:55
separate function actually on save button click event – prachi Nov 19 '14 at 08:22 — prachi, Nov 19 '14 at 08:22

Why does this UPDATE command not update the data, even though there is no error?

4 Answers4