value not updating in the database ? ASP.NET C#

Question

I have a gridview in which i bound the template field.delete command working fine but update command not working. Here is my aspx code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="User_ID" onrowcancelingedit="GridView1_RowCancelingEdit" 
        onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing" 
        onrowupdating="GridView1_RowUpdating" style="margin-top: 15px">
        <Columns>
            <asp:TemplateField AccessibleHeaderText="User_ID" HeaderText="User_ID">
                <EditItemTemplate>
                    <asp:TextBox ID="TXT_ID" runat="server" Text='<%# Eval("User_ID") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txt_id" runat="server" Text='<%# Eval("User_ID") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField AccessibleHeaderText="Name   " HeaderText="Name">
                <EditItemTemplate>
                    <asp:TextBox ID="TXT_NAME" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField AccessibleHeaderText="User_Name" HeaderText="User_Name">
                <EditItemTemplate>
                    <asp:TextBox ID="TXT_USERNAME" runat="server" Text='<%# Eval("User_Name") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txt_username" runat="server" Text='<%# Eval("User_Name") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField AccessibleHeaderText="Email" HeaderText="Email">
                <EditItemTemplate>
                    <asp:TextBox ID="TXT_EMAIL" runat="server" Text='<%# Eval("Email") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txt_email" runat="server" Text='<%# Eval("Email") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField AccessibleHeaderText="Password" HeaderText="Password">
                <EditItemTemplate>
                    <asp:TextBox ID="TXT_PASSWORD" runat="server" Text='<%# Eval("Password") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txt_password" runat="server" Text='<%# Eval("Password") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField AccessibleHeaderText="Date" HeaderText="Date">
                <EditItemTemplate>
                    <asp:TextBox ID="TXT_DATE" runat="server" Text='<%# Eval("Date") %>'></asp:TextBox>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="txt_Date" runat="server" Text='<%# Eval("Date") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:CommandField ShowEditButton="True" />
            <asp:CommandField ShowDeleteButton="True" />
        </Columns>
    </asp:GridView>

here is ASPX.CS code:

SqlConnection cnn = new SqlConnection("Data Source=LIFE_WELL; Initial catalog=db_compiler; Integrated security=true");
protected void Page_Load(object sender, EventArgs e)
{
    get();   
}
public void get()
{
   SqlCommand cmd = new SqlCommand("SELECT User_ID,Name,User_Name,Email,Password,Date FROM tbl_user", cnn);
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adp.Fill(dt);
    cnn.Open();
    cmd.ExecuteNonQuery();
    GridView1.DataSource = dt;
    GridView1.DataBind();
    cnn.Close();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
    GridView1.EditIndex = -1;
    get();

}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
    Label txtlbl = (Label)GridView1.Rows[e.NewEditIndex].Cells[1].FindControl("txt_id");
     Session["ID"] = txtlbl.Text;
   // Label txtName = (Label)GridView1.Rows[e.NewEditIndex].Cells[1].FindControl("txtName");
    //Label txtUser = (Label)GridView1.Rows[e.NewEditIndex].Cells[1].FindControl("txt_username");
   // Label txtEmail = (Label)GridView1.Rows[e.NewEditIndex].Cells[1].FindControl("txt_email");
   // Label txtpassword = (Label)GridView1.Rows[e.NewEditIndex].Cells[1].FindControl("txt_password");
    GridView1.EditIndex = e.NewEditIndex;
    get();

}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int id = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
    Delete(id);
    get();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
    int id = Int32.Parse(GridView1.DataKeys[e.RowIndex].Value.ToString());
   // TextBox ID = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TXT_ID");
    TextBox Name = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TXT_Name");
    TextBox USERNAME = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TXT_USERNAME");
    TextBox EMAIL = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TXT_EMAIL");
    TextBox PASSWORD = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TXT_PASSWORD");
    //TextBox DATE = (TextBox)GridView1.Rows[e.RowIndex].FindControl("TXT_DATE");
    updateTbl(id,Name.Text,USERNAME.Text,EMAIL.Text,PASSWORD.Text);
    GridView1.EditIndex = -1;
    get();
}
public void updateTbl(int id,string name,string username,string email,string pass)
{
    try
    {
        string u= Session["ID"].ToString();
        int i = Int32.Parse(u);
        //String db = Session["value"].ToString();

        //string RNquery = "USE " + db + " EXEC sp_rename '" + oldname + "', '" + newname + "'";
        string updateQuery = "USE db_compiler Update tbl_user SET Name='" + name + "',User_Name='" + username + "',Email='" + email + "',Password='" + pass + "',Confirm_Password='" + pass + "' WHERE User_ID="+ id+"";

        SqlCommand cmd2 = new SqlCommand(updateQuery, cnn);
        // SqlCommand cmd = new SqlCommand(RNquery, cnn);
        cnn.Open();
        cmd2.ExecuteNonQuery();
        //cmd.ExecuteNonQuery();

        cnn.Close();
    }
    catch (SqlException ex)
    {
        Response.Write("<script>alert("+ ex.Message + ")</script>");
    }
}
public void Delete(int id)
{
    string QUERY = "USE db_compiler DELETE FROM tbl_user WHERE User_ID=" + id + "";
    SqlCommand cmd = new SqlCommand(QUERY, cnn);
    cnn.Open();
    cmd.ExecuteNonQuery();
    cnn.Close();
}

No error display.But value not updating.Thanks

Answer 1

Use the below property on gridview which will allow you to get newvalues instead of old one

Enableviewstate="False"

Answer 2

There might be different reasons of such behaviour and if an unknown issue has occurred in the code where you have a try..catch structure, first what you should do is to get rid of try..catch. For example, your code catches only SqlException and all other exceptions will be ignored. Even it a SqlException would occur, you output its message with a js script that might fail/ignored/etc. Remove try..catch and see if it gives any output.

If there is still no error will be shown, you should debug the code step by step.

Set a breakpoint to updateTbl() and see how your final sql statement will look like. Copy-paste it to Sql Server Management Studio and try to execute it from there. It might be that you missed something there. For example, it looks strange that you have a Confirm_Password column in the database. Make sure that all values from all columns got correct values and that id has got an id of edited row (to make sure that you do not update different row and you do not update correct row with old values).

Other issues in your code:

1. as it was told, your code is vulnerable for sql injections

2. there are unused parts that might get debugging complex, e.g.

   Label txtlbl = (Label)GridView1.Rows[e.NewEditIndex].Cells[1].FindControl("txt_id");
   Session["ID"] = txtlbl.Text;
   

   or

   string u= Session["ID"].ToString();
   int i = Int32.Parse(u);
   

3. coding style is sometimes strange, e.g.

   ..." + id + "";
   

4. it seems no need to call USE db_compiler in every statement if db is only one and set in ...Initial catalog=db_compiler;.... Suppose you need to move your application on another server where db will be named differenty - you would need to change all the code because of hardcoded USE db_compiler in it. (The connection string to db must be also moved to web.config).

Answer 3

you have problem with post back , actually after pressing update button your page do a postback and your changed/edited values are replace by old values , so your record update but with old values so you dont see any change. use this code in your button click event. so that your page dont do any post back.

Response.Redirect(Request.RawUrl);