Displaying MS SQL Server Column Names and Values via ASP
Sometimes in a ASP (Active Server Pages) page it may be useful to not only retrieve data values from an MS SQL Server database table, but also to retrieve column names from the table. Listed below is an example of how to do this for MS SQL Server databases using ASP.
<%
Response.Buffer = True
On Error Resume Next
Dim host
Dim port
Dim user
Dim password
Dim database
host = "localhost"
port = "1433"
user = "testuser"
password = "testpass"
database = "testdatabase"
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
Dim ds
ds = host & "," & port
Dim connString
connString = "Provider=SQLOLEDB;Data Source=" & ds &
";Network Library=DBMSSOCN;Initial Catalog=" & database & ";User Id="
& user & ";Password=" & password & ";"
conn.Open connString
If conn.Errors.Count > 0 Then
Response.Write "Error: Unable to Connect" & Err.Description
Response.END
End If
Dim query
query = "select * from employee"
Set rs = conn.Execute(query)
If conn.Errors.Count > 0 Then
Response.Write "Error: " & Err.Description
Else
Response.Write "<html><body><table><tr>"
For Each objField in rs.Fields
Response.Write "<td>" & objField.Name & "</td>"
Next
Response.Write "</tr>"
While Not rs.EOF
Response.Write "<tr>"
For Each objField in rs.Fields
Response.Write "<td>" & rs(objField.Name) & "</td>"
Next
rs.MoveNext
Response.Write "</tr>"
Wend
rs.Close
End If
conn.Close
Set conn = Nothing
%>
