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();
            }
        }
    }
}

}

FYI - your code is susceptible to sql injection.

Daniel A. White
– Daniel A. White

2011-04-19 14:12:08 +00:00
Commented Apr 19, 2011 at 14:12 — Daniel A. White
– Daniel A. White, Commented Apr 19, 2011 at 14:12

KTF · Accepted Answer · 2011-04-19 14:17:58Z

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!

Thank you, KTF, that is great! I'm reading the article right now. For clarification, I should perform the if sequence on my original query (which is only to display the information), or should it be on the query to update? I would assume the latter, but there is a reason I'm the one asking questions...
Your question implies a misunderstanding of the ASP.NET model. Just about any event on the page will be a "Post Back" since it will be triggered by the page, so to speak. As such, you would want the Button Click event to update always. (It will never be called if the page is not a post back anyway). This article may help you as well: msdn.microsoft.com/en-us/library/59t350k3(v=vs.71).aspx
Touche. Thank you for going the extra mile to teach me how to fish. I'm basically crash-coursing this work, and it equates to trying to do medical school from home in a year instead of 10. No shortcuts to proficiency in this profession.

secretAgentB · Accepted Answer · 2011-04-19 14:04:43Z

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;
    }
}

Thank you, Ryan. Your and KTF's answers solved my issue. Many thanks.

John · Accepted Answer · 2011-04-19 15:52:31Z

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

Collectives™ on Stack Overflow

update not executing with query string?

3 Answers 3

3 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Related