Dynamic Array - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Dynamic Array

Author

Topic

savvy95
Starting Member

23 Posts

Posted - 2007-01-03 : 09:04:47

Please excuse me if this is the wrong forum. I hope your combined genius will be able to point me in the right direction.

I have an ASP page that accepts any SQL query. I want to create a table based on the result set so the number of columns can grow/shrink. But I don't know how. Can anyone help me?

My ASP Code shows how I create a table with only 8 fields.


<%
Dim objConn
dim SQLstmt
Dim objRS
Dim SQLqry
Dim UID
Dim RecordArray
Dim I

UID = right(Request.ServerVariables("AUTH_USER"),8)
SQLqry = Request.Form("qry")

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="Trusted_Connection=yes;driver=SQL Server;server=Server;database=db;"
objConn.Open 


If SQLqry = "" then
	Response.Write "Please Enter a SQL Query"
	Else

Set objRS = objConn.Execute(SQLqry)
         Response.Write "<H5 align = ""center"">" & sqlqry & "</H5>"

Response.Write "<Table align = ""center"" border = ""1"">"
For I = 0 To objRS.Fields.Count - 1
  Response.Write "<TD><B>" & objRS(I).Name & "</B></TD>"
Next


	Do while not objRS.EOF 
	  RecordArray = objRS.GetRows(30)

	  For I = 0 To UBound(RecordArray,2)
		Response.Write "<TR>"
		Response.Write "<TD>" & RecordArray(0,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(1,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(2,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(3,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(4,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(5,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(6,I) & "</TD>"
		Response.Write "<TD>" & RecordArray(7,I) & "</TD>"
		Response.Write "</TR>"
	  Next
	Loop
            Response.Write "</TABLE>"
	objRS.close
	objConn.close
	set objRS=nothing
	set objConn=nothing

End if
%>

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-03 : 09:24:52

By TABLE you mean HTML Table element or physical table in the back-end?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 10:53:16

you need two nested loops:


	  For I = 0 To UBound(RecordArray,2)
            Response.Write "<TR>"
            For C = 0 to Ubound(RecordArray,1)
		Response.Write "<TD>" & RecordArray(C,I) & "</TD>"
            Next
            Response.Write "</TR>"
	  Next

- Jeff

savvy95
Starting Member

23 Posts

Posted - 2007-01-03 : 11:34:01

Jeff -- I knew you could help me.....Thank you. It works like a charm

harsh_athalye - I meant an HTML table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources