Re: Calling SP from ADO and passing parameters
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 09/29/04
- Next message: Stephen Howe: "Broken MSDN Microsoft link"
- Previous message: William \(Bill\) Vaughn: "Re: local ACCESS db connection"
- In reply to: James Armstrong: "Re: Calling SP from ADO and passing parameters"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Sep 2004 11:15:44 -0700
A RETURN parameter can only be an integer. An OUTPUT parameter can be set to
most types (not BLOBs).
I would approach setting these parameters a bit differently. There is no
reason to execute a separate SELECT to set each one since they are all
coming from the same row.
SELECT @CheckPrice =Price, @CheckRepSC = RepSC, @CheckDeskSC = DeskSC,
@CheckBuySell BuySell
FROM dbo.tblOrders WHERE Ticketnum=@MatchID)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"James Armstrong" <armstrongjc@hotmail.com> wrote in message
news:7cf26243.0409290658.769b033f@posting.google.com...
> Hi Scott,
>
> Thanks for your advice. I was finally able to get it going, but only
> by keeping the @Checkreturn defined as an output param. Basically,
> trying your advice yielded the following results:
>
> 1. I moved the @checkreturn param to the local declarations section
> 2. I removed that parameter create command from my vbscript
> 3. When trying to reference the cmd.parameters("@RETURN_VALUE").value
> or the cmd.parameters("@Checkresult").value I get a "item cannot be
> found in collection" error
>
> So, how do I reference the return values when they are not created in
> my vbscript? I tried to add the @checkresult parameter creation code
> after moving that param to the declarations section of the SP and it
> told me I was trying to pass too many parameters.
>
> Posted below is the SP and VB code that worked for me - if anyone can
> tell me how to streamline it, that would be great. I don't want excess
> declarations and stuff I don't need, and I'd like to cut the code down
> as much as possible. I'm just learning this stuff and want to get best
> practices down as early as possible.
>
> Thanks much!!
>
> -Jim
>
> VB:
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
> Dim strMsg As String
> Set cmd = New ADODB.Command
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "spCreditCheck"
> cmd.ActiveConnection = CurrentProject.Connection
> Set prm = cmd.CreateParameter("@MatchID", adInteger, adParamInput, ,
> 763)
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@Price", adNumeric, adParamInput, ,
> 101.25)
> prm.Precision = 18
> prm.NumericScale = 10
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@RepSC", adNumeric, adParamInput, , 0)
> prm.Precision = 18
> prm.NumericScale = 10
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@Rep2SC", adNumeric, adParamInput, , 0)
> prm.Precision = 18
> prm.NumericScale = 10
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@DeskSC", adNumeric, adParamInput, , 0)
> prm.Precision = 18
> prm.NumericScale = 10
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@CheckResult", adNumeric,
> adParamOutput, , Null)
> prm.Precision = 18
> prm.NumericScale = 10
> cmd.Parameters.Append prm
> cmd.Execute , , adExecuteNoRecords
> strMsg = "The check result is " & cmd.Parameters("@checkresult").Value
> MsgBox (strMsg)
>
>
> SP:
>
> CREATE PROCEDURE spCreditCheck
> @MatchID integer, @Price numeric(18,10), @RepSC numeric(18,10),
> @Rep2SC numeric(18,10), @DeskSC numeric(18,10),@CheckResult
> numeric(18,10) OUTPUT
> AS
> Declare @CheckPrice numeric(18,10), @CheckRepSC numeric(18,10),
> @CheckRep2SC numeric(18,10), @CheckDeskSC numeric(18,10),
> @CheckBuySell varchar(2), @PriceDiff numeric(18,10), @RateSum
> numeric(18,10)
> 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
- Next message: Stephen Howe: "Broken MSDN Microsoft link"
- Previous message: William \(Bill\) Vaughn: "Re: local ACCESS db connection"
- In reply to: James Armstrong: "Re: Calling SP from ADO and passing parameters"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
