Response.write in multiple place with same recordset

Question

I have two divs

                    <div class="selection mini">
                        <select name="selfromYear">
                            <option selected disabled>From</option>
                            <option>1956</option>
                            <option>1986</option>
                        </select>
                    </div>
                    <div class="selection mini">
                        <select name="selToYear">
                            <option selected disabled>To</option>
                            <option>1956</option>
                            <option>1986</option>
                        </select>
                    </div>

And I want to fill them with same fields

                        <div class="selection mini">
                            <select name="seltoYear">
                                <option selected disabled>To</option>
                                    <%
                                        Set rs =  rs.NextRecordset
                                            do while not rs.EOF 
                                                    Response.Write "<option>" & rs.Fields(0) & "</option>" & vbCrLf
                                                    rs.MoveNext 
                                            loop
                                    %>
                            </select>
                        </div>
                        <div class="selection mini">
                            <select name="selfromYear">
                                <option selected disabled>From</option>
                                    <%
                                            do while not rs.EOF 
                                                    Response.Write "<option>" & rs.Fields(0) & "</option>" & vbCrLf
                                                    rs.MoveNext 
                                            loop
                                    %>
                            </select>
                        </div>

But filling only first.

Answer 1

if its not working by do while loop, you may try using simple for loop

For i as integer=0 to rs.rows.count
Response.Write("<option>"+rs.rows(i).value+"</option>")
Next

Answer 2

Use rs.MoveFirst before the second loop to 'rewind' the recordset.

Answer 3

Personally I would avoid using a record set like this. I would utilise an array instead.

The following function:

const C_NO_DATA = "NO_DATA" 'Used when no data is returned to a consuming routine
const C_ERROR   = "ERROR"   'Used when an error is generated - to be fed to the comsuming routine

'GetDataSet
'   Returns a table of data based on the supplied SQL statement and connection string.
'Parameters:
'   sqlString (string) - The SQL string to be sent.
'   connString (string) - The database connection string.
'Usage:
'   dataSet = GetDataSet(sqlString, connString)
'Description:
'   This function generates a table of information in a 2 dimensional array.
'   The first dimension represents the columns and the second the rows.  If
'   an error occurs while the routine is executing the array and the base
'   index (0,0) is set to C_ERROR, (0,1) to the VBScript error index, and
'   (0,2) to the VBScript error description.
function GetDataSet(sqlString, connString)
    'Initialise...
    dim returnVal, rsData
    on error resume next
        'Define and open the recordset object...
        set rsData = Server.CreateObject("ADODB.RecordSet")
        rsData.Open sqlString, connString, 0, 1, 1
        'Initialise an empty value for the containing array...
        redim returnVal(0,0)
        returnVal(0,0) = C_NO_DATA
        'Deal with any errors...
        if not rsData.EOF and not rsData.BOF then
            'Store the data...
            returnVal = rsData.GetRows()
            select case err.number
                case 3021   'No data returned
                    'Do nothing as the initial value will still exist (C_NO_DATA)
                case 0      'No error
                    'Do nothing as data has been returned
                case else
                    redim returnVal(4,0)
                    returnVal(C_COL_IDENTIFIER,0) = C_ERROR
                    returnVal(C_COL_ERROR_ID,0) = err.number
                    returnVal(C_COL_ERROR_MESSAGE,0) = err.description
                    returnVal(C_COL_SQL,0) = sqlString
                    returnVal(C_COL_CONNECTION,0) = connString
            end select
        end if
        'Tidy up...
        rsData.close
        set rsData = nothing
    on error goto 0
    'Return the array...
    GetDataSet = returnVal
end function

Will return your data to an array. Supply the query string and connection string and let it do the rest. The resultant array can be used whenever. Becareful on the amount of data being returned, of course.