Re: Tricky Stored Procedure Problem - for SQL Experts only!

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Andy (aon14_at_lycos.co.uk)
Date: 03/19/04 

Date: 19 Mar 2004 06:05:57 -0800

jhoge123@yahoo.com (John Hoge) wrote in message news:<d0f2d316.0403130607.3d826b5a@posting.google.com>...
> In a web application I need to call a stored procedure that lists
> items in a shopping cart. The Sproc is pretty complicated as it needs
> to do compatibility checks between the items in the cart, and makes
> use of temporary tables. It has one parameter, the order ID.
>
> The problem lies when the recorset needs to be opened in any
> non-forward only mode, such as static, dynamic or keyset. This is
> needed because the checkout page contains script to compose an email,
> so the recordset needs to be looped through twice.
>
> It seems the recordset.moveFirst command will cause this error
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e18'
> Rowset position cannot be restarted.
>
> in any situation where a stored procecure including a temporary table
> is called, and a parameter is passed. Take a look at the Sproc below:
>
> create proc usp_test(@id int) as
> select @@version as version into #test
> select * from #test
>
> Pretty simple. It makes a temp table and spits it out. When called by
> the vbscript code below, it produces the above error. I've tried this
> both on SQL7 and SQL2000.
>
> set ordItemsRS = server.createObject("ADODB.RECORDSET")
> with cmdorderItems
> .activeConnection = "DSN=SeaEagle"
> .commandType = adCmdStoredProc
> .Commandtext = "usp_test"
> .Parameters.Append .CreateParameter("@ord_id",adInteger,adParamInput,,25075)
> end with
>
> ordItemsRS.open cmdOrderItems, ,adOpenStatic ,adLockReadOnly
>
> do while not ordItemsRS.eof
> response.write ordItemsRS("version") & "<br>"
> ordItemsRS.moveNext
> loop
>
> ordItemsRS.moveFIrst

Can you moveprevious?

Relevant Pages

  • Sproc with temp tables and input parameters causing trouble with dynamic recordsets - a tricky one!
    ... In a web application I need to call a stored procedure that lists ... items in a shopping cart. ... The Sproc is pretty complicated as it needs ... ordItemsRS.open cmdOrderItems,,adOpenStatic,adLockReadOnly ...
    (microsoft.public.sqlserver.programming)
  • Tricky Stored Procedure Problem - for SQL Experts only!
    ... In a web application I need to call a stored procedure that lists ... items in a shopping cart. ... The Sproc is pretty complicated as it needs ... ordItemsRS.open cmdOrderItems,,adOpenStatic,adLockReadOnly ...
    (microsoft.public.sqlserver)
  • How to refresh XSD method
    ... the Stored Procedure it uses, but no matter what I do, it only lists the ... before the stored procedure was updated. ... new fields returned through the SProc? ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Retrieving SPROC return value using SPT
    ... You cannot use the parenthesis in calling the stored procedure. ... Your parameter must be declared as OUTPUT inside your stored procedure. ... It still not updated after return from> sproc. ... The "@" seems to be sufficient to tell> SQL>> that it's an output parameter. ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: SqlDataAdapter - can it be (re)used for 2 SELECT statements
    ... dataadapter for 2 different calls to the same sproc, ... the stored procedure parameter ... //create our DataAdapter object and use it to fill the dataset object ...
    (microsoft.public.dotnet.framework.aspnet)