ASP.NET

Test

2025-09-01T09:03:00.000-07:00

Databound fields in GridView using Ado.net in asp.net C#

2016-04-10T06:01:00.000-07:00

Hello there, here i m explaining how to use Databound fields using gridview .

Now first of all we will create a database. Here the following script, copy it and run it.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbEmp]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tbEmp](

[id] [int] IDENTITY(1,1) NOT NULL,

[name] [varchar](50) NULL,

[age] [int] NULL,

[address] [varchar](50) NULL

) ON [PRIMARY]

END

Place the GridView control on a form and set the following property.

AutoGenerateColumns="False"

And set the Gridview Events name is

onrowcancelingedit="GridView1_RowCancelingEdit"

onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"

onrowupdating="GridView1_RowUpdating"

Or add the default.aspx page and paste the following source code in it.

<%@ 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">

<title>Untitled Page</title>

.style1

{

width: 100%;

}

</style>

</head>

<body>

<div>

</div>

<tr>

<td>

Name</td>

<td>

<asp:TextBox ID="txt_name" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>

Age</td>

<td>

<asp:TextBox ID="txt_age" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>

Address</td>

<td>

<asp:TextBox ID="txt_address" runat="server"></asp:TextBox></td>

</tr>

<tr>

<td>

 </td>

<td>

<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />

</td>

</tr>

<tr>

<asp:Label ID="lbl_msg" runat="server" ForeColor="Red"></asp:Label>

</td>

</tr>

<tr>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

AutoGenerateDeleteButton="True" AutoGenerateEditButton="True"

onrowdeleting="GridView1_RowDeleting" onrowediting="GridView1_RowEditing"

onrowcancelingedit="GridView1_RowCancelingEdit"

onrowupdating="GridView1_RowUpdating" CellPadding="4" ForeColor="#333333"

GridLines="None">

<asp:BoundField DataField="id" HeaderText="Id" ReadOnly="True" />

<asp:BoundField DataField="Name" HeaderText="Name" />

<asp:BoundField DataField="Age" HeaderText="Age" />

<asp:BoundField DataField="address" HeaderText="address" />

</Columns>

</asp:GridView>

</td>

</tr>

</table>

</form>

</body>

</html>

In Coding

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

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

{

SqlConnection con = new SqlConnection();

SqlDataAdapter adp;

SqlCommand cmd;

DataTable dt;

protected void Page_Load(object sender, EventArgs e)

{

con.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;

con.Open();

if (con.State == ConnectionState.Closed)

{ con.Open(); }

con.Close();

if (IsPostBack == false)

{ grdview(); }

}

private void grdview()

{

if (con.State == ConnectionState.Closed)

{ con.Open(); }

adp = new SqlDataAdapter("select * from tbemp order by id desc", con);

dt = new DataTable();

adp.Fill(dt);

adp.Dispose();

if(dt.Rows.Count == 0)

{

lbl_msg.Text = "No Record";

}

else

{

lbl_msg.Text = String.Empty;

GridView1.DataSource = dt;

GridView1.DataBind();

}

dt.Dispose();

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

if (con.State == ConnectionState.Closed)

{ con.Open(); }

cmd = new SqlCommand("insert into tbEmp values(@name,@age,@address)", con);

cmd.Parameters.AddWithValue("@name", txt_name.Text);

cmd.Parameters.AddWithValue("@age", txt_age.Text);

cmd.Parameters.AddWithValue("@address", txt_address.Text);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

grdview();

txt_name.Text = String.Empty;

txt_age.Text = String.Empty;

txt_address.Text = String.Empty;

}

// Deleting the Record

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

if (con.State == ConnectionState.Closed)

{ con.Open(); }

Int32 idd = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);

cmd = new SqlCommand("delete from tbemp where id=@id", con);

cmd.Parameters.AddWithValue("@id", idd);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

grdview();

}

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

GridView1.EditIndex = -1;

grdview();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

