Is there a way to populate a HTML table from a database? (SQL Server, VB.NET, ASP.NET)

Question

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

Answer 1

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.