update not executing with query string?

Question

I'm using an edit page for my user to view and change their data in textboxes, which they access from the gridview on the homepage. I use a datakey on an autoincremented column, ProductId, and the row data displays perfectly. Unfortunately, when I fire the click button event to update the row with the changes made in these textboxes, they don't register. I've included the code below, but as a note, this is a training project and I was expressly forbidden to paramaterize in the interest of learning the basics first. I realize this is a security imperative, but for now, no paramaters. To clarify and restate my question, when I click the submit button, the row data is not affected by changes entered into the textboxes ,but instead reverts to the original values. I know it's probably something to do with the query string, but I've no idea what. Ideas?

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class ViewEdit : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string x = Request.QueryString["ProductId"];
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        string editQuery = "SELECT CustId, CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroId, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments, ProductId FROM ProductInstance WHERE ProductId =" + x;



    using (SqlConnection editConn = new SqlConnection(connectionString))
    {
        editConn.Open();

        using (SqlCommand command = new SqlCommand(editQuery, editConn))
        {

            SqlDataReader dr = command.ExecuteReader();
            dr.Read();
            TextBox1.Text = dr.GetInt32(0).ToString();
            TextBox2.Text = dr.GetString(1);
            TextBox3.Text = dr.GetString(2);
            TextBox4.Text = dr.GetString(3);
            TextBox5.Text = dr.GetString(4);
            TextBox6.Text = dr.GetString(5);
            TextBox7.Text = dr.GetInt32(6).ToString();
            TextBox8.Text = dr.GetString(7);
            TextBox9.Text = dr.GetInt32(8).ToString();
            TextBox10.Text = dr.GetString(9);
            TextBox11.Text = dr.GetString(10);
            TextBox12.Text = dr.GetString(11);
            TextBox13.Text = dr.GetInt32(12).ToString();
            TextBox14.Text = dr.GetString(13);
            TextBox15.Text = dr.GetInt32(14).ToString();
            TextBox16.Text = dr.GetInt32(15).ToString();
            TextBox17.Text = dr.GetInt32(16).ToString();
            TextBox18.Text = dr.GetInt32(17).ToString();
            TextBox19.Text = dr.GetDateTime(18).ToString();
            TextBox20.Text = dr.GetInt32(19).ToString();
            TextBox21.Text = dr.GetInt32(20).ToString();
            TextBox22.Text = dr.GetString(21);



        }
        editConn.Close();
    }   
}

protected void Button1_Click(object sender, EventArgs e)
{
    string x = Request.QueryString["ProductId"];
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    using (SqlConnection updateConn = new SqlConnection(connectionString))
    {
        updateConn.Open();
        {
            string updateQuery = "UPDATE ProductInstance SET CustId = '" + TextBox1.Text + "', CustName = '" + TextBox2.Text + "', SicNaic = '" + TextBox3.Text + "', CustCity =  '" + TextBox4.Text + "', CustAdd = '" + TextBox5.Text + "', CustState =  '" + TextBox6.Text + "', CustZip =  '" + TextBox7.Text + "', BroName = '" + TextBox8.Text + "', BroId =  '" + TextBox9.Text + "', BroAdd =  '" + TextBox10.Text + "', BroCity = '" + TextBox11.Text + "', BroState =  '" + TextBox12.Text + "', BroZip =  '" + TextBox13.Text + "', EntityType =  '" + TextBox14.Text + "', Coverage =  '" + TextBox15.Text + "', CurrentCoverage =  '" + TextBox16.Text + "', PrimEx = '" + TextBox17.Text + "', Retention = '" + TextBox18.Text + "', EffectiveDate =  '" + TextBox19.Text + "', Commission = '" + TextBox20.Text + "', Premium =  '" + TextBox21.Text + "', Comments = '" + TextBox22.Text + "' WHERE ProductId =" + x;



            using (SqlCommand command = new SqlCommand(updateQuery, updateConn))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

}

Answer 1

The first thing you should always do on a page like this is check for !IsPostBack and then carry out the standard process for rendering the page inside the code:

protected void Page_Load(object sender, EventArgs e)
{         
    if (!IsPostBack)
    {
        // Add your normal code in here
    }
}

And then in your click event, rebind the data when you finish your insertion to the database

protected void Button1_Click(object sender, EventArgs e)
{
    // Do Insertion here
    lstView.DataSource = sqlVals;
    lstView.DataBind();
}

Also get reading up on EF4 or LinqToSql as it will make data calls from SQL easier and more secure

Answer 2

Brazos,

This occurs because when a change is made and posted back to the page, Page_Load is executing again before you are able to save the values collected from the form back to the database. Instead, the text boxes are loaded with the values from the database and any changes are overridden. Later, when the Button1_Click event occurs and you do save the data to the database, you update the row with the values from the textboxes which now reflect what was in the database in the first place and not what was submitted in the form.

Check out the order of events in the ASP.NET page life cycle here: http://msdn.microsoft.com/en-us/library/ms178472.aspx

An easy way to fix this would be to only run the query that loads and updates the textboxes (in the Page_Load) if it is not in postback:

public partial class ViewEdit : System.Web.UI.Page {
    protected void Page_Load(object sender, EventArgs e)
    {

        if (!Page.IsPostBack)
        {
            string x = Request.QueryString["ProductId"];
            string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            string editQuery = "SELECT CustId, CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroId, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments, ProductId FROM ProductInstance WHERE ProductId =" + x;



        using (SqlConnection editConn = new SqlConnection(connectionString))
        {
            editConn.Open();

            using (SqlCommand command = new SqlCommand(editQuery, editConn))
            { [...]

However, this also means that after the changes get done you will no longer be reloading the changes from the database since every page event after that will be a postback (unless you do a redirect). Since you are learning ASP.NET, I recommend you check out the page life cycle and explore a different solution. Good luck!

Answer 3

In your Page_Load check for postback

protected void Page_Load(object sender, EventArgs e)     
{         
    if (Page.IsPostBack == false)
    {
        string x = Request.QueryString["ProductId"];         
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;         string editQuery = "SELECT CustId, CustName, SicNaic, CustCity, CustAdd, CustState, CustZip, BroName, BroId, BroAdd, BroCity, BroState, BroZip, EntityType, Coverage, CurrentCoverage, PrimEx, Retention, EffectiveDate, Commission, Premium, Comments, ProductId FROM ProductInstance WHERE ProductId =" + x;
    }
}