grdview();

}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

if (con.State == ConnectionState.Closed)

{ con.Open(); }

Int32 idd = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);

cmd = new SqlCommand("update tbemp set name=@name,age=@age,address=@address where id=@id", con);

cmd.Parameters.AddWithValue("@name", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text);

cmd.Parameters.AddWithValue("@age", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text);

cmd.Parameters.AddWithValue("@address", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text);

cmd.Parameters.AddWithValue("@id", idd);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

GridView1.EditIndex = -1;

grdview();

}

EDIT RECORDS

EDITED RECORDS

DataList Paging using ado.net in asp.net C#

2016-04-10T05:48:00.000-07:00

In this article i am explaining how to do paging in datalist as we know datalist control does not support paging properties as Gridview control support. In this article i m displaying two images for paging and according to your requirement you can change it. First of all create a database following the script which you can copy and paste it in your sql server 2005.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbStudent]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tbStudent](

[id] [int] IDENTITY(10,1) NOT NULL,

[name] [varchar](50) NULL,

[age] [int] NULL,

[rollno] [int] NULL,

[address] [varchar](50) NULL,

[image] [varchar](50) NULL

) ON [PRIMARY]

END

Now create a folder name pics in your website.

Few property must be set like

Repeatcolumns =2 or you can change it

Now add a page name it dtlist.aspx and paste the souce code in it.

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

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

<title>Untitled Page</title>

.style1

{

width: 85%;

height: 67px;

}

.style2

{

width: 770px;

}

.style3

{

width: 76px;

}

.style4

{

width: 67px;

}

.style5

{

width: 67px;

height: 21px;

}

.style6

{

height: 21px;

}

.style7

{

width: 62px;

}

</style>

</head>

<body>

<div>

<tr>

<tr>

Name</td>

<td>

<asp:TextBox ID="txt_name" runat="server" style="margin-bottom: 0px"></asp:TextBox>

</td>

</tr>

<tr>

age</td>

<td>

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

</td>

</tr>

<tr>

rollno</td>

<td>

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

</td>

</tr>

<tr>

address</td>

<td>

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

</td>

</tr>

<tr>

image</td>

<td>

<asp:FileUpload ID="fileupload" runat="server" />

<asp:RegularExpressionValidator ID="RegularExpressionValidator1"

runat="server"

ErrorMessage="Invalid File!(only .gif, .jpg, .jpeg, .wav Files are supported)"

ValidationExpression="^.+(.jpg|.JPG|.gif|.GIF|.jpeg|JPEG)$"

ControlToValidate="fileupload"></asp:RegularExpressionValidator>

</td>

</tr>

<tr>

 </td>

<td>

<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Insert" />

</td>

</tr>

</table>

</td>

 </td>

<td>

 </td>

</tr>

<asp:DataList ID="DataList1" runat="server" RepeatColumns="2" Width="500px"

CellPadding="4" ForeColor="#333333" Height="204px"

ShowFooter="False" ShowHeader="False">

<tr>

<img src='pics/<%#Eval("image") %>' style="height: 95px; width: 93px" />

</td>

name</td>

<td>

<asp:Label ID="Label1" runat="server" Text='<%# Eval("name") %>'></asp:Label>

</td>

</tr>

<tr>

age</td>

<td>

<asp:Label ID="Label2" runat="server" Text='<%# Eval("age") %>' Width="200px"></asp:Label>

</td>

</tr>

<tr>

rollno</td>

<asp:Label ID="Label3" runat="server" Text='<%# Eval("rollno") %>'></asp:Label>

</td>

</tr>

<tr>

address</td>

<td>

<asp:Label ID="Label4" runat="server" Text='<%# Eval("address") %>'

Width="250px"></asp:Label>

</td>

</tr>

</table>

</ItemTemplate>

</asp:DataList>

</table>

</div>

<p>

<asp:LinkButton ID="btn_prev" runat="server" onclick="btn_prev_Click">Prev</asp:LinkButton>

<asp:LinkButton ID="btn_first" runat="server" onclick="btn_first_Click">First</asp:LinkButton>

<asp:LinkButton ID="btn_nxt" runat="server" onclick="btn_nxt_Click">Next</asp:LinkButton>

<asp:LinkButton ID="btn_last" runat="server" onclick="btn_last_Click">Last</asp:LinkButton>

</p>

<p>

<asp:Label ID="lbl_msg" runat="server"></asp:Label>

</p>

</form>

</body>

</html>

In Coding

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.IO;

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

{

decimal count; //for count the total records.

Decimal last1; // to access the last record

String fn; // for image name

String pth; // for storing the path

SqlConnection con = new SqlConnection();

SqlCommand cmd;

PagedDataSource pageData = new PagedDataSource();

protected void Page_Load(object sender, EventArgs e)

{

con.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;

con.Open();

doPaging(); //will call this message and fetch the data from database

}

public DataTable getTheData()

{

SqlDataAdapter objSQLAdapter = new SqlDataAdapter("select *from [tbStudent]", con);

DataSet DS = new DataSet();

objSQLAdapter.Fill(DS);

if (DS.Tables[0].Rows.Count == 0)

{

lbl_msg.Text = "";

DataList1.Visible = false;

}

else

{

Session["cnt"] = DS.Tables[0].Rows.Count;

}

return DS.Tables[0];

}

private void doPaging()

{

//paging function

try

{

pageData.DataSource = getTheData().DefaultView; //will call the getTheData() method

pageData.AllowPaging = true;

pageData.PageSize = 2;

count = Convert.ToInt32(Session["cnt"]);

last1 = count / pageData.PageSize;

last1 = Convert.ToDecimal(Math.Ceiling(last1)); // will convert the 17/4 = 4.5 --> 5 pages

try

{

pageData.CurrentPageIndex = Int32.Parse(Request["page"].ToString());

}

catch

{

pageData.CurrentPageIndex = 0; // default first 4 images

}

btn_prev.Visible = (!pageData.IsFirstPage);

btn_first.Visible = (!pageData.IsFirstPage);

btn_nxt.Visible = (!pageData.IsLastPage);

btn_last.Visible = (!pageData.IsLastPage);

DataList1.DataSource = pageData;

DataList1.DataBind();

}

catch

{

}

protected void Button1_Click(object sender, EventArgs e)

{

if (fileupload.PostedFile.ContentLength > 0)

{

fn = Path.GetFileName(fileupload.FileName);

pth = Server.MapPath("pics") + "/" + fn; // fn for file image name

fileupload.SaveAs(pth); // storing the image in pics folder *Be sure pics folder must exist there..

}

if (con.State == ConnectionState.Closed)

{

con.Open();

}

cmd = new SqlCommand("insert into [tbStudent] values(@name,@age,@rollno,@address,@image)", con);

cmd.Parameters.AddWithValue("@name", txt_name.Text);

cmd.Parameters.AddWithValue("@age", txt_age.Text);

cmd.Parameters.AddWithValue("@rollno", txt_rollno.Text);

cmd.Parameters.AddWithValue("@address", txt_address.Text);

cmd.Parameters.AddWithValue("@image", fn);

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Close();

doPaging();

}

protected void btn_prev_Click(object sender, EventArgs e)

{

Response.Redirect(Request.CurrentExecutionFilePath + "?Page=" + (pageData.CurrentPageIndex - 1).ToString());

}

protected void btn_first_Click(object sender, EventArgs e)

{

Response.Redirect(Request.CurrentExecutionFilePath + "?Page=" + (pageData.CurrentPageIndex == 1).ToString());

}

protected void btn_nxt_Click(object sender, EventArgs e)

{

Response.Redirect("dtlist.aspx?page=" + Convert.ToInt32(last1 - 1)); // dtlist.aspx to access the last record.

}

protected void btn_last_Click(object sender, EventArgs e)

{

Response.Redirect(Request.CurrentExecutionFilePath + "?Page=" + (pageData.CurrentPageIndex + 1).ToString());

}

How to create Temporary Table in asp.net with compute column in C#

2010-10-08T22:42:00.000-07:00

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

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

{

Int32 tt; // tt for total it will calculate the total

DataTable tb = new DataTable(); //create a instance tb of Datatable

protected void Page_Load(object sender, EventArgs e)

{

// here we will create a temporary table of columns under ispostback.

if (IsPostBack == false)

{

//”ord” is a datatable type which create a “Table” and “c” for datatable columns.

DataTable ord = new DataTable("Table"); //the “Table” value must be same for

DataColumn c = new DataColumn(); // always

ord.Columns.Add(new DataColumn("value", Type.GetType("System.Int32")));

// “value” is a column name with Int32 datatype.

ord.Columns.Add(new DataColumn("length", Type.GetType("System.Int32")));

// “length” is a column name with Int32 datatype.

ord.Columns.Add(new DataColumn("breadth", Type.GetType("System.Int32")));

// “total” is a column name with Int32 datatype.

ord.Columns.Add(new DataColumn("total", Type.GetType("System.Decimal")));

// “total” is a Expression which calculate the following formula

ord.Columns["total"].Expression = "value/(length*breadth)";

//storing the structure in “ss” name session.

Session["ss"] = ord;

}

protected void btn_submit_Click(object sender, EventArgs e)

{

grdview();// will call the grdview method

}

// following the temporary table with structure

private void tempdata()

{

tb = (DataTable)(Session["ss"]); //getting the structure from session and

GridView1.DataSource = tb; // storing in “tb” datatable variable and bind with

GridView1.DataBind(); // gridview

Int32 a; Int32 i; ;

a = tb.Rows.Count; // it will count the total records in the temporary table

for (i = 0; i < a; i++)

{

tt += Convert.ToInt32(tb.Rows[i][3]);// will sum the each value

}

Label1.Text = tt.ToString();// display in label the total values

}

private void grdview()

{

//retreiving the structure from session and after creating the new row

// storing the data in rows as index wise. As we know we cannot store Expression column

// in database name is “total” which calculating the formula.

tb = (DataTable)(Session["ss"]);

DataRow r1 = tb.NewRow();

r1[0] = Convert.ToInt32(txt_value.Text);

r1[1] = Convert.ToInt32(txt_length.Text);

r1[2] = Convert.ToInt32(txt_breadth.Text);

tb.Rows.Add(r1);

GridView1.DataSource = tb; //now binding the gridview with data.

GridView1.DataBind();

Int32 a; Int32 i; ;

a = tb.Rows.Count;

tt = 0;

for (i = 0; i < a; i++)

{

]);// will sum the each value according to index of the column.

tt += Convert.ToInt32(tb.Rows[i][3]);

}

Label1.Text = tt.ToString(); //will store the Total amount of the Square feet..

}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

try

{

tb = (DataTable)(Session["ss"]);

tb.Rows.RemoveAt(e.RowIndex); //deleting the records and decreasing the total

tempdata(); //calling the tempdata form refresh the output

}

catch

{ }

}

store the data into database table

*put the below code where u need.
DataTable tb_data = new DataTable();
tb_data = Session(“ss”);    //** here u can store ur all records from session to data table.
savedataintotable();

private void savedataintotable()
{
SqlDataAdapter adp1 = new SqlDataAdapter(“select * from table”, con); //* columnname must be same
                                                                                                                      table.
SqlCommandBuilder cb = new SqlCommandBuilder(adp1);
DataSet ds1 = new DataSet();
adp1.Fill(ds1);
try {
adp1.Update(tb_data);
} catch (Exception ex) {
}
try
{
tb_data.AcceptChanges()
}
catch (Exception ex) {
}
}
hope it will help u