How to Bind Formview Data control in Asp.net using Store Procedure - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

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?

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon