Display data from Database in HTML table using Stored Procedure in ASP.Net

Last Reply on Dec 14, 2016 04:14 AM By Indresh

Posted on Dec 13, 2016 01:03 AM

Mr.Mudassar

I am using your code Display data from database in HTML table in ASP.Net

But It is in query string. But I need using strored Procedure due to large data. I have given code below

Pls advice me 

Maideen

 

        Dim constr As String = ConfigurationManager.ConnectionStrings("ConnectString").ConnectionString
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand("usp_CL_LogType_Details")
                cmd.Parameters.AddWithValue("@Action", "INBOUND_ACTIVE_HTML_TABLE")
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure
                Using sda As New SqlDataAdapter(cmd)
                    Dim dt As New DataTable()
                    sda.Fill(dt)
                    Dim html As New StringBuilder()
                    html.Append("<table border = '1'>")
                    html.Append("<tr>")
                    For Each column As DataColumn In dt.Columns
                        html.Append("<th>")
                        html.Append(column.ColumnName)
                        html.Append("</th>")
                    Next
                    html.Append("</tr>")

                    For Each row As DataRow In dt.Rows
                        html.Append("<tr>")
                        For Each column As DataColumn In dt.Columns
                            html.Append("<td>")
                            html.Append(row(column.ColumnName))
                            html.Append("</td>")
                        Next
                        html.Append("</tr>")
                    Next
                    html.Append("</table>")
                    PlaceHolder1.Controls.Add(New Literal() With {.Text = html.ToString()})

                End Using
            End Using
        End Using

 

You are viewing reply posted by: Indresh on Dec 14, 2016 04:14 AM.
Posted on Dec 14, 2016 04:14 AM Modified on on Dec 14, 2016 05:39 AM

Hi maideen,

I have modified the article code and using the store procedure.

HTML

   <form id="form1" runat="server">
    <asp:PlaceHolder ID="PlaceHolder1" runat="server" />
    </form>

VB.NET

Protected Sub Page_Load(sender As Object, e As EventArgs)
	If Not Me.IsPostBack Then
		Me.PopulateCustomers()
	End If
End Sub

Private Sub PopulateCustomers()

	'Populating a DataTable from database.
	Dim dt As DataTable = Me.GetData()

	'Building an HTML string.
	Dim html As New StringBuilder()

	'Table start.
	html.Append("<table border = '1'>")

	'Building the Header row.
	html.Append("<tr>")
	For Each column As DataColumn In dt.Columns
		html.Append("<th>")
		html.Append(column.ColumnName)
		html.Append("</th>")
	Next
	html.Append("</tr>")

	'Building the Data rows.
	For Each row As DataRow In dt.Rows
		html.Append("<tr>")
		For Each column As DataColumn In dt.Columns
			html.Append("<td>")
			html.Append(row(column.ColumnName))
			html.Append("</td>")
		Next
		html.Append("</tr>")
	Next

	'Table end.
	html.Append("</table>")

	'Append the HTML string to Placeholder.
	PlaceHolder1.Controls.Add(New Literal() With { _
		Key .Text = html.ToString() _
	})
End Sub

Private Function GetData() As DataTable
	Dim constr As String = ConfigurationManager.ConnectionStrings("Constring").ConnectionString
	Using con As New SqlConnection(constr)
		Using cmd As New SqlCommand("GetCustomersTest")
			cmd.Parameters.AddWithValue("@Action", "INBOUND_ACTIVE_HTML_TABLE")

			Using sda As New SqlDataAdapter(cmd)
				cmd.Connection = con
				cmd.CommandType = CommandType.StoredProcedure
				Using dt As New DataTable()
					sda.Fill(dt)
					Return dt
				End Using
			End Using
		End Using
	End Using
End Function

Your procedure should be like below and add these parameter to the code also.

SQL

CREATE PROCEDURE LogTypeDetails
    @Category VARCHAR(50),
    @Status VARCHAR(50)
AS
BEGIN
    SELECT DocNo as CaseID,convert (varchar(10),docdate, 105) as DocDate,Salescode as [HRM ID],Code as [Hotel ID],Name as HotelName, 
    Spokenwith, LogType,Service,Department,Remarks, convert (varchar(10),followupdate, 105) as Followup,Status 
    from CL_LogTypeDetails 
    where category = ''+@Category+'' and status <> ''+@Status+''   
    order by id desc
END