OUTPUT parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OUTPUT parameter

Author

Topic

jmatt
Starting Member

3 Posts

Posted - 2003-01-13 : 10:05:21

Good morning,

I have a stored procedure:

CREATE PROCEDURE proc_CallCenterAbacusNewPrspct
@intIntlCntct_id int,
@strType varchar(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?

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
jm

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-13 : 10:12:03

Did you try naming all of the other parameters? In your ADO code, the only parameter you named was the output parameter. See if adding the name to all of the other parameters helps.

ADO is finicky about when parameters need to be named, usually you can't mix and match. If you name one, you must name all of them.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-14 : 02:17:30

you've missed out strType

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources