The Old Fashioned Way
The code here shows how we can query a database and write out the columns and rows into a nicely formatted table.
The Code: Below I open a database connection, run my query and loop through the records with GetRows and display the results. To run the examples below as written you'll need to have the Pubs database available to you.
<%@ Language=VBScript %>
<%
Dim sqlStr
sqlStr = "Select lname As LastName, fname As FirstName, emp_id As ID, hire_date As [Hired On] from Employee"
'Open up the database connection
Set dbConn = Server.CreateObject("ADODB.Connection")
With dbConn
.Provider = "SQLOLEDB"
.ConnectionString = "Provider=SQLOLEDB;USER ID=sa;PASSWORD=;INITIAL CATALOG=Pubs;Data Source=(local)"
.Open
End With
Set rsDbConn = dbConn.Execute(sqlStr, adExecuteNoRecords)
If Not rsDbConn.EOF Then
GetrsConn = rsDbConn.GetRows()
End if
Response.Write "<TABLE border=1 Width=""60%"">"
Response.Write "<TR>"
'Columns name loop
For c = 0 to UBound(GetrsConn,1)
Response.Write "<TD><B>" & rsDbConn.Fields(c).Name & "</B></TD>"
Next
Response.Write "</TR>"
'Rows loop
For r = 0 to UBound(GetrsConn,2)
Response.Write "<TR>"
Response.Write "<TD>" & GetrsConn(0,r) & "</TD>"
Response.Write "<TD>" & GetrsConn(1,r) & "</TD>"
Response.Write "<TD>" & GetrsConn(2,r) & "</TD>"
Response.Write "<TD>" & GetrsConn(3,r) & "</TD>"
Response.Write "</TR>"
Next
Response.Write "</TABLE>"
'Close and clear our connections
dbConn.Close
Set rsDbConn = Nothing
%>
Again, this is standard to any ASP developer. Now as you'll see, you can do the exact same thing in .NET and explain that after.
Let's Do the Same Thing in .NET
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
Dim grTable As StringBuilder
Dim sqlStr As String
Dim TotalRows, TotalFlds, c, r As Integer
Sub Page_Load(Source As Object, E As EventArgs)
sqlStr = "Select lname As LastName, fname As FirstName, emp_id As ID, hire_date As [Hired On] from Employee"
Dim objConnect As New SqlConnection ("server=(local);uid=sa;pwd=;database=Pubs;")
Dim objDataAdapter As New SqlDataAdapter (sqlStr.ToString(), objConnect)
Dim objDS As New DataSet()
'Create and Fill Info Datatable with results
objDataAdapter.Fill (objDS, "Info")
'Close and clear our connections
objConnect.Close : objConnect = Nothing
'Declare name variable as a DataTable
Dim GetRows As DataTable = objDS.Tables ("Info")
'Get Table Info
TotalRows = GetRows.Rows.Count
TotalFlds = GetRows.Columns.Count
grTable = New StringBuilder ()
grTable.Append ("<TABLE border=1 Width=60%>")
grTable.Append ("<TR>")
'Loop through data
'Loop through the Columns Fields
For c = 0 To TotalFlds-1
grTable.Append ("<TD><B>" & GetRows.Columns(c).ToString() & "</B></TD>")
Next
grTable.Append ("</TR>")
'First header row is now closed and we loop through our database rows
For r = 0 To TotalRows-1
grTable.Append ("<TR>")
grTable.Append ("<TD>" & GetRows.Rows(r)(0).ToString() & "</TD>")
grTable.Append ("<TD>" & GetRows.Rows(r)(1).ToString() & "</TD>")
grTable.Append ("<TD>" & GetRows.Rows(r)(2).ToString() & "</TD>")
grTable.Append ("<TD>" & FormatDateTime(GetRows.Rows(r)(3).ToString(),2) & "</TD>")
grTable.Append ("</TR>")
Next
grTable.Append ("</TABLE>")
objDataAdapter = Nothing : objDS = Nothing
End Sub
</script>
<html>
<body>
<%=grTable.ToString()%>
</body>
</html>
Comments