2nd recordset wipes out first 2

[eyeswideclosed]

VB.NET/ASP.NET
When I create 2 ADO recordsets, even with their own connections, the 2nd one interferes with the 1st. I'm trying to do a nested loop, the outer loop with the 1st and the inner loop with the 2nd, but as soon as the 2nd one is created the data in the 1st one is gone. I have been forced to use an array for the outer loop created from the 1st, then create the 2nd, this works ok but I want to know why I can't create 2 recordsets on the same page and have them act independently.

 

[ThatRickGuy]

I think we're going to need some code to follow you on this one.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[eyeswideclosed]

pseudo code, but you get the gist:

dim cn1 as adodb.connection
dim cn2 as adodb.connection
cn1.open("connectionstring")
cn2.open("connectionstring")
dim rs1 as adodb.recordset
dim rs2 as adodb.recordset
dim sSql1 as string = "SELECT * FROM TABLE1"
dim sSql2 as string = "SELECT * FROM TABLE2"
rs1.activeconnection = cn1
rs2.activeconnection = cn2
rs1.open(sSql1)
'everything's fine up till now
rs2.open(sSql2)
'rs2 is now populated, but rs1 is now empty

what's the deal? why is rs2 stepping on rs1?

 

[ThatRickGuy]

Right off the bat, I would highly recommend scraping the legacy ADO code and jumping to ADO.Net. It takes a bit to get used to, but it will make your life significantly easier.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[eyeswideclosed]

We want to eventually, but it's a holdover from a massive classic asp system, literally thousands of ado loops etc everywhere, when we upgraded to .net management would not allow us the extra 6 months or so it would have taken to go to datasets. But is this the problem here? .Net requires ado.net when using more than one recordset(dataset) in the same scope?

 

[ThatRickGuy]

I actually don't know. That code in theory looks good. You may want to check on the VB6 and ASP forums to see if there is anything specific about your environment that could be throwing the first recordset out.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[macleod1021]

the problem is that you're not calling the New statement. That will create a second object. Once that's created they won't overwrite each other. I used to do it all the time in vb 6.

dim cn as adodb.connection
cn.connectionstring = "DSN=MyDSN"
cn.open

Dim rs as adodb.recordset
Dim rs1 as abodb.recordset

Set rs = new adodb.recordset
str = "Select * from Table1"
rs.open str,cn,,,adcmdtext


Set rs1 = new adodb.recordset
str = "Select * from Table2"
rs1.open str,cn,,,adcmdtext


This will open 2 recordsets in seperate objects that will not bleed over.

 

[ThatRickGuy]

LOL, good catch Macleod! I'm suprised that he didn't get a null reference exception in there.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[macleod1021]

Yeah, I was thinking that too. I just figured he had on error resume next set somewhere Either that, or he had a stray thought of resume next and vb6 caught it and added it for the fun of it LOL

 

[eyeswideclosed]

Wow thanks, these recordsets feed off of a base recordset in a referenced database utility dll we wrote that DOES use "New", but in the client pieces (asp.net pages) they are just typed, not instantiated, perhaps that's why they've been working at all, but also may explain this problem. At any rate I think you've put me on the right track, thanks again.

 

[macleod1021]

If you're accessing it through a dll, then it's very possible that the dll isn't creating a new instance when the second request comes through. You could always create a new instance of the dll.

 

[eyeswideclosed]

It's a little baffling, here's my exact scenario:
dbUtil.dll has a class, clsDbUtil, that contains the parent recordset (created using New of course) that feeds all the client's local recordsets.
Each page references this class.
Function openRecordset in the dll accepts the SQL and returns a recordset.
The local recordsets (no New just As) are populated thusly:
rs = myDbUtil.openRecordset(sSql)

This works perfectly and has for many years as long as you only use 1 recordset at a time, the problem only arises in the few cases where I need 2 recordsets open at the same time, I have the array workaround but I sure would like to fix this. Anyway this is all moot now I guess, since you guys have made me aware of the "New" problem.
Many thanks again, I have been looking for an answer for quite some time, now it seems so simple!

 

[macleod1021]

np...glad we could help.