Cannot update in Datagrid

Who is online? 0 guests and 0 members

home » forums » asp.net topics » getting started / general asp.net » Cannot update in Datagrid

Cannot update in Datagrid

Posts under the topic: Cannot update in Datagrid

Posted: 7/16/2011

rino raju

30 points Lurker

Joined on: 6/8/2011
Posts: 6

Please hlep as i cannot update the datagid in asp.net below is the code ,m not geeting where m a goin wrong.

After editing the Datagrid when i click on update the database is not updated.There is no error message.

Please help

>>>ASP.net Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

</div>

<table>

<tr>

<td>

<asp:Label ID="id" runat="server" Text="ID"></asp:Label>

<asp:TextBox ID="t1" runat="server" ></asp:TextBox>

</td>

<td>

<asp:Label ID="nm" runat="server" Text="Name"></asp:Label>

<asp:TextBox ID="t2" runat="server" ></asp:TextBox>

</td>

<td>

<asp:Label ID="lnm" runat="server" Text="Last Name"></asp:Label>

<asp:TextBox ID="t3" runat="server" ></asp:TextBox>

</td>

</tr>

</table>

<asp:DataGrid ID="Dg1" runat="server" oncancelcommand="Dg1_CancelCommand"

oneditcommand="Dg1_EditCommand" onupdatecommand="Dg1_UpdateCommand">

<asp:BoundColumn HeaderText="ID" DataField="id">

</asp:BoundColumn>

<asp:BoundColumn HeaderText="NAME" DataField="name">

</asp:BoundColumn>

<asp:BoundColumn HeaderText="LAST NAME" DataField="last_name">

</asp:BoundColumn>

<asp:EditCommandColumn EditText="EDIT" CancelText="CANCEL" UpdateText="UPDATE" HeaderText="EDIT">

</asp:EditCommandColumn>

<asp:ButtonColumn CommandName="delete" HeaderText="DELETE" Text="DELETE">

</asp:ButtonColumn>

</Columns>

</asp:DataGrid>

</form>

</body>

</html>

C# Code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

public partial class _Default : System.Web.UI.Page

{

SqlConnection con1;

SqlDataAdapter da;

DataSet ds;

SqlCommand com;

DataTable dt;

protected void Page_Load(object sender, EventArgs e)

{

string str= ConfigurationManager.ConnectionStrings["con"].ConnectionString;

con1 = new SqlConnection(str);

com = new SqlCommand("select * from details", con1);

ds = new DataSet();

da = new SqlDataAdapter(com);

da.Fill(ds, "details");

dt = ds.Tables["details"];

con1.Open();

com.ExecuteNonQuery();

Dg1.DataSource = dt;

Dg1.DataBind();

con1.Close();

}

static int num;

protected void Dg1_EditCommand(object source, DataGridCommandEventArgs e)

{

num = Convert.ToInt16(e.Item.Cells[0].Text.ToString());

Dg1.EditItemIndex = e.Item.ItemIndex;

databind();

}

protected void Dg1_CancelCommand(object source, DataGridCommandEventArgs e)

{

Dg1.EditItemIndex = -1;

databind();

}

public void databind()

{

string str = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

con1 = new SqlConnection(str);

com = new SqlCommand("select * from details", con1);

ds = new DataSet();

da = new SqlDataAdapter(com);

da.Fill(ds, "details");

dt = ds.Tables["details"];

con1.Open();

com.ExecuteNonQuery();

Dg1.DataSource = dt;

Dg1.DataBind();

con1.Close();

}

protected void Dg1_UpdateCommand(object source, DataGridCommandEventArgs e)

{

string str = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

con1 = new SqlConnection(str);

com.Parameters.Add("@EmpId", SqlDbType.Int).Value = ((TextBox)e.Item.Cells[0].Controls[0]).Text;

com.Parameters.Add("@F_Name", SqlDbType.VarChar).Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;

com.Parameters.Add("@L_Name", SqlDbType.VarChar).Value = ((TextBox)e.Item.Cells[2].Controls[0]).Text;

//TextBox t1, t2, t3;

//t1 = (TextBox)e.Item.Cells[0].Controls[0];

//t2 = (TextBox)e.Item.Cells[1].Controls[0];

//t3 = (TextBox)e.Item.Cells[2].Controls[0];

com.CommandText = "update details set id=@EmpId,name=@F_Name,last_name=@L_Name where id=@EmpId";

con1.Open();

com.Connection = con1;

com.ExecuteNonQuery();

com.Connection.Close();

Dg1.EditItemIndex=-1;

databind();

}

Thankyou,

Rino

Posted: 7/17/2011

Raghav Khunger

16813 points Guru

Joined on: 4/19/2009
Posts: 490

Hi,

You need to change two things in your code:
1st: Add AutoGenerateColumns="false" to your datagrid.

