How to Bind Formview Data control in Asp.net using Store Procedure

Introduction: In this post I try to explain how we can bind the Formview Data control in Asp.net using Store procedure.

Description:

I have created a table name STUDENT_DETAIL and insert data into table. STUDENT_ID is primary key.

STUDENT_ID	int
STUDENT_NAME	varchar(50)
STUDENT_ADDRESS	varchar(50)
STUDENT_CLASS	varchar(50)

Create a Store Procedure:

CREATE PROCEDURE DISPLAY_DATA

     

AS

BEGIN

     

      SET NOCOUNT ON;

SELECT * FROM dbo.STUDENT_DETAIL

 

END

GO

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 Formview data control from Toolbox.

<asp:FormView ID="formviewstudent" runat="server" DataKeyNames="STUDENT_ID"

            AllowPaging="True" onpageindexchanging="formviewstudent_PageIndexChanging1"

                    >

             <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

        <RowStyle BackColor="#EFF3FB" />

        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <ItemTemplate>

            <table style="border:1px solid #c1c1c1;">

            <tr style="background-color:#E5E5FE;font-weight:bold"><td>Student Detail</td></tr>

        

              <tr> <td><b>Student Name:-</b></td><td><asp:Label ID="lblstudentname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:Label></td></tr>

              

              <tr><td><b>Student Address:-</b></td><td><asp:Label ID="lblstudentaddress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label></td></tr>

              

              <tr><td><b>Student Class:-</b></td><td><asp:Label ID="lblstudentclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label></td></tr>

          

                </table>

            </ItemTemplate>

        

            <EmptyDataTemplate>

            <table style="border:1px solid #c1c1c1;">

            <tr style="background-color:#E5E5FE;font-weight:bold"><td><b>Student Details</b></td></tr>

            <tr><td><b>Student Name:-</b></td><td style="color:Red;">No Records Available!</td></tr>

            <tr><td><b>Student Address:-</b></td><td style="color:Red;">No Records Available!</td></tr>

            <tr><td><b>Student Class:-</b></td><td style="color:Red;">No Records Available!</td></tr>

           

            </table>

            </EmptyDataTemplate>

        </asp:FormView>

Now go to .aspx.cs page.

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BIndFormview();

        }

    }

    private void BIndFormview()

    {

        try

        {

            SqlCommand cmd = new SqlCommand("DISPLAY_DATA", con);

            con.Open();

            SqlDataAdapter adp = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            adp.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                formviewstudent.DataSource = dt;

                formviewstudent.DataBind();

                con.Close();

                cmd.Dispose();

            }

            else

            {

                formviewstudent.DataSource = null;

                formviewstudent.DataBind();

            }

        }

        catch (Exception ex)

        {

        }

    }

   

    protected void formviewstudent_PageIndexChanging1(object sender, FormViewPageEventArgs e)

    {

        formviewstudent.PageIndex = e.NewPageIndex;

        BIndFormview();

    }

In VB

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

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 Not IsPostBack Then

            Bindformview()

        End If

    End Sub

    Private Sub Bindformview()

        Try

            Dim cmd As New SqlCommand("DISPLAY_DATA", con)

            cmd.CommandType = CommandType.StoredProcedure

            con.Open()

            Dim adp As New SqlDataAdapter(cmd)

            Dim dt As New DataTable

            adp.Fill(dt)

            If dt.Rows.Count > 0 Then

                formviewstudent.DataSource = dt

                formviewstudent.DataBind()

                con.Close()

            Else

                formviewstudent.DataSource = Nothing

                formviewstudent.DataBind()

            End If

        Catch ex As Exception

        End Try

    End Sub

    Protected Sub formviewstudent_PageIndexChanging1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewPageEventArgs) Handles formviewstudent.PageIndexChanging

        formviewstudent.PageIndex = e.NewPageIndex

        Bindformview()

    End Sub

Now debug the project and check the result.

Related Articles on Formview:

Ø  How to Bind, Edit, Delete and Update in Formview inAsp.net?

Ø  How to Bind Formview controlusing Sqldataadapter, Datatable and Query in Asp.net?