GRidviews, binding, searching and editing

[wbodger]

I have a page with a gridview. I can edit said gridview and everything works fine. I can search said gridview and everything works fine until... If I try to edit the record that I found with my search, then it doesn't actually edit the record that I found because it re-binds to the non-searched gridview method. I know I am not the first person that has wanted to do this, so there has to be a better way than what I have come up with. Since I come from a classic asp background and I am more of a database guy than a .Net guy (but I am trying to learn), all that I could come up with was perhaps editing my showgrid() method so that it accepts a parameter and then just send it null unless a search was performed (and then after any action is performed after the search I set the session variable to "").

So, here is what I am doing

        protected void Page_Load(object sender, System.EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["sortOrder"] = "";
                Session["StockCode"] = "";

                showgrid();
            }
           
        }

        protected void OnClickSearch(object sender, EventArgs e)
        {
            string _stk= txtSearch.Text.Trim();
            Session["StockCode"] = _stk;
            showgrid();
        }

        public void showgrid()
        {
           DataTable dt = new DataTable();
           string connStr = ConfigurationManager.ConnectionStrings["xxxx"].ConnectionString;
           SqlConnection sqlcon = new SqlConnection(connStr);
           sqlcon.Open();
           SqlDataAdapter sda = new SqlDataAdapter();
           string strQuery = "SELECT StockCode, Description, UN, UOM, VendorDescription, MOQ, Comment, DateofChange, CurrentPrice, QuotePrice, CreateDate, EffectiveDate FROM AIP.dbo.tbl_PattonAirPricing where RTRIM(StockCode) like UPPER('" + Session["StockCode"] + "%') ";

            SqlCommand cmd = new SqlCommand(strQuery);
           cmd.CommandType = CommandType.Text;
           cmd.Connection = sqlcon;
           sda.SelectCommand = cmd;
           sda.Fill(dt);
           GridView1.DataSource = dt;
           GridView1.DataBind();
        }

and then

        protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            GridView1.EditIndex = -1;
            Session["StockCode"] = "";
            showgrid();   
        } 
        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            GridView1.EditIndex = e.NewEditIndex;
            showgrid();
        }

        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            HiddenField lbl = (HiddenField)GridView1.Rows[e.RowIndex].FindControl("TranID");
            TextBox tx1 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("Comment");
            TextBox tx2 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("CurrentPrice");
            TextBox tx3 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("QuotePrice");
            TextBox tx4 = (TextBox)GridView1.Rows[e.RowIndex].FindControl("EffectiveDate");

            string connStrII = ConfigurationManager.ConnectionStrings["xxxx"].ConnectionString;
            SqlConnection sqlconII = new SqlConnection(connStrII);
            sqlconII.Open();
            string sqlII = "UPDATE AIP.dbo.tbl_PattonAirPricing SET Comment='" + tx1.Text.Replace("'", "''") + "', CurrentPrice='" + tx2.Text.Replace("'", "''") + "', QuotePrice='" + tx3.Text.Replace("'", "''") + "', EffectiveDate='" + tx4.Text.Replace("'", "''") + "' WHERE StockCode=rtrim('" + lbl.Value + "')";
            SqlCommand cmd = new SqlCommand(sqlII);
            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlconII;
            cmd.ExecuteNonQuery();
            GridView1.EditIndex = -1;
            Session["StockCode"] = "";
            showgrid();
        }

which all works, I just don't like to depend on session variables, though perhaps that is misguided on my part? Is there a better solution?

Thanks!
Willie

 

[jbenson001]

You will have to trace through your code to see what is happening. The problem is probably when calling the DB with your SQL. You need to make sure it is executing the same SQL when you edit, as when you performed the search.

 

[wbodger]

This code works, I was just wondering if anybody had a better way to accomplish this same thing.

wb

 

[jbenson001]

I am confused, you said that when you searched and got rows back, and then tried to edit, that it was editing the wrong row. Is that the case?
I looked at your code you have and it is structured the same way I do my code, so I didn't see what the problem would be.

 

[wbodger]

My apologies, I think I jumped sides mid-stream in my initial post. I came up with a way to accomplish what I had wanted to do, but was wondering if there was a better way to accomplish it. So, no worries, it sounds like this is a viable solution.

Thanks!
Willie

 

[jbenson001]

OK, good, glad there is not a problem.
As far as I can see, this is the proper way to do what you want to do.