    <asp:DataGrid ID="Dg1" runat="server" OnCancelCommand="Dg1_CancelCommand" OnEditCommand="Dg1_EditCommand"
        OnUpdateCommand="Dg1_UpdateCommand" AutoGenerateColumns="false">

2nd: Wrap your code of datagrid databind in if (!IsPostBack) :

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string str = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
            con1 = new SqlConnection(str);
            com = new SqlCommand("select * from details", con1);
            ds = new DataSet();
            da = new SqlDataAdapter(com);

            da.Fill(ds, "details");
            dt = ds.Tables["details"];
            con1.Open();
            com.ExecuteNonQuery();
            Dg1.DataSource = dt;
            Dg1.DataBind();
            con1.Close();
        }
    }

Your grid will update the records successfully now.

<b>[<a href="https://mvp.support.microsoft.com/profile/Raghav">ASP.NET MVP</a>] [<a href="https://www.microsoftcommunitycontributor.com">Microsoft® Community Contributor</a>] [<a href="http://www.dzone.com/aboutmvb">Dzone MVB</a>] [<a href="https://twitter.com/#!/RaghavKhunger">Follow me on Twitter</a>]</b> <br/><br/><b>[<a href="http://codeasp.net/wiki/view/10/how-to-ask-questions-on-forums">How to ask questions on forums</a>]</b> <br/><br/> "Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous <br/> "Success is simply a matter of luck. Ask any failure." Anonymous

Posted: 7/17/2011

rino raju

30 points Lurker

Joined on: 6/8/2011
Posts: 6

Thankyou Raghav for the effort i had updated the code with as suggested now i m getin Message:

NullRefrerence Exception was Unhandelled by usercode

"Object reference not set to an instance of an object"

The above error is received at the following code below marked as Bold:

protected void Dg1_UpdateCommand(object source, DataGridCommandEventArgs e)

{

string str = ConfigurationManager.ConnectionStrings["con"].ConnectionString;

con1 = new SqlConnection(str);

com.Parameters.Add("@EmpId", SqlDbType.Int).Value = ((TextBox)e.Item.Cells[0].Controls[0]).Text;

com.Parameters.Add("@F_Name", SqlDbType.VarChar).Value = ((TextBox)e.Item.Cells[1].Controls[0]).Text;

com.Parameters.Add("@L_Name", SqlDbType.VarChar).Value = ((TextBox)e.Item.Cells[2].Controls[0]).Text;

com.CommandText = "update details set id=@EmpId,name=@F_Name,last_name=@L_Name where id=@EmpId";

con1.Open();

com.Connection = con1;

com.ExecuteNonQuery();

com.Connection.Close();

Dg1.EditItemIndex=-1;

databind();

}

Tables definition given below:

Column Name DataType Allow null

ID Int Yes

name Vchar Yes

Last_name Vchar Yes

Please help with the above problem.

Thankyou your help appriciated.

Posted: 7/17/2011

Raghav Khunger

16813 points Guru

Joined on: 4/19/2009
Posts: 490

The same code was working for me and I was able to update the data in DB. You are getting the issue because of this line:

com.Parameters.Add("@EmpId", SqlDbType.Int).Value = ((TextBox)e.Item.Cells[0].Controls[0]).Text;

because your com is null. You haven't set it to new instance. So before the above line you have to set it to new instance of SQLCommand, write this

        com=new SqlCommand();
        com.Parameters.Add("@EmpId", SqlDbType.Int).Value = ((TextBox)e.Item.Cells[0].Controls[0]).Text;

Posted: 7/17/2011

rino raju

30 points Lurker

Joined on: 6/8/2011
Posts: 6

Thankyou Raghav this worked .You wer very helpfull.

Three Chears for you ............

Page 1 of 1 (5 items)