How to Bind Detailsview in Asp.net using Store Procedure | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorial

How to Bind Detailsview in Asp.net using Store Procedure


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

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

<asp:DetailsView ID="studentdetailview" runat="server" CellPadding="4"
            Height="50px" Width="400px" AutoGenerateRows="False" DataKeyNames="STUDENT_ID"
            AllowPaging="True"
            onpageindexchanging="studentdetailview_PageIndexChanging">
             <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
             <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
            <RowStyle BackColor="#e8d5f1" ForeColor="#333333" />
            <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <Fields>
                <asp:TemplateField HeaderText="Student Name">
                    <ItemTemplate>
                        <asp:Label ID="lblstudentname" runat="server" Text='<%# Eval("STUDENT_NAME") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Student Address">
                    <ItemTemplate>
                        <asp:Label ID="lblstudentaddress" runat="server" Text='<%# Eval("STUDENT_ADDRESS") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Student Class">
                    <ItemTemplate>
                        <asp:Label ID="lblstudentclass" runat="server" Text='<%# Eval("STUDENT_CLASS") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
              
                            </Fields>    </asp:DetailsView>

Now go to .aspx.cs page. Firstly add namespace.
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindDetailview();
        }
    }
    private void BindDetailview()
    {
        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)
        {
            studentdetailview.DataSource = dt;
            studentdetailview.DataBind();
            con.Close();
            cmd.Dispose();
        }
        else
        {
            dt.Rows.Add(dt.NewRow());
            studentdetailview.DataSource = dt;
            studentdetailview.DataBind();
            int columncount = studentdetailview.Rows[0].Cells.Count;
            studentdetailview.Rows[0].Cells.Clear();
            studentdetailview.Rows[0].Cells.Add(new TableCell());
            studentdetailview.Rows[0].Cells[0].ColumnSpan = columncount;
            studentdetailview.Rows[0].Cells[0].Text = "No Records Available";
        }
        }
        catch(Exception ex)
        {
        }

    }
    protected void studentdetailview_PageIndexChanging(object sender, DetailsViewPageEventArgs e)
    {
        studentdetailview.PageIndex = e.NewPageIndex;
        BindDetailview();
    }

In VB
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Private 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
            Binddetailsview()
        End If
    End Sub
    Private Sub Binddetailsview()
        Try
            Dim cmd As New SqlCommand("DISPLAY_DATA", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)
            Dim dt As New DataTable()
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                studentdetailview.DataSource = dt
                studentdetailview.DataBind()
            Else
                dt.Rows.Add(dt.NewRow())
                studentdetailview.DataSource = dt
                studentdetailview.DataBind()
                Dim columncount As Integer = studentdetailview.Rows(0).Cells.Count
                studentdetailview.Rows(0).Cells.Clear()
                studentdetailview.Rows(0).Cells.Add(New TableCell())
                studentdetailview.Rows(0).Cells(0).ColumnSpan = columncount
                studentdetailview.Rows(0).Cells(0).Text = "No Records Available"
            End If
        Catch ex As Exception
        End Try
    End Sub

    Protected Sub studentdetailview_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewPageEventArgs) Handles studentdetailview.PageIndexChanging
        studentdetailview.PageIndex = e.NewPageIndex
        Binddetailsview()
    End Sub

Now debug the project and check out the result.

Related Articles on Detailsview:

Ø  How to Bind, Edit, Delete and Update in Detailsview 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.

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget