CODEWITHSUNDEEP
c#asp.netsql-server
user2517610
user2517610

Reputation: 275

Trying to update table in sql server but not working

I am trying to update table in sql server but it's not working. Here is my code

SqlConnection conn;
    string connString = ConfigurationManager.ConnectionStrings["Alumnidb"].ConnectionString;
    string userName;
    SqlCommand cmdProfile, cmdUpdate;
    SqlDataReader reader;
    string UserId;
    protected void Page_Load(object sender, EventArgs e)
    {
        userName = Request.QueryString["UserName"].ToString();

        RetriveProfile();
    }

    protected void RetriveProfile()
    {
        conn = new SqlConnection(connString);
        cmdProfile = new SqlCommand("SELECT Name, UserId FROM UserProfile WHERE UserName=@UserName",conn);
        cmdProfile.Parameters.AddWithValue("@UserName",userName);
        conn.Open();
        reader = cmdProfile.ExecuteReader();
        while (reader.Read())
        {
            TextBoxName.Text = reader["Name"].ToString();
            UserId = reader["UserId"].ToString();
        }
        conn.Close();
    }
    protected void buttonUpdate_Click(object sender, EventArgs e)
    {
        conn = new SqlConnection(connString);
        cmdUpdate = new SqlCommand("UPDATE UserProfile SET Name=@Name WHERE UserId=@UserId",conn);
        cmdUpdate.Parameters.AddWithValue("@UserId",UserId);
        cmdUpdate.Parameters.AddWithValue("@Name",TextBoxName.Text.ToString());
        conn.Open();
        cmdUpdate.ExecuteScalar();

        conn.Close();

    }

and .aspx file

Name: <asp:TextBox ID="TextBoxName" runat="server" ></asp:TextBox>

<asp:Button ID="buttonUpdate" runat="server"  Text="UpDate" 
        onclick="buttonUpdate_Click"/>

It shows me the previous value as before updated. . i checked in sql server and there is also no change in there What am i doing wrong? Your help will be appreciated. . .Thanx

Upvotes: 0

Views: 1154

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460018

The problem is that you fill all values in Page_Load even if you are in a postback. So if the user clicks on the update-button Page_Load is fired first, all values are loaded from database and you set the TextBox.Text value to the old value. So all changes are lost.

So use the IsPostBack property:

protected void Page_Load(object sender, EventArgs e)
{
    userName = Request.QueryString["UserName"].ToString();
    if(!IsPostBack)
        RetriveProfile();
}

Since you are getting the UserID from the sql query and you need that in the update you have several options. You could persist the userid across postbacks e.g. in ViewState or Session. This can be done in RetriveProfile.

protected void RetriveProfile()
{
    conn = new SqlConnection(connString);
    cmdProfile = new SqlCommand("SELECT Name, UserId FROM UserProfile WHERE UserName=@UserName",conn);
    cmdProfile.Parameters.AddWithValue("@UserName",userName);
    conn.Open();
    reader = cmdProfile.ExecuteReader();
    while (reader.Read())
    {
        TextBoxName.Text = reader["Name"].ToString();
        UserId = reader["UserId"].ToString();
    }
    conn.Close();
}

Change the field UserID to be a property:

private string UserId {
    get { return (string)ViewState["UserID"]; }
    set { ViewState["UserID"] = value;}
}

Note that all variables are disposed at the end of every page's lifecycle due to the statelessness of HTTP. Therefore you need to persist it somewhere.

Nine options for managing persistent User State in your ASP.NET Application

Upvotes: 1

Related Questions