Stored Procedure Recordset closing!

[Gal]

Hi

I am in the process of trying to convert an Access DB to ADP/SQL Server
2000 - learing as I go. All help gratefully appreciated!!!

I had a VBA routine which added various records to various tables which
I have re-written as a stored procedure, and, after calling the
procedure via a form in the ADP, I want to return the ID from the first
header record written.

The procedure does this when run in Query Analyser, but for some
reason, when called via VBA, (rst.Open SQL, cnn) the next line of code
claims (and I believe it!) that rst is now closed, so it won't give me
my single value! The message I get when trying to run rst.close is:
Operation is not allowed when the object is closed.

I stripped down the procedure to remove as much as necessary to make
the thing work in principle, and it seems that as soon as the proc
contains an INSERT statment, it won't return.

A pruned down (still failing) version of the procedure is as follows:

ALTER PROCEDURE dbo.usp_vItemsAdd
@CurrentUser varchar(50), @PO varchar(30),
@ReqBy varchar(30), @IssBy varchar(30),
@IndItemQty int, @Qty int, --IndItemQty is the number of INDIVIDUAL
ITEMS for STE or Plant
@Type varchar(1), @Prefix varchar(1), @StartNo bigint,
@ItemType bigint, @ItemValue money

AS
declare @SMUID bigint
declare @Counter bigint
--Add Stock Movement Header Record
INSERT INTO dbo.tblStockMovement
(PONumber, ReqBy, IssBy)
SELECT @PO, @ReqBy, @IssBy

--Store new SMUID from above
select @SMUID=@@Identity

SET @Counter = 1
WHILE (@Counter <= @IndItemQty)
begin
--Add Items to tblItems
INSERT INTO tblItems
(ItemCode, ItemType, ItemValue)
SELECT @Type +convert(varchar(10),@StartNo + @Counter - 1),
@ItemType, @ItemValue

SET @Counter = @Counter+1
end
SELECT @SMUID as LastID

 

[Vadim Rapp]

put SET NOCOUNT ON in the beginning of the stored procedure.

Vadim

G> I am in the process of trying to convert an Access DB to ADP/SQL Server
G> 2000 - learing as I go. All help gratefully appreciated!!!

G> I had a VBA routine which added various records to various tables which
G> I have re-written as a stored procedure, and, after calling the
G> procedure via a form in the ADP, I want to return the ID from the first
G> header record written.

G> The procedure does this when run in Query Analyser, but for some
G> reason, when called via VBA, (rst.Open SQL, cnn) the next line of code
G> claims (and I believe it!) that rst is now closed, so it won't give me
G> my single value! The message I get when trying to run rst.close is:
G> Operation is not allowed when the object is closed.

G> I stripped down the procedure to remove as much as necessary to make
G> the thing work in principle, and it seems that as soon as the proc
G> contains an INSERT statment, it won't return.

G> A pruned down (still failing) version of the procedure is as follows:

G> ALTER PROCEDURE dbo.usp_vItemsAdd
G> @CurrentUser varchar(50), @PO varchar(30),
G> @ReqBy varchar(30), @IssBy varchar(30),
G> @IndItemQty int, @Qty int, --IndItemQty is the number of INDIVIDUAL
G> ITEMS for STE or Plant
G> @Type varchar(1), @Prefix varchar(1), @StartNo bigint,
G> @ItemType bigint, @ItemValue money

G> AS
G> declare @SMUID bigint
G> declare @Counter bigint
G> --Add Stock Movement Header Record
G> INSERT INTO dbo.tblStockMovement
G> (PONumber, ReqBy, IssBy)
G> SELECT @PO, @ReqBy, @IssBy

G> --Store new SMUID from above
G> select @SMUID=@@Identity

G> SET @Counter = 1
G> WHILE (@Counter <= @IndItemQty)
G> begin
G> --Add Items to tblItems
G> INSERT INTO tblItems
G> (ItemCode, ItemType, ItemValue)
G> SELECT @Type +convert(varchar(10),@StartNo + @Counter - 1),
G> @ItemType, @ItemValue

G> SET @Counter = @Counter+1
G> end
G> SELECT @SMUID as LastID

Vadim

 

[Gal]

Your a star Vadim - I might get some sleep tonight!

Worked a treat.