Tuesday, April 30, 2013

How to bind Gridview using Sqldataadapter, Datatable and Query in Asp.net(C#, VB)?


Introduction: In this post I have try to explain how to bind Gridview in Asp.net.

Description:
I have create a table name STUDENT_DETAIL and insert data into table.
STUDENT_ID
int
STUDENT_NAME
varchar(50)
STUDENT_ADDRESS
varchar(50)
STUDENT_CLASS
varchar(50)

STUDENT_ID is primary key.

Now open the Visual Studio>Go to File>New>Website. Add the Connectionstring in web.config file of website.
<configuration>
       <connectionStrings>
    <add name="connection" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True"/>
       </connectionStrings>
       <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
</configuration>


After that add new web form to website, drag and drop the Gridview data control from
Toolbox. After that add the Template field and ItemTemplate in Gridview as structure mention below.

<asp:GridView ID="grdstudentdetail" runat="server" AutoGenerateColumns="False" DataKeyNames="STUDENT_ID">
            <Columns>
                <asp:TemplateField HeaderText="STUDENT NAME">
                    <ItemTemplate>
                        <asp:Label ID="lblname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="STUDENT ADDRESS">
                    <ItemTemplate>
                        <asp:Label ID="lbladdress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="STUDENT CLASS">
                    <ItemTemplate>
                        <asp:Label ID="lblclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

Now go to .aspx.cs page and add namespace.

using System.Configuration;
using System.Data.SqlClient;
using System.Data;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        if (!IsPostBack)
        {
            Bindgridview();
        }
    }
    private void Bindgridview()
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("Select * from STUDENT_DETAIL", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                grdstudentdetail.DataSource = dt;
                grdstudentdetail.DataBind();
            }
            else
            {
                dt.Rows.Add(dt.NewRow());
                grdstudentdetail.DataSource = dt;
                grdstudentdetail.DataBind();
                int columncount = grdstudentdetail.Rows[0].Cells.Count;
                grdstudentdetail.Rows[0].Cells.Clear();
                grdstudentdetail.Rows[0].Cells.Add(new TableCell());
                grdstudentdetail.Rows[0].Cells[0].ColumnSpan = columncount;
                grdstudentdetail.Rows[0].Cells[0].Text = "No Records Available
";
            }
        }
        catch (Exception ex)
        {
        }
    }

In VB:


Add namespace to .aspx.vb page.



Imports System.Data

Imports System.Data.SqlClient
Imports System.Configuration

Create a function to bind Gridview.

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        If Not IsPostBack Then
            Bindgridview()
        End If
    End Sub
    Private Sub Bindgridview()
        Try
            Dim adp As New SqlDataAdapter("Select * from STUDENT_DETAIL", con)
            Dim dt As New DataTable()
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                grdstudentdetail.DataSource = dt
                grdstudentdetail.DataBind()
            Else
                dt.Rows.Add(dt.NewRow())
                grdstudentdetail.DataSource = dt
                grdstudentdetail.DataBind()
                Dim columncount As Integer = grdstudentdetail.Rows(0).Cells.Count
                grdstudentdetail.Rows(0).Cells.Clear()
                grdstudentdetail.Rows(0).Cells.Add(New TableCell())
                grdstudentdetail.Rows(0).Cells(0).ColumnSpan = columncount
                grdstudentdetail.Rows(0).Cells(0).Text = "No Records Available"
            End If
        Catch ex As Exception
        End Try
    End Sub

Now debug the application and check the result.

Related Articles on Gridview:

Ø  How to Bind Gridview with Datareader in asp.net

Ø  How to highlight row on mouse hover in Gridview

Ø  How to edit and update Dropdownlist in Gridview datacontrol in Asp.net

Ø  How to Search Records in Gridview in Asp.net

Ø  How to Bind Gridview using Store Procedure, SqlDataAdapterand Datatable in Asp.net

Ø  How to use RadioButtonList control inside the Gridview inAsp.net

                         
  ØHow to use Fileupload control in Gridview inAsp.net

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

No comments:

Post a Comment