Re: Too many parameters in sp

David,

I believe that the RETURN_VALUE needs to use adParamReturnValue instead of adParamOutput.

RLF


"David C" <dlchase@xxxxxxxxxxxxxxx> wrote in message news:ePr3faKMKHA.4004@xxxxxxxxxxxxxxxxxxxxxxx
I am executing a stored procedure in VB that returns a new identity value. I am getting an error about too many parameters and am trying to figure out how to fix it. Below is part of the VB code and below that is the stored procedure. Can someone help with this? Thank you.

David

cnn.Open strSQLCon
bolcnOpen = True

cmd.ActiveConnection = cnn
cmd.CommandText = "ms_insRepairOrderWarranty"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@RepairOrderID", _
adInteger, adParamInput, , Me.fsubEstAndROsummary.Form!RepairOrderID)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@RETURN_VALUE", _
adInteger, adParamOutput)
cmd.Parameters.Append prm

cmd.Execute

lngNewRecID = prm.Value


ALTER PROCEDURE [dbo].[ms_insRepairOrderWarranty]
(@RepairOrderID [int])

AS INSERT INTO dbo.RepairOrder
( [EstimateID],
[RepairOrderID],
[EstimateDate],
[RODate],
[EstimateWriter],
[CustomerID],
[VehicleID],
[PayorIs],
[LicensePlate] )

SELECT
(SELECT MAX(EstimateID)+1 FROM RepairOrder),
(SELECT MAX(RepairOrderID)+1 FROM RepairOrder),
CONVERT(char(8), GETDATE(), 112),
CONVERT(char(8), GETDATE(), 112),
EstimateWriter,
CustomerID,
VehicleID,
N'W',
LicensePlate
FROM dbo.RepairOrder
WHERE RepairOrderID = @RepairOrderID

RETURN SCOPE_IDENTITY()



.

Relevant Pages

  • Re: Too many parameters in sp
    ... cmd.Parameters.Append prm ... adInteger, adParamOutput) ... (SELECT MAX(RepairOrderID)+1 FROM RepairOrder), ... CONVERT, GETDATE(), 112), ...
    (microsoft.public.sqlserver.programming)
  • Too many parameters in sp
    ... I am executing a stored procedure in VB that returns a new identity value. ... cmd.Parameters.Append prm ... (SELECT MAX(RepairOrderID)+1 FROM RepairOrder), ... CONVERT, GETDATE(), 112), ...
    (microsoft.public.sqlserver.programming)