Reuse the recordset object

eapsokha · February 21st, 2004, 02:32 AM

I want to a recordset object at least twice times; first for students, second for teacher and third for subjects as below:

objRecordset.Open "SELECT * FROM tbl_student", objConn
'list of students
objRecordset.Open "SELECT * FROM tbl_teacher", objConn
'list of teachers
objRecordset.Open "SELECT * FROM tbl_subject", objConn
'list of subjects

I may reuse the recordset of students in here too.

Can I do that? if so, how?

Many thanks

joefawcett · February 21st, 2004, 08:27 AM

That's fine although I think you need to close it in between:

Code:

objRecordset.Open "SELECT * FROM tbl_student", objConn
'list of students
'process data
objRecordset.close()
objRecordset.Open "SELECT * FROM tbl_teacher", objConn
'list of teachers
'process data
objRecordset.close()
objRecordset.Open "SELECT * FROM tbl_subject", objConn
'list of subjects
'process data
objRecordset.close()

--

Joe

eapsokha · February 23rd, 2004, 02:53 AM

How can I use the first one again, student recordset?

Thanks

DaveGerard · February 23rd, 2004, 03:25 AM

How about a different approach to this? Provided that you are passing the RsType to the page.

RsType = request("RsType")

select case RsType
    case "Student"
        MySql = "select * from tbl_student"
    case "Teacher"
        MySql = "select * from tbl_teacher"
    case "Subject"
        MySql = "select * from tbl_subject"
    case else
        MySql = "select * from tbl_student"
end select

set rs = server.createobject("adodb.recordset")
sql = MySql
rs.open sql, objConn
if not rs.eof then
    do while not rs.eof
        response.write rs("Field")
    rs.movenext
    loop
end if
rs.close
set rs = nothing

eapsokha · February 27th, 2004, 12:05 AM

thanks for your guideline.