Running an SQL stored procedure with ADO

Discussion in 'ASP General' started by Jeremy, Jul 30, 2003.

  1. Jeremy

    Jeremy Guest

    Hi, I am having a problem running an sql stored procedure with
    ADO/ASP. If I hard code a select statement, the code works, but when
    I try to use a stored procedure it bombs. Here is my code:

    THIS WORKS:

    sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601',
    '20030605'"

    'Note: I already did my connection object above
    set rs=Server.CreateObject("ADODB.Recordset")
    rs.Open sql,conn

    THIS DOES NOT:

    'my stored procedure is LC_Top50_ByCost
    sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"

    set rs=Server.CreateObject("ADODB.Recordset")
    rs.Open sql,conn

    HERE IS THE ERROR:

    Error Type:
    ADODB.Recordset (0x800A0E78)
    Operation is not allowed when the object is closed.
    /MyWeb/T10_Cost.asp, line 202

    Line 202 is the first line that I try to access the data in the
    recordset.

    Any help would be wonderful, thank you

    -Jeremy
     
    Jeremy, Jul 30, 2003
    #1
    1. Advertising

  2. Jeremy

    Ray at Guest

    Have you created and opened "conn" and not closed it prior to executing that
    code?

    BTW, you may want to use:

    Set rs = conn.Execute(sql)

    and drop the CreateObject("adodb.recordset") method.

    Ray at work

    "Jeremy" <> wrote in message
    news:...
    > Hi, I am having a problem running an sql stored procedure with
    > ADO/ASP. If I hard code a select statement, the code works, but when
    > I try to use a stored procedure it bombs. Here is my code:
    >
    > THIS WORKS:
    >
    > sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601',
    > '20030605'"
    >
    > 'Note: I already did my connection object above
    > set rs=Server.CreateObject("ADODB.Recordset")
    > rs.Open sql,conn
    >
    > THIS DOES NOT:
    >
    > 'my stored procedure is LC_Top50_ByCost
    > sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"
    >
    > set rs=Server.CreateObject("ADODB.Recordset")
    > rs.Open sql,conn
    >
    > HERE IS THE ERROR:
    >
    > Error Type:
    > ADODB.Recordset (0x800A0E78)
    > Operation is not allowed when the object is closed.
    > /MyWeb/T10_Cost.asp, line 202
    >
    > Line 202 is the first line that I try to access the data in the
    > recordset.
    >
    > Any help would be wonderful, thank you
    >
    > -Jeremy
     
    Ray at, Jul 30, 2003
    #2
    1. Advertising

  3. Can you show your stored procedure code? Sounds like it would be remedied
    if you added SET NOCOUNT ON to the beginning of the proc code, but to be
    sure, please post it...


    "Jeremy" <> wrote in message
    news:...
    > Hi, I am having a problem running an sql stored procedure with
    > ADO/ASP. If I hard code a select statement, the code works, but when
    > I try to use a stored procedure it bombs. Here is my code:
    >
    > THIS WORKS:
    >
    > sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN '20030601',
    > '20030605'"
    >
    > 'Note: I already did my connection object above
    > set rs=Server.CreateObject("ADODB.Recordset")
    > rs.Open sql,conn
    >
    > THIS DOES NOT:
    >
    > 'my stored procedure is LC_Top50_ByCost
    > sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"
    >
    > set rs=Server.CreateObject("ADODB.Recordset")
    > rs.Open sql,conn
    >
    > HERE IS THE ERROR:
    >
    > Error Type:
    > ADODB.Recordset (0x800A0E78)
    > Operation is not allowed when the object is closed.
    > /MyWeb/T10_Cost.asp, line 202
    >
    > Line 202 is the first line that I try to access the data in the
    > recordset.
    >
    > Any help would be wonderful, thank you
    >
    > -Jeremy
     
    Aaron Bertrand - MVP, Jul 30, 2003
    #3
  4. Once again, what does the STORED PROCEDURE code look like??? Maybe you
    forgot to include that, or don't believe it's relevant?

    And why do you do this:

    > with cmd
    > .CommandType=adCmdText


    And then:

    > with cmd
    > .CommandType = adCmdStoredProc


    Which .commandType did you *really* mean?

    > rs.MoveFirst


    Why are you doing a MoveFirst? Where else do you think the recordset should
    start at, the last row? Thought about testing for .EOF before executing any
    commands? If .EOF returns true, then you can work backward... because you
    know why you got the error...

    <%
    group_charged="DB"
    begin_date="20030101"
    end_date="20030501"

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

    conn.open "Provider=SQLOLEDB.1;" & _
    "Server=myserver;Database=SQMS;" & _
    "UID=generic;PWD=generic"

    sql = "EXEC Top50Cost " & _
    "@LineCode = '" & group_charged & "'," & _
    "@StartDate = '" & begin_date & "'," & _
    "@EndDate = '" & end_date & "'"

    set rs = conn.execute(sql)

    if rs.eof then
    response.write "Run this in QA. If it returns records, "
    response.write " SHOW US THE STORED PROCEDURE."
    else
    response.write rs(0)
    end if

    rs.close: set rs = nothing
    conn.close: set conn = nothing
    %>






    "Jeremy" <> wrote in message
    news:...
    > Hi Guys, I took out all of my code and JUST left the ado stuff, and it
    > still gives the same error (Operation is not allowed when the object
    > is closed) on the rs.MoveFirst method. I changed the code up a bit as
    > well. Below is the complete ado code. I put in a bogus username and
    > password as well as server address because I didn't want to post the
    > real ones here. The parameters I have hard coded into variables and I
    > know that it should return about 45 rows and does not. Any Help?
    > thanks!
    >
    > HERE'S THE CODE:
    > ----------------
    > <%
    > group_charged="DB"
    > begin_date="20030101"
    > end_date="20030501"
    > %>
    >
    >
    > <%
    > set conn=Server.CreateObject("ADODB.Connection")
    > set cmd=Server.CreateObject("ADODB.Command")
    > set rs=Server.CreateObject("ADODB.Recordset")
    >
    > with conn
    > .CursorLocation = 3
    > .ConnectionString = "Provider=SQLOLEDB.1;" & _
    > "Persist Security Info=True;" & _
    > "Initial Catalog=SQMS;" & _
    > "Password=generic;" & _
    > "User ID=generic;" & _
    > "Data Source=myserver;"
    > .open
    > end with
    >
    > with cmd
    > .CommandType=adCmdText
    > .ActiveConnection=conn
    > end with
    >
    > with cmd
    > .CommandType = adCmdStoredProc
    > .CommandTimeout = 180
    > .CommandText = "Top50Cost"
    > .Parameters.Append .CreateParameter("LineCode", adChar, adParamInput,
    > 2, group_charged)
    > .Parameters.Append .CreateParameter("StartDate", adChar,
    > adParamInput, 8, begin_date)
    > .Parameters.Append .CreateParameter("EndDate", adChar, adParamInput,
    > 8, end_date)
    >
    > set rs = .Execute
    >
    > End With
    >
    > rs.MoveFirst
    > %>
     
    Aaron Bertrand - MVP, Jul 31, 2003
    #4
  5. Jeremy

    Bob Barrows Guest

    All stored procedures should have that line in them, except those where you
    WANT to have them waste time and resources sending that message to the
    client, that is. Take the time to correct your stored procedures. It will be
    worth it.

    The only alternative is to use the time-and-resources-consuming
    NextRecordset method to get to the recordset containing the data you
    actually wanted to receive from your procedure.

    Bob Barrows

    Jeremy wrote:
    > Aaron, you are a GENIUS! the set nocount worked, HOWEVER. . .I don't
    > want to have to go and edit all of my stored procedures to accomplish
    > this. Is there a way to "SET NOCOUNT ON" in asp with the setup that I
    > have going here? THANK YOU!
    >
    > -Jeremy
    >
    >
    > "Aaron Bertrand - MVP" <> wrote in message
    > news:<eF$>...
    >> Can you show your stored procedure code? Sounds like it would be
    >> remedied
    >> if you added SET NOCOUNT ON to the beginning of the proc code, but
    >> to be
    >> sure, please post it...
    >>
    >>
    >> "Jeremy" <> wrote in message
    >> news:...
    >>> Hi, I am having a problem running an sql stored procedure with
    >>> ADO/ASP. If I hard code a select statement, the code works, but
    >>> when
    >>> I try to use a stored procedure it bombs. Here is my code:
    >>>
    >>> THIS WORKS:
    >>>
    >>> sql="SELECT PartNumber FROM Scrap WHERE DateOpened BETWEEN
    >>> '20030601', '20030605'"
    >>>
    >>> 'Note: I already did my connection object above
    >>> set rs=Server.CreateObject("ADODB.Recordset")
    >>> rs.Open sql,conn
    >>>
    >>> THIS DOES NOT:
    >>>
    >>> 'my stored procedure is LC_Top50_ByCost
    >>> sql="LC_Top50_ByCost 'DB', '20030601', '20030605'"
    >>>
    >>> set rs=Server.CreateObject("ADODB.Recordset")
    >>> rs.Open sql,conn
    >>>
    >>> HERE IS THE ERROR:
    >>>
    >>> Error Type:
    >>> ADODB.Recordset (0x800A0E78)
    >>> Operation is not allowed when the object is closed.
    >>> /MyWeb/T10_Cost.asp, line 202
    >>>
    >>> Line 202 is the first line that I try to access the data in the
    >>> recordset.
    >>>
    >>> Any help would be wonderful, thank you
    >>>
    >>> -Jeremy
     
    Bob Barrows, Jul 31, 2003
    #5
    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. =?Utf-8?B?SW5kaXJh?=
    Replies:
    0
    Views:
    568
    =?Utf-8?B?SW5kaXJh?=
    Jan 10, 2005
  2. MS
    Replies:
    5
    Views:
    11,354
    Marc Scheuner [MVP ADSI]
    Jan 31, 2005
  3. David Sagenaut

    calling stored procedure in ADO.NET 2.0

    David Sagenaut, Oct 24, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    3,699
    Karl Seguin
    Oct 24, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,313
    Mike P
    Jun 19, 2006
  5. Quinet, Joel
    Replies:
    0
    Views:
    491
    Quinet, Joel
    Aug 8, 2003
Loading...

Share This Page