I've got a basic HTML table setup in my ASP.NET web portal:
<table ID="MasterTable" width="100%" style="border: 1px solid gray;">
<tr>
<th style="border: 1px solid gray;" width="33%">
Promo Master ID
</th>
<th style="border: 1px solid gray;" width="33%">
Name
</th>
<th style="border: 1px solid gray;" width="33%">
PLU
</th>
</tr>
NOTE: there is a closing table tag there was just some extra code that's not related.
I was wondering how I could fill in the table with data that is saved in a SQL Server database.
cmd = New SqlCommand("SELECT PromoMasterID, Description, PLU FROM PromoMaster")
cmd.Connection = conn
conn.Open()
Is what I have for the call, and the PromoMasterID, Description, PLU are what I want as my columns, how can I use this to populate the table?
Any help would be appreciated, thanks.
UPDATE
Thanks to Albert D. Kallal for all the help, here's the code that worked for me.
ASP.NET
<asp:GridView ID="MasterGrid" runat="server" width="100%">
<Columns>
</Columns>
</asp:GridView>
VB.NET
Protected Sub MasterGridFill()
Dim MasterData As New DataTable
Using cmdSQL As New SqlCommand("SELECT PromoMasterID, Description, PLU FROM PromoMaster ORDER BY PromoMasterID", conn)
conn.Open()
MasterData.Load(cmdSQL.ExecuteReader)
conn.Close()
End Using
MasterGrid.DataSource = MasterData
MasterGrid.DataBind()
End Sub
Well, you can fill out a HTML table, but you have "oh so" many better choices here.
However, as a FYI, then if you tag the control (the table) with a runat="server", then code behind is now free to manipulate that HTML table object.
Hence, say this markup:
<table ID="Hotels" runat="server"
style="width:30%" class="table table-hover table-bordered table-striped" >
<tr>
<th style="border: 1px solid gray;" width="33%">
Hotel Name
</th>
<th style="border: 1px solid gray;" width="33%">
Description
</th>
</tr>
</table>
So, our code behind can thus be:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
LoadTable
End If
End Sub
Sub LoadTable()
Dim strSQL =
"SELECT HotelName, Description FROM tblHotels
ORDER BY HotelName"
Dim dtHotels As DataTable = MyRst(strSQL)
For Each OneRow As DataRow In dtHotels.Rows
Dim NewRow As New HtmlTableRow()
Dim HotelName As New HtmlTableCell()
HotelName.InnerText = OneRow("HotelName").ToString
Dim Description As New HtmlTableCell()
Description.InnerText = OneRow("Description").ToString
NewRow.Cells.Add(HotelName)
NewRow.Cells.Add(Description)
Hotels.Rows.Add(NewRow)
Next
End Sub
And the result is this:
And note the class setting for the table (they are bootstrap classes, and thus the resulting table looks rather nice).
However, you are far better off to use a GridView control, since they are data aware, and the output is going to be a HTML table anyway.
So, in place of the above, using a GridView, then we have this markup:
<asp:GridView ID="GHotels" runat="server"
style="width:30%" CssClass="table table-hover table-striped">
</asp:GridView>
And our code behind now becomes this:
Sub LoadTable()
Dim strSQL =
"SELECT HotelName, Description FROM tblHotelsA
ORDER BY HotelName"
Dim dtHotels As DataTable = MyRst(strSQL)
GHotels.DataSource = dtHotels
GHotels.DataBind()
End Sub
So, note how little code the above requires. And even note how we did not have to write out the column names for above.
The result of the above is thus this:
So, unless you are on a quest for writing all kinds of extra code? I see little reason to use a HTML table as opposed to using a "data aware" control like a GridView, which at the end of the day will output valid HTML table markup anyway.
And to complete this post, I used a handy "helper" routine called MyRst, which I placed in a global code module (since one becomes fast tired of typing connection and data table code over and over). This routine simply returns a data table.
So, MyRst is this:
Public Function MyRst(strSQL As String) As DataTable
' general get any data from SQL
Dim rstData As New DataTable
Using conn As New SqlConnection(My.Settings.TEST4)
Using cmdSQL As New SqlCommand(strSQL, conn)
conn.Open()
rstData.Load(cmdSQL.ExecuteReader)
rstData.TableName = strSQL
End Using
End Using
Return rstData
End Function
Of course, if you going to introduce parameters into the SQL, then I would use MyRstP like this:
Dim strSQL =
"SELECT * FROM tblHotels WHERE City = @City"
Dim cmdSQL As New SqlCommand(strSQL)
cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = "Edmonton"
Dim rstData As DataTable = MyRstP(cmdSQL)
And thus MyRstP is this:
Public Function MyRst(strSQL As String) As DataTable
' general get any data from SQL command object
Dim rstData As New DataTable
Using conn As New SqlConnection(My.Settings.TEST4)
Using cmdSQL As New SqlCommand(strSQL, conn)
conn.Open()
rstData.Load(cmdSQL.ExecuteReader)
rstData.TableName = strSQL
End Using
End Using
Return rstData
End Function
So, note how no looping code is required, and thus note how we have very few lines of markup, and we have very few lines of code. Hence, little if any reason exists to go down that road of all kinds of extra looping code, and all kinds of extra HTML markup.