I can't figure out how to open two different recordsets on the same web 

page. I need to use data from 2 different tables in my calculations on one 

web page.  I don't think I can use a JOIN because the 2 tables don't have 

any keys in common.  Here is the code I use to access the database.



Set connection=Server.CreateObject("ADODB.Connection")

connection.Open "DSN=baseball"



userid = Request.Form("userid")

rank = Request.QueryString("rank")

league = Request.QueryString("league")



'SQLmember = "SELECT * FROM members WHERE userid = 'userid'" 

'

'Set objCommand = Server.CreateObject("ADODB.Command")

'	objCommand.ActiveConnection = connection

'	objCommand.CommandText = SQLmembers

'	objCommand.CommandType = adCmdText

'

'	Set rsmembers = Server.CreateObject("ADODB.Recordset")

'	rsmembers.Open objCommand,,adOpenDynamic, adLockOptimistic

'	Set objCommand = Nothing

'

'	teamnum = rsmembers("teamnum")

'	alnl = rsmembers("alnl")

'	cat = rsmembers("cat")

'	salarycap = rsmembers("salarycap")

'	firstb = rsmembers("firstb")

'	secondb = rsmembers("secondb")

'	thirdb = rsmembers("thirdb")

'	short = rsmembers("short")

'	middle = rsmembers("middle")

'	corner = rsmembers("corner")

'	catcher = rsmembers("catcher")

'	outfield = rsmembers("outfield")

'	utility = rsmembers("utility")

'	starter = rsmembers("starter")

'	reliever = rsmembers("reliever")

'	

'	rsmembers.Close

'	Set rsmembers = Nothing



After this, I want to open another recordset called rsbaseball.



SQLbatter = "SELECT * FROM stats520 ORDER BY DV DESC"



Set objCommand = Server.CreateObject("ADODB.Command")

	objCommand.ActiveConnection = connection

	objCommand.CommandText = SQLbatter

	objCommand.CommandType = adCmdText



	Set rsbatter = Server.CreateObject("ADODB.Recordset")

	rsbatter.Open objCommand,,adOpenDynamic, adLockOptimistic

	Set objCommand = Nothing



When I leave the first SQL and recordset commented, everything works 

fine.  When I uncomment it,  I get a blank page.  Please help.



Steve

I dont know what wrong with your code but I know you can have more than 

one recordset open at a time. I dont use command object or DNS. You could 

try my code. Note that I reuse the same recordset and connection object 

and SQL variable, if they dont need to be open at the same time.



<% 

'open connection to database

	DBName = "test.mdb"

	adOpenForwardOnly = 0

	adLockReadOnly = 1

	set CON = Server.CreateObject ("ADODB.Connection") 

	set RS = Server.CreateObject ("ADODB.Recordset") 

	sDatabaseFile=(server.mappath("..\..\Database\"&DBName)) 

	CON.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data 

Source="&sDatabaseFile&"" 'open database

	SQL="SELECT * FROM " & TableName & " "

	RS.open SQL, CON, adOpenForwardOnly, adLockReadOnly

 

'first recordset data

        name = RS("name")



'open second recordset 

	SQL="SELECT * FROM " & TableName & " "

	RS.open SQL, CON, adOpenForwardOnly, adLockReadOnly



'second recordset data

        age= RS("age")

 %>

> I can't figure out how to open two different recordsets on the same web 

> page.