Reordering columns (fields) in a ADO Recordset
- by Sukotto
I have a classic asp webpage written in vbscript that outputs the results from a third-party stored procedure. My user wants the page to display the columns of data in a different order than they come in from the database. Is there an easy and safe way to re-order the columns in an ADO recordset?
I did not write this page and cannot change the SP.
What is the minimum change I can make here to get the job done and not risk screwing up all the other stuff in the page?
The code looks something like
dim Conn, strSQL, RS
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ServerName
Set strSQL = "EXEC storedProc @foo = " & Request("fooParam")
'This stored procedure returns a date column, an arbitrary '
' number of data columns, and two summation columns. We '
' want the two summation columns to move so they appear '
' immediately after the data column '
Set RS = Server.CreateObject("ADODB.RecordSet")
RS.ActiveConnection = Nothing
RS.CursorLocation = adUseClient
RS.CursorType = adOpenStatic
RS.LockType = adLockBatchOptimistic
RS.Open strSQL, Conn, adOpenDynamic, adLockOptimistic
dim A
' ----- '
' Insert some code here to move the columns of the RS around '
' to suit the whim of my user '
' ----- '
' Several blocks of code that iterate over the RS and display it various ways '
RS.MoveFirst
For A = 0 To RS.Fields.Count -1
' do stuff '
Next
...
RS.MoveFirst
For A = 0 To RS.Fields.Count -1
' do more stuff '
Next
RS.Close : Set RS = Nothing
Conn.Close : Set Conn = Nothing
