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
