Multiple recordsets

[madanthrax]

Hi all,

I have a web page displaying various types of uploaded files (details stored in SQL 2000). The page headings and other page details need 2 recordsets, and if I do it the simple (for me) way then I will require 8 recordsets for each of the 8 file types so they display under sub headings as sort of paragraphs (using while/wend).

The file recordsets are all the same but with a variable for each file type.

Is there a pro way to cut down on the recordsets, maybe a function that takes a variable?

_{"Nothing is impossible until proven otherwise"}​

 

[DotNetGnat]

Below is just a pseudo Code... but should give you an idea...

Function GetRecordSet(myvar)
  Select Case myvar
    Case: "variable1"
       GetRecordSet = " Something with variable1"
    Case: "variable2"
       GetRecordSet = " Something with variable2"
   End Select
  return GetRecordSet
End Function

-DNG

 

[madanthrax]

Thanks DNG, this helps.

_{"Nothing is impossible until proven otherwise"}​

 

[Tarwn]

I don't believe you need seperate file recordsets in the first place. You could query for all of the data and display it with headings with just a single recordset. All you need to do is generate your query with an order by on the file type field first, then any additional fields (like the file name, etc). This will, in essence, group together your results by their file type. At that point you just need to keep track of whicvh type your outputting in the output loop and generate a new heading each time it changes. Something like:

'assume you already have a cnneciton called dbconn

Dim rs_files, sql_files
sql_files = "SELECT filename, filetype FROM FileTable ORDER BY filetype, filename"
Set rs_files = dbconn.Execute(sql_files)

Dim cur_type   ' will be used to track type in loop
If Not rs_files.EOF Then rs_files.MoveFirst
Do Until rs_files.EOF
   'this should be true on first loop and on loops when the type has changed
   If rs_files("filetype") <> cur_type Then
      Response.Write "<h3>" & rs_files("filetype") & "</h3>"
      cur_type = rs_files("filetype")
   End If

   Response.Write rs_files("filename") & "<br/>"
   rs_files.MoveNext
Loop

Set rs_files = Nothing

You can also make this more complicated by using a ul or div tag around all of the items:

'assume you already have a cnneciton called dbconn

Dim rs_files, sql_files
sql_files = "SELECT filename, filetype FROM FileTable ORDER BY filetype, filename"
Set rs_files = dbconn.Execute(sql_files)

Dim cur_type   ' will be used to track type in loop
If Not rs_files.EOF Then rs_files.MoveFirst
Do Until rs_files.EOF
   'this should be true on first loop and on loops when the type has changed
   If rs_files("filetype") <> cur_type Then
      Response.Write "<h3>" & rs_files("filetype") & "</h3>"
      cur_type = rs_files("filetype")

      Response.Write "<ul>"
   End If

   Response.Write "<li>" & rs_files("filename") & "</li>"

   rs_files.MoveNext

   'if the recordset is done or current type has changed, finish the last ul
   If rs_files.EOF Then
       Response.Write "</ul>"
   ElseIf rs_files("filetype") <> cur_type Then
       Response.Write "</ul>"
   End If
Loop

Set rs_files = Nothing

-T