Re: Calling SP from ADO and passing parameters

From: ScottShell (ScottRShell_at_comcast.net)
Date: 09/29/04 

Date: Tue, 28 Sep 2004 21:06:45 -0700

1. You don't need to declare a parameter as OUTPUT if you only return one
value in the stored procedure. Just declare @CheckResult as a local
variable and not as one of the arguements.

2. Check the ADO documentation to see if the Parameters collection is 0 or
1 based.

3. You can still use the "Execute spname var1, var2, etc..." way that you
are used to; the Execute method off the Connection object (I believe)
returns a read only recordset.

"James Armstrong" <armstrongjc@hotmail.com> wrote in message
news:7cf26243.0409281632.517cf8b7@posting.google.com...
> 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