Stored Procedure Return Value

[scotttom]

Hi

I am using a passthrough querry to fire a stored procedure on SQL2000.

CREATE PROCEDURE spMATERIALRECEIPT @MONOPROJECTID int, @RECIEVEDBY varchar(20) AS

INSERT INTO tblMonoMaterialReceipt ( MonoProjectID, RecievedBy, RecievedOn ) SELECT @MONOPROJECTID, @RECIEVEDBY, GetDate();

SELECT NEWID = SCOPE_IDENTITY()
GO

If i look at the passthrough query i get the NEWID returned properly, but what I can't figure out is how to then assign that value to a control.

Should be easy no?

Thanks in advance for any help!

Scotttom

 

[VicRauch]

For a form named "Form1" with a control named "Control1" that you want updated:

Forms!From1!Control1 = NEWID

 

[scotttom]

Hi Vic,

I get an error on NEWID. "Variable not defined."

I can't even seem to get the returned NEWID into a variable. Any ideas?

Thanks.

 

[DatabaseDude]

The stored procedure doesn't seem to have a variable defined for NEWID, or for it to be passed as a return parameter.

Perhaps try this:

CREATE PROCEDURE spMATERIALRECEIPT 
   (
   @MONOPROJECTID int, 
   @RECIEVEDBY varchar(20),
   @NEWID int OUTPUT
   )
AS

INSERT INTO tblMonoMaterialReceipt 
   ( 
   MonoProjectID, 
   RecievedBy, 
   RecievedOn 
   ) 
VALUES
   (
   @MONOPROJECTID, 
   @RECIEVEDBY, 
   GetDate()
   )

SELECT @NEWID = SCOPE_IDENTITY()

GO

Does this match what you intended?