Calling SP from ADO and passing parameters

From: James Armstrong (armstrongjc_at_hotmail.com)
Date: 09/29/04 

Date: 28 Sep 2004 17:32:22 -0700

Can someone please give me a hand? I have a stored procedure which I
want to pass some parameters. In other parts of my code, I just set a
string variable to "Execute spname var1, var2, etc..." and then run
the sql cmd. From what I've read so far, it seems that if I want the
SP to return a value I need to call it in a much more complex method?

Anyway, here's the SP:

CREATE PROCEDURE spCreditCheck
@MatchID integer, @Price numeric, @RepSC numeric, @Rep2SC numeric,
@DeskSC numeric, @CheckResult Integer OUTPUT
AS
Declare @CheckPrice numeric, @CheckRepSC numeric, @CheckRep2SC
numeric, @CheckDeskSC numeric, @CheckBuySell varchar(2), @PriceDiff
numeric, @RateSum numeric
SET @CheckPrice = (SELECT Price FROM dbo.tblOrders WHERE
Ticketnum=@MatchID)
SET @CheckRepSC = (SELECT RepSC FROM dbo.tblOrders WHERE
Ticketnum=@MatchID)
SET @CheckRep2SC = (SELECT Rep2SC FROM dbo.tblOrders WHERE
Ticketnum=@MatchID)
SET @CheckDeskSC = (SELECT DeskSC FROM dbo.tblOrders WHERE
Ticketnum=@MatchID)
SET @CheckBuySell = (SELECT BuySell FROM dbo.tblorders WHERE
Ticketnum=@MatchID)
SET @RateSum=(@RepSC+@Rep2SC+@DeskSC+@CheckRepSC+@CheckRep2SC+@CheckDeskSC)
IF @CheckBuysell='BY'
SET @PriceDiff=(@Price-@CheckPrice)
ELSE
IF @CheckBuysell='SL'
SET @PriceDiff=(@CheckPrice-@Price)
IF @RateSum=@PriceDiff
SET @CheckResult=0
ELSE
IF @RateSum<@PriceDiff
SET @CheckResult=(@PriceDiff-@RateSum)
ELSE
IF @RateSum>@PriceDiff
SET @CheckResult=(@RateSum-@PriceDiff)
RETURN @CheckResult
GO

First q: Do I have my vars right? Will the SP indeed output the value
of @CheckResult?

Second: How the heck do I call the damn thing? I've tried:

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim strMsg As String
Dim strConnection As String
cmd.ActiveConnection
Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "dbo.spCreditCheck"
cmd.Parameters.Refresh
cmd.Parameters(1) = Me.MatchID.Value
cmd.Parameters(2) = Me.Price.Value
cmd.Parameters(3) = Me.RepSC.Value
cmd.Parameters(4) = Me.Rep2SC.Value
cmd.Parameters(5) = Me.DeskSC.Value
cmd.Parameters(6) = 0
cmd.Execute

But I get "item not found in collection" errors. I'm going crazy and
it's getting late and I've been staring at this screen for hours
working on this thing (not just on this prob thankfully)

If someone could lend me a helping hand I'd be very grateful - I will
go home and study my books another night, but right now I just want to
get this done...

Thanks in advance!

Relevant Pages

  • Calling SP from ADO and passing parameters
    ... I have a stored procedure which I ... @DeskSC numeric, @CheckResult Integer OUTPUT ... Dim cmd As ADODB.Command ... Dim prm As ADODB.Parameter ...
    (microsoft.public.vb.database.ado)
  • Re: Calling SP from ADO and passing parameters
    ... You don't need to declare a parameter as OUTPUT if you only return one ... Just declare @CheckResult as a local ... I have a stored procedure which I ... > Dim prm As ADODB.Parameter ...
    (microsoft.public.data.ado)
  • Re: Calling SP from ADO and passing parameters
    ... You don't need to declare a parameter as OUTPUT if you only return one ... Just declare @CheckResult as a local ... I have a stored procedure which I ... > Dim prm As ADODB.Parameter ...
    (microsoft.public.vb.database.ado)
  • Re: Doug can you reply regarding advapi32.dll
    ... create a stored procedure in SQL Server ... ... Dim cn As ADODB.Connection ... Dim cmd As ADODB.Command ...
    (microsoft.public.access.formscoding)
  • String data - right truncation error with numeric fields!!!
    ... I have a stored procedure I wrote: ... @DeskSC numeric, @CheckResult numeric OUTPUT ... SET @CheckPrice = (SELECT Price FROM dbo.tblOrders WHERE ... will be decimals. ...
    (microsoft.public.sqlserver.odbc)