How to Update a GridView when boundfield is used

Question

I have a GridView and it is bound to a database. I am finding difficulties in updating the GridView and corresponding table in the database.

My asp code for GridView after binding to SQLdatasource is :

<asp:GridView ID="GridView2" runat="server" OnRowEditing="GridView2_RowEditing"
    OnRowUpdating="GridView2_RowUpdating" CellPadding="4" ForeColor="#333333" OnRowCancelingEdit="GridView2_RowCancelingEdit"
    OnRowDataBound="GridView2_RowDataBound" AutoGenerateColumns="False" 
    DataSourceID="SqlDataSource1" AutoGenerateEditButton="True" DataKeyNames="Locations">
    <Columns>
        <asp:BoundField DataField="Locations" HeaderText="Locations" 
            SortExpression="Locations" ReadOnly="true"/>
        <asp:BoundField DataField="Lamp_pro4" HeaderText="Lamp_pro4" 
            SortExpression="Lamp_pro4" />
        <asp:BoundField DataField="Lamp_pro5" HeaderText="Lamp_pro5" 
            SortExpression="Lamp_pro5" />
        <asp:BoundField DataField="AC_Profile5" HeaderText="AC_Profile5" 
            SortExpression="AC_Profile5" />
    </Columns>

</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:TouchPadConnectionString %>" 
    SelectCommand="SELECT * FROM [Quantity]">
</asp:SqlDataSource>

My datakey is Locations and its readonly.

The .cs code for updating is:

protected void GridView2_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=ARCHANA-PC\\ARCHANA;Initial Catalog=TouchPad;Integrated Security=True");
        string LocName = GridView2.DataKeys[e.RowIndex].Values["Locations"].ToString();
        TextBox txt1 = (TextBox)GridView2.Rows[e.RowIndex].FindControl("Lamp_pro4");
        TextBox txt2 = (TextBox)GridView2.Rows[e.RowIndex].FindControl("Lamp_pro5");
        TextBox txt3 = (TextBox)GridView2.Rows[e.RowIndex].FindControl("AC_Profile5");
        string updStmt = "UPDATE Quantity set Lamp_pro4=@Lamp_pro4,Lamp_pro5=@Lamp_pro5,AC_Profile5=@AC_Profile5 where Locations=@locName";

        con.Open();
        SqlCommand updCmd = new SqlCommand(updStmt, con);

        updCmd.Parameters.AddWithValue("@locName", LocName);
        updCmd.Parameters.AddWithValue("@Lamp_pro4", txt1.Text);
        updCmd.Parameters.AddWithValue("@Lamp_pro5", txt2.Text);
        updCmd.Parameters.AddWithValue("@AC_Profile5", txt3.Text);
        updCmd.ExecuteNonQuery();
        GridView2.DataBind();

    }

Solution 1

1) You must call GridView2.DataBind() in the end of your treatment

protected void GridView2_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    //GridViewRow row = (GridViewRow)GridView2.Rows[e.RowIndex];

    string LocName = GridView2.DataKeys[e.RowIndex].Values["Locations"].ToString();
    TextBox txt1 = (TextBox)GridView2.Rows[e.RowIndex].FindControl("Lamp_pro4");
    TextBox txt2 = (TextBox)GridView2.Rows[e.RowIndex].FindControl("Lamp_pro5");
    TextBox txt3 = (TextBox)GridView2.Rows[e.RowIndex].FindControl("AC_Profile5");
    con.Open();
    SqlCommand cmd = new SqlCommand("UPDATE Quantity set Lamp_pro4='" + txt1.Text + "',Lamp_pro5='" + txt2.Text + "',AC_Profile5='" + txt3.Text + "' where Locations=" + LocName, con);
    cmd.ExecuteNonQuery();
    con.Close();

    GridView2.EditIndex = -1;
    //BindQuantity();
    GridView2.DataBind();
}

2) Define UpdateCommand on your SqlDataSource

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:TouchPadConnectionString %>" 
    SelectCommand="SELECT * FROM [Quantity]"
    UpdateCommand="UPDATE Quantity set Lamp_pro4 = @Lamp_pro4 ,Lamp_pro5=@Lamp_pro5,AC_Profile5=@AC_Profile5 where Locations=@Locations">
</asp:SqlDataSource>

Link : http://msdn.microsoft.com/fr-fr/library/system.web.ui.webcontrols.sqldatasource.updatecommand.aspx

Solution 2

Just call GridView2.DataBind() to rebind your data onto the control.

Anyway you should really use SQL Parameters to prevent SQL Injection! Have a look at this example

string connetionString = "YOUR_CONSTR" ;
string updStmt = "UPDATE Quantity set Lamp_pro4=@lamp_pro4,Lamp_pro5=@Lamp_pro5,AC_Profile5=@ac_profile5 " + 
                 "where Locations=@locName";

using (SqlConnection cnn = new SqlConnection(connetionString))
{
  cnn.Open();
  SqlCommand updCmd = new SqlCommand(updStmt , cnn);

  // use sqlParameters to prevent sql injection!
  updCmd.Parameters.AddWithValue("@lamp_pro4", txt1.Text);

  // or define dataType if necessary
  SqlParameter p1 = new SqlParameter();
  p1.ParameterName = "@Lamp_pro5";
  p1.SqlDbType = SqlDbType.Int;
  p1.Value = txt2.Text;
  updCmd.Parameters.Add(p1);

  // demo code must be adapted!! (correct paramNames, textbox names, add missing params ...)

  int affectedRows = updCmd.ExecuteNonQuery();
  Debug.WriteLine(affectedRows + " rows updated!");
}

If you have problems accessing your textboxes you may adapt this example

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    string Lamp_pro4 = e.NewValues["Lamp_pro4"].ToString();
    Debug.WriteLine("Lamp_pro4: " + Lamp_pro4); // to check if everything works fine!
}

Comments

[Archana B.R]

Nope its not working. Error at the Update command: Object reference not set to an instance of an object.

[Aghilas Yakoub]

whant the line on this exception is thrown ?

[Archana B.R]

No its not working. And yeah I will implement it with SQL Parameters once this gets goin.

[Aghilas Yakoub]

Yes you must define UpdateCommand Archana i send you link very interessant

[Archana B.R]

I also tried UpdateCommand on sqldatasorce, yet not working. I wonder should I add templatefield,Itemtemplate and edittemplate in the asp code...But I dunno how to add. kindly help by editing my code.

[Archana B.R]

Nope its not working. Error at the Update command: Object reference not set to an instance of an object. I have edited my question based on what you have sent.

[Pilgerstorfer Franz]

I don't see any instance of SqlConnection created!? Something like SqlConnection con = new SqlConnection(CON_STR) or perhaps a using like in my example. Furthermore there is no executeNonQuery() and gridView2.DataBind()

[Archana B.R]

Giving me an error in the browser: Must declare the scalar variable "@Locations". If I remove InsertVisible="False" ReadOnly="True", its working but all the other rows rows are getting edited with the same values. Help me please, while am in the urge of getting rid of this issue. Thank you

[Archana B.R]

I have edited my code in the question with the change what you asked me to do. Yest not working. When I debug the program : the txt1,txt2,txt3 is null. I think it is not holding the value that i entered in the Grid. Some small mistake. Cant figure out what it is. Kindly help.

[Richard Vivian]

Your column headings or SQL field names will automatically be declared as variables. If there is a column in your select data [Locations] , you can use set [Locations]=@locations in your insert statement. Hope this helps.

[Aghilas Yakoub]

I'am happy to help you Archana

[Pilgerstorfer Franz]

I wrote a small example code to show how you can easily access your new values within your gridView!