Multiple recordsets on the same Data Connection??

Discussion in 'ASP General' started by sans_spam@yahoo.com, Feb 2, 2005.

  1. 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!!
     
    , Feb 2, 2005
    #1
    1. Advertising

  2. wrote:
    > 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


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Feb 3, 2005
    #2
    1. Advertising

  3. 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??
     
    , Feb 3, 2005
    #3
  4. wrote:
    > 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 ...

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 3, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bob
    Replies:
    3
    Views:
    483
    =?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=
    Feb 22, 2007
  2. Bob
    Replies:
    0
    Views:
    434
  3. =?Utf-8?B?TWFkaXNvbg==?=

    SqlDataSource and multiple recordsets

    =?Utf-8?B?TWFkaXNvbg==?=, Jul 24, 2007, in forum: ASP .Net
    Replies:
    5
    Views:
    841
    Cowboy \(Gregory A. Beamer\)
    Jul 24, 2007
  4. Darren

    Newbie using multiple recordsets

    Darren, Apr 3, 2004, in forum: ASP General
    Replies:
    4
    Views:
    144
    Bob Barrows
    Apr 3, 2004
  5. Replies:
    21
    Views:
    472
    Daniel Crichton
    Jan 19, 2009
Loading...

Share This Page