Update sql table with parameters not updating

Question

I have this method in an asp page, there are no errors when executed but even no updates. What am I doing wrong in this method that does not update the table?

protected void saveSettings()
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sendyourmessageConnectionString"].ConnectionString);
    SqlCommand cmd = new SqlCommand();
    string userName = "something";

        try
        {
            cmd = new SqlCommand("UPDATE Tb_Registration SET Country = @Country, City = @City Where Username = @Username" , con);

            cmd.Parameters.AddWithValue("@Username", SqlDbType.VarChar);
            cmd.Parameters["@Username"].Value=userName;

            cmd.Parameters.AddWithValue("@Country", SqlDbType.VarChar);
            cmd.Parameters["@Country"].Value= txtCountry.Text.Trim();

            cmd.Parameters.AddWithValue("@City", SqlDbType.VarChar);
            cmd.Parameters["@City"].Value= txtCity.Text.Trim();

            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
            return;
        }
        finally
        {
            con.Close();
            cmd.Dispose();
            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Your settings have been saved');", true);
        }
}

Do you have user called something in your database?

danish
– danish

2014-12-24 08:27:08 +00:00
Commented Dec 24, 2014 at 8:27 — danish
– danish, Commented Dec 24, 2014 at 8:27
@danish, yes, I am testing with it.

FeliceM
– FeliceM

2014-12-24 08:27:58 +00:00
Commented Dec 24, 2014 at 8:27 — FeliceM
– FeliceM, Commented Dec 24, 2014 at 8:27

Soner Gönül · Accepted Answer · 2014-12-24 08:51:58Z

2

I think the problem is AddWithValue method.

It takes the parameter value as a second parameter, not SqlDbType.

But don't use this method anymore. It might generate unexpected results. Use .Add() method and it's overloads.

Read: Can we stop using AddWithValue() already?

Also use using statement to dispose your SqlConnection and SqlCommand.

using(SqlConnection con = new SqlConnection(connString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = @"UPDATE Tb_Registration SET Country = @Country, City = @City
                       Where Username = @Username";
   cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = userName;
   cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();
   cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
   con.Open();
   cmd.ExecuteNonQuery();
}

edited Dec 24, 2014 at 8:51

answered Dec 24, 2014 at 8:26

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrey Korneyev · Accepted Answer · 2014-12-24 08:27:44Z

1

Your problem here: cmd.Parameters.AddWithValue("@Country", SqlDbType.VarChar);

It should be value of parameter, not type if you're using AddWithValue

But better practice - not to use AddWithValue at all, as it can lead to different problems.

Instead use something like:

cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = userName;

answered Dec 24, 2014 at 8:27

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Comments

Brave Soul · Accepted Answer · 2014-12-24 08:31:05Z

Insert direct values rather that providing datatype

 protected void saveSettings()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sendyourmessageConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        string userName = "something";

            try
            {
                cmd = new SqlCommand("UPDATE Tb_Registration SET Country = @Country, City = @City Where Username = @Username" , con);

                cmd.Parameters.AddWithValue("@Username", userName);


                cmd.Parameters.AddWithValue("@Country",  txtCountry.Text.Trim());


                cmd.Parameters.AddWithValue("@City", txtCity.Text.Trim());


                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
                return;
            }
            finally
            {
                con.Close();
                cmd.Dispose();
                ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Your settings have been saved');", true);
            }
    }

Tien Dinh · Accepted Answer · 2014-12-24 08:30:27Z

0

The second parameter to AddWithValue is your value not the sql type

 cmd.Parameters.AddWithValue("@Username", userName);

answered Dec 24, 2014 at 8:30

Tien Dinh

1,0387 silver badges12 bronze badges

Collectives™ on Stack Overflow

Update sql table with parameters not updating

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related