January 13, 2003 at 8:05 am
Good morning,
I have a stored procedure:
CREATE PROCEDURE proc_CallCenterAbacusNewPrspct
@intIntlCntct_id int,
@strTypevarchar(20),
@strMailName varchar(20),
@strAddr1 varchar(50),
@strAddr2 varchar(50),
@strCity varchar(20),
@strState varchar(2),
@strZip varchar(20),
@prspct_id int OUTPUT
AS
SET NOCOUNT ON
INSERT INTO Prspct
(initl_cntct_mrktng_evnt_id,
prspct_type_cd ,
prspct_mail_name,
hsehold_addr1_name,
hsehold_addr2_name,
hsehold_city_name,
hsehold_state_cd,
hsehold_postal_zone_cd)
VALUES
(@intIntlCntct_id,
@strType,
@strMailName,
@strAddr1,
@strAddr2,
@strCity,
@strState,
@strZip)
SELECT @prspct_id = @@IDENTITY
I am executing through ADO from Access 2000:
Dim cnnSQL As ADODB.Connection
Dim rstSQL As ADODB.Recordset
Dim cmdSQL As ADODB.Command
Dim prmSQL As ADODB.Parameter
Dim strcnn As String
Dim strSQL As String
Dim lngPrspct_id As Long
On Error GoTo PROC_ADOERR
'Set ADO connection, recordset set command.
Set cnnSQL = New ADODB.Connection
Set rstSQL = New ADODB.Recordset
Set cmdSQL = New ADODB.Command
'Define the input parameters.
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = intIntlCntct_id
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strMailName
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strAddr1
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strAddr2
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strCity
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strState
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Direction = adParamInput
prmSQL.Type = adVarChar
prmSQL.Size = 20
prmSQL.Value = strZip
cmdSQL.Parameters.Append prmSQL
Set prmSQL = New ADODB.Parameter
prmSQL.Name = "Return_prspct_id"
prmSQL.Direction = adParamOutput
prmSQL.Type = adVarChar
prmSQL.Size = 20
cmdSQL.Parameters.Append prmSQL
'Open the connection and prepare the command to execute procAbcs_hit_CallCenter
cnnSQL.Open ("TM DirMail_test")
cmdSQL.CommandType = adCmdStoredProc
cmdSQL.CommandText = "proc_CallCenterAbacusNewPrspct"
cmdSQL.ActiveConnection = cnnSQL
cmdSQL.Execute
Why do I get the following error (see attachment, but if it does not make it:)?
Error #-2147217900 [Microsoft][ODBC SQL Server Driver][SQL Server]Formal parameter '@strZip' was defined as OUTPUT but actual paramater not declared OUTPUT.
I cannot figure why it thinks @strZip is declared OUTPUT!
Thanks again
jm
January 13, 2003 at 8:47 am
You have 9 stored procedure parameters but only 8 in ADO your parameters collection, I think you are missing a parameter corresponding to @strType.
Regards,
Andy Jones
.
January 13, 2003 at 9:01 am
I think I better have a few more cups of coffee. Thanks Andy, I did not see that.
Jonathan
January 15, 2003 at 7:13 am
is that "output" parameter still need to put in the request statement?
January 15, 2003 at 7:23 am
Yes.
The stored procedure properly outputs with the syntax above when I have the correct number of input parameters. I believe you have to declare "Output" both in the procedure and the calling program.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply