This topic is locked

Calling MSSQL Stored Procedure

6/9/2008 3:47:35 PM
ASPRunnerPro General questions
T
tgunther author

I have searched the forums for a way to call a stored procedure and I have not found one that has helped me to accomplish my task. I want to be able to execute a stored procedure after a a record is added using some of the field values from the new record.

Here is the stored procedure:

CREATE PROCEDURE [dbo].[sp_FamilySerialUpdate]

@item varchar(100),

@WO varchar(50),

@CB varchar(50),

@CD varchar(50),

@QTY int,

@serial varchar(50)

AS

DECLARE @counter int;

SET @counter = 0;

IF @QTY = 1

UPDATE FamilySerialIssued

SET Serial = Serial + 1

WHERE

[dbo].[FamilySerialIssued].[Item] = @item

ELSE

WHILE (@counter < @QTY)

BEGIN

SET @counter = @counter + 1

UPDATE FamilySerialIssued

SET Serial = Serial + 1

WHERE

[dbo].[FamilySerialIssued].[Item] = @item

SET @serial = (SELECT Serial FROM dbo.[FamilySerialIssued] WHERE Item = @item)

INSERT into dbo.[FamilySerial Number] ([Item], [WO Number], [Serial Number], [Created by], [Created Date], [QTY])

VALUES (@item, @WO, @serial, @CB, @CD, 0)

END

GO



Essentially I want to run the MSSQL command:

exec sp_FamilySerialUpdate '@item', '@WO' etc.



Replacing the parameters with values from the added recorded.

I am running ASPRunner 5.2 and I assume I can added this to the event page, I am just not sure how to implement this or can this even be done through ASP runner? Many thanks in advance!

Sergey Kornilov admin 6/9/2008

Something like this will work:

dbConnection.Execute "exec sp_FamilySerialUpdate '12345', '67890'"
T
tgunther author 6/10/2008

Something like this will work:


dbConnection.Execute "exec sp_FamilySerialUpdate '12345', '67890'"



Thanks for the help, that worked perfectly. I just have one more question. How can I pass the values from the record that was just added into the parameters for the stored procedure? So instead of hard coding 12345 that would be the value of field1 from the record that was just added.

Thanks in advance!

J
Jane 6/18/2008

Hi,
all entered values are stored in the dict collection:

dict("FieldName")



where FieldName si your actual field name.