Multiple recordsets on the same Data Connection??

[Guest]

I want to pull 2 different recordsets using 2 different stored
procedures using the same Data Connection. Below is the code that I
currently have, but I'm getting an 'Object Required' error message. Am
I doing this correctly, or at least headed in the right direction? Can
someone tell me the most efficient way (provide a code example) to pull
2 different recordsets from the same Data Connection?

Set dConn = Server.CreateObject("ADODB.Connection")
dConn.Open Application("Some_ConnectionString")

set rsStats = Server.CreateObject("ADODB.recordset")
rsStats = dConn.Execute("exec sp_ReturnSomeRecords " & _
"@Month='" & StatMonth & "'," & _
"@Year='" & StatYear & "'")


set rsMonthTotal = Server.CreateObject("ADODB.recordset")
rsMonthTotal = dConn.Execute("exec sp_ReturnRecordsFromAnotherTbl " & _
"@Month='" & StatMonth & "'," & _
"@Year='" & StatYear & "'")

Thanks in advance!!

 

[Bob Barrows [MVP]]

I want to pull 2 different recordsets using 2 different stored
procedures using the same Data Connection. Below is the code that I
currently have, but I'm getting an 'Object Required' error message. Am
I doing this correctly, or at least headed in the right direction? Can
someone tell me the most efficient way (provide a code example) to
pull 2 different recordsets from the same Data Connection?

Set dConn = Server.CreateObject("ADODB.Connection")
dConn.Open Application("Some_ConnectionString")

set rsStats = Server.CreateObject("ADODB.recordset")
rsStats = dConn.Execute("exec sp_ReturnSomeRecords " & _
"@Month='" & StatMonth & "'," & _
"@Year='" & StatYear & "'")

This should be
Set rsStats=dConn...

You don't need the first "Set rsStats = ..." line.
Same below

set rsMonthTotal = Server.CreateObject("ADODB.recordset")
rsMonthTotal = dConn.Execute("exec sp_ReturnRecordsFromAnotherTbl " &
_ "@Month='" & StatMonth & "'," & _
"@Year='" & StatYear & "'")

Thanks in advance!!

You should not use te "sp_" prefix for non-system stored procedures. SQL
Server expects procedures with that prefix to be system procedures and
therefore looks first in the Master database for them, only looking in the
default database when tey are not found in Master. It's a slight performance
penalty, and also can be confusing to somebody maintaining your code.

You may want to take a look at this:
http://tinyurl.com/jyy0

Bob Barrows

 

[Guest]

Thanks Bob, but when the page initially loads I get:

Microsoft VBScript runtime error '800a000d'

Type mismatch

I get this for the second recordset (rsMonthTotal). But when I hit the
"January" button to feed the parameters and reload the page it works.
I'm not understanding why I'm getting Type Mismatch on initial load and
then it works when I submit the parameters b/c the code to make the
data call only runs upon 'Submit' having a value...and obviouslyupon
initial load it does not. Thoughts??

 

[Bob Barrows [MVP]]

Thanks Bob, but when the page initially loads I get:

Microsoft VBScript runtime error '800a000d'

Type mismatch

I get this for the second recordset (rsMonthTotal). But when I hit the
"January" button to feed the parameters and reload the page it works.
I'm not understanding why I'm getting Type Mismatch on initial load
and then it works when I submit the parameters b/c the code to make
the data call only runs upon 'Submit' having a value...and
obviouslyupon initial load it does not. Thoughts??

What is the line that produces this error? It sounds as if you need to us an
If statement to only allow that line to run if there is data ...