CODEWITHSUNDEEP
c#sqlasp.netsql-server
RoobNoob
RoobNoob

Reputation: 33

Asp.net c# SQL Server Update query not working for all columns

I have a form that should edit the information in the database. First, I query the database using an URL parameter to get the record and display them in textboxes so I could update them. That part of the code works the issue is when I press submit to update the data after changing what's on the textboxes. Only LastMod column gets updated. I think the page_load fires twice and overwrites what's on the textboxes before the button click event fires.

Here's the code.

public partial class Edit : System.Web.UI.Page
{
    int aID;
    SqlConnection conn = new SqlConnection(@connectionstring);

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        { 
            conn.Open();
            aID = Convert.ToInt16(Request.QueryString["ID"]);
            string sql = "select * from Contacts where ID = '" + aID + "' ";

            SqlDataAdapter adap = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();

            adap.Fill(ds);

            txtFName.Text = ds.Tables[0].Rows[0]["Fname"].ToString();
            txtLName.Text = ds.Tables[0].Rows[0]["Lname"].ToString();
            txtEmail.Text = ds.Tables[0].Rows[0]["EmailAdd"].ToString();
            lblLastMod.Text = ds.Tables[0].Rows[0]["LastMod"].ToString();
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
            DateTime date = DateTime.Now;
            SqlCommand sqlComm = new SqlCommand();
            sqlComm = conn.CreateCommand();

            sqlComm.CommandText = @"UPDATE Contacts SET Fname=@FName, Lname = @LName, EmailAdd = @Eadd,LastMod = @LMod WHERE ID=@ID";
            sqlComm.Parameters.Add("@FName", SqlDbType.NChar);
            sqlComm.Parameters["@FName"].Value = txtFName.Text.Trim();
            sqlComm.Parameters.Add("@LName", SqlDbType.NChar);
            sqlComm.Parameters["@LName"].Value = txtLName.Text.Trim();
            sqlComm.Parameters.Add("@Eadd", SqlDbType.NChar);
            sqlComm.Parameters["@Eadd"].Value = txtEmail.Text.Trim();
            sqlComm.Parameters.Add("@LMod", SqlDbType.DateTime);
            sqlComm.Parameters["@LMod"].Value = date;
            sqlComm.Parameters.Add("@ID", SqlDbType.Int);
            sqlComm.Parameters["@ID"].Value = aID;

            conn.Open();
            sqlComm.ExecuteNonQuery();
            conn.Close();
    }
}

I tried adding !IsPostback in my Page_Load method but nothing gets updated even the LastMod record.

Thanks for any help I get.

Upvotes: 2

Views: 399

Answers (1)

Nikki9696
Nikki9696

Reputation: 6348

You declare a variable, aID, at the top of the page, and you set a value in it in the page load. You then also use it in the click, but you never re-set it. Therefore, for postback, it will not have a value. Move this to page load above your "if not ispostback"

protected void Page_Load(object sender, EventArgs e)
    {
       aID = Convert.ToInt16(Request.QueryString["ID"]);
        if (!IsPostBack)
        { 
            conn.Open();

            string sql = "select * from Contacts where ID = '" + aID + "' ";

Upvotes: 2

Related Questions