Calling Stored proc in ActiveX script

Hi!
How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.
Thanks.Dim oConn
Dim oComm

Set oConn = CreateObject("ADODB.Connection")
Set oComm = CreateObject("ADODB.Command")

oConn.ConnectionString = sConnectionString
oConn.Open

oComm.CommandText = "myStoredProc"
oComm.CommandType = 4 ' adStoredProc
oComm.Parameters.Refresh
oComm.Parameters("myParam") = "This"
oComm.ActiveConnection = oConn

oComm.Execute

(alternatively)

Set oRS = oComm.Execute

Written from the top of my head. Your mileage may very. Check syntax carefully. Never run while carrying scissors by the handle. Always keep your barrel pointed downrange.

Regards,

hmscott|||thanks for the advice!

calling a nested procedure

Everytime I execute this script - I am not getting the result I want. I was hoping to take the some parameters call one stored procedufre with in the main procedure. I was hoping to assign a variable to the nested procedure and use that variable in the main procedure, but I am getting a '0' as result - however when I call the a inner procedure by itself if get the correct result. Can someone look at this and tell me where I am wrong? It seems to be a scope problem.

Thanks

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: September 8,2006
-- Description: Inserts a new project
-- =============================================
ALTER PROCEDURE [echo88].[insertProject]
-- Add the parameters for the stored procedure here
@.name varchar(50),
@.desc varchar(MAX),
@.start datetime,
@.finish datetime,
@.memid int,
@.addr1 varchar(32),
@.addr2 varchar(16),
@.city varchar(32),
@.stateid int,
@.zipcode char(5)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.siteid int;
EXEC @.siteid = insertSite @.addr1, @.addr2, @.city, @.stateid, @.zipcode;
-- Insert statements for procedure here
INSERT INTO Project(Title, Description, Start, Finish, SiteID, MemberID ) VALUES ( @.name, @.desc, @.start, @.finish, @.siteid, @.memid );
END

SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Have you defined RETURN values in the 'insertSite' stored procedure? If not, by default it will return 0 for successful execution, so you'll always get 0 for @.siteid. In this case I suggest you use OUTPUT parameter in the stored procedure, for example:

ALTER PROCEDURE [echo88].[insertSite]
-- Add the parameters for the stored procedure here
@.siteid int output
--add other parameters
AS
BEGIN

--add your code

SET @.siteid=1--set the value of the output parameter

END

Then when call it a another procedure:

ALTER PROCEDURE [echo88].[insertProject]
-- Add the parameters for the stored procedure here
@.name varchar(50),
@.desc varchar(MAX),
@.start datetime,
@.finish datetime,
@.memid int,
@.addr1 varchar(32),
@.addr2 varchar(16),
@.city varchar(32),
@.stateid int,
@.zipcode char(5)
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.siteid int;
EXEC insertSite @.addr1, @.addr2, @.city, @.stateid, @.zipcode,@.siteid output;
-- Insert statements for procedure here
INSERT INTO Project(Title, Description, Start, Finish, SiteID, MemberID ) VALUES ( @.name, @.desc, @.start, @.finish, @.siteid, @.memid );
END