SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Malaysia
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cyclic looping thru a database

    hey guys,

    i got a SQL query here that pulls columns out from my SQLServer2K d/b and spits it out. My code fragment is as follows:

    SQLCode = "SELECT * FROM BKMTABLE"

    Set objRS = objConn.Execute(SQLCode)
    Do While Not objRS.EOF
    response.write objRS("BKM_ID") & "<br>"
    response.write objRS("BKM_TITLE") & "<br>"
    objRS.MoveNext
    Loop
    objRS.Close

    Problem is, I don't want to keep on repeating response.writes for all the column headers. Is there a way to do automatic cyclic looping thru a whole table and just spit all data out including column headers?

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Do you mean create a generic set of code that will allow you to show a whole table regardless of the table layout? Sure it can be done, but it will be "expensive" in terms of speed and server load.

    Your code would be something like this:
    Code:
    SQLCode = "SELECT * FROM BKMTABLE"
    Set objRS = objConn.Execute(SQLCode)
    if objRs.EOF then
    	' Do Nothing.  No records found...
    else
    	' Display the column headers...
    	Response.Write "<table width=""100%"">" & vbNewLine & _
    			"	<tr>" & vbNewLine
    	for each fld in rs.fields
    		Response.Write "<td>" & fld.name & </td>" & vbNewLine
    	Next
    	Response.Write "	</tr>" & vbNewLine
    	' Now display all the record information...
    	Do While Not objRS.EOF
    		Response.Write "	<tr>" & vbNewLine
    		for each fld in rs.fields
    			Response.Write "<td>" & rs(fld.name) & </td>" & vbNewLine
    		Next
    		Response.Write "	</tr>" & vbNewLine		objRS.MoveNext
    	Loop
    	Response.Write "</table>" & vbNewLine
    End If
    objRS.Close	:	set objRs = nothing
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Malaysia
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool... thanks a lot Dave! I'll tweak this code a lil to improve the load on server resources... but if it gets really bogged then i guess i can always write out each col. header name

  4. #4
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can't you do that with getrows as well? then you're not bogging down the server and you can print out the headers.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    That's true, you could use getrows once you've looped through the fields once. I should have thought of that.

    Guess this code would have been better:
    Code:
    Dim reccount, fieldarray, fieldcount, FieldLoop, RowLoop
    SQLCode = "SELECT * FROM BKMTABLE"
    Set objRS = objConn.Execute(SQLCode)
    if objRs.EOF then
    	' Do Nothing.  No records found...
    	reccount = 0
    else
    	fieldarray = objrs.GetRows(-1)
    	fieldcount = objrs.Fields.Count
    	reccount = UBound(fieldarray,2)
    end if
    
    if reccount > 0 then
    	' Display the column headers...
    	Response.Write "<table width=""100%"">" & vbNewLine & _
    			"	<tr>" & vbNewLine
    	for each fld in objrs.fields
    		Response.Write "<td>" & fld.name & </td>" & vbNewLine
    	Next
    	Response.Write "	</tr>" & vbNewLine
    	objRS.Close	:	set objRs = nothing
    
    	' Now display all the record information...
    	For RowLoop = 0 to reccount					Response.Write "	<tr>" & vbNewLine
    		for FieldLoop = 0 to (FieldCount - 1)
    			Response.Write "<td>" & fieldarray(FieldLoop, RowLoop) & </td>" & vbNewLine
    		Next
    		Response.Write "	</tr>" & vbNewLine	Next
    	Response.Write "</table>" & vbNewLine
    End If
    Last edited by DaveMaxwell; Sep 12, 2002 at 09:37.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you should probably close the recordset after the first "end if"...

    [VBS]
    Dim reccount, fieldarray, fieldcount, FieldLoop, RowLoop
    SQLCode = "SELECT * FROM BKMTABLE"
    Set objRS = objConn.Execute(SQLCode)
    if objRs.EOF then
    ' Do Nothing. No records found...
    reccount = 0
    else
    fieldarray = objrs.GetRows(-1)
    fieldcount = objrs.Fields.Count
    reccount = UBound(fieldarray,2)
    end if

    'close the recordset
    objRs.close
    set objRs = Nothing


    if reccount > 0 then
    ' Display the column headers...
    Response.Write "<table width=""100%"">" & vbNewLine & _
    " <tr>" & vbNewLine
    for each fld in rs.fields
    Response.Write "<td>" & fld.name & </td>" & vbNewLine
    Next
    Response.Write " </tr>" & vbNewLine
    objRS.Close : set objRs = nothing

    ' Now display all the record information...
    For RowLoop = 0 to reccount Response.Write " <tr>" & vbNewLine
    for FieldLoop = 0 to (FieldCount - 1)
    Response.Write "<td>" & fieldarray(FieldLoop, RowLoop) & </td>" & vbNewLine
    Next
    Response.Write " </tr>" & vbNewLine Next
    Response.Write "</table>" & vbNewLine
    End If


    [/VBS]

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I was using the objrs.Fields to get the headers, which is why I didn't close it until after I got the field names....

    You'll get an error because you closed it twice (never took mine out...)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops, didn't see it there. won't getrows grab the header info in the same array? or do you have to do it twice like you've done? i'm still learning getrows...

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    I don't think it will return the "header" information (or at least I've never seen it used in that way). It assumes you already know which fields you've selected and in what order.

    I can't find any info in MSDN about it either. I'll check some other sources to see what I can find...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    getrows and headers

    i may be misunderstanding him, but he does it on the third example.

  11. #11
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by bbolte
    getrows and headers

    i may be misunderstanding him, but he does it on the third example.
    Ah, but if you look at that third example, he knows ahead of time what his data structure returned is. He knows that column 3 is the email and column4 is the date. There is no where that he is utilizing any method for getting field names from the getrow result array.

    Under the example that started this thread, infinitium was looking for a "generic" type of data display facility in which you may or may not know the data structure. I don't think getrows will provide that (though I'm sure m@rco will prove me wrong if I am - which he has done numerous times )
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  12. #12
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your right. i had seen an example where someone was printing headers with getrows. they have 2 arrays, here it is.

  13. #13
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    That's basically doing the same thing I did, just putting it in an array first then displaying it, where I displayed it on the fly......
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  14. #14
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DaveMaxwell
    I don't think getrows will provide that (though I'm sure m@rco will prove me wrong if I am - which he has done numerous times )
    Dave, you're actually quite right (for once ) - GetRows will only return the data itself, not any recordset metadata (such as field names). These must be retrieved directly from the RecordSet object itself.

    Incidentally, a couple of years ago I wrote a lightweight VBScript class which combines some of the most useful features of the RecordSet object with that of GetRows, to provide fast, easy RecordSet-style data/record retrieval and manipulation (read-only though as it stands), with a fraction of the overhead of the real RecordSet object.

    If I get around to it, I will touch it up (to remove dependencies on other parts of my code library), and post it here at SitePoint for all to use. Remind me if it doesn't appear in the next month or so!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •