Re: Calling SP from ADO and passing parameters

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

Date: 29 Sep 2004 07:58:03 -0700

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

Relevant Pages

  • Re: Calling SP from ADO and passing parameters
    ... I moved the @checkreturn param to the local declarations section ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@MatchID", adInteger, adParamInput,, ...
    (microsoft.public.vb.database.ado)
  • Re: Calling SP from ADO and passing parameters
    ... I moved the @checkreturn param to the local declarations section ... > Dim prm As ADODB.Parameter ... > Set prm = cmd.CreateParameter("@MatchID", adInteger, adParamInput,, ...
    (microsoft.public.data.ado)
  • Re: Calling SP from ADO and passing parameters
    ... I moved the @checkreturn param to the local declarations section ... > Dim prm As ADODB.Parameter ... > Set prm = cmd.CreateParameter("@MatchID", adInteger, adParamInput,, ...
    (microsoft.public.vb.database.ado)
  • Re: ADODB Recordset Not Loading From Parameter Query
    ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 25) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Dealing to a SQL SP with lots of params
    ... Currently in each SP, each param has its own name. ... prmD.Value = strCountry ... Dim prmE = New SqlParameter("@country", Data.SqlDbType.NVarChar, ... Dim prm as SqlParameter ...
    (microsoft.public.dotnet.languages.vb)