Re: store proc not returning value

Hi,

It looks like your code is not correct. You can declare only one parameter
as a return value and this parameter has to be the first one in a collection
of ADO parameters. Last three parameters should be declared as an output
(adParamOutput) parameters, not as a return values

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



"LU" <LU@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E44C97AC-2D84-4740-9502-84CAB7EFA234@xxxxxxxxxxxxxxxx
> Moved my database to a separate machine with IIS6. I changed database
> connection to DSN and now my stored proc will not return values. asp and
> stored proc below. It looks like it is inserting, but not returning
> value.
>
> CREATE PROCEDURE [MH_sp_insert]
>
> (--@UNIT_DEPT_ID_NUM_1 [int],
> @USER_ID_2 [nvarchar](100),
> @USER_FIRST_NAME_3 [nvarchar](100),
> @USER_LAST_NAME_4 [nvarchar](100),
> @USER_MIDDLE_NAME_5 [nvarchar](50),
> @USER_CODE_6 [nvarchar](100),
> @USER_SS_NUM_7 [nvarchar](5),
> @USER_EMAIL_ADDRESS_8 [nvarchar](100),
> @USER_PASSWORD_9 [nvarchar](50),
> @USER_STATUS_10 [nvarchar](50),
> @USER_ACCESS_LEVEL_11 [int],
> @USER_START_DATE_12 [datetime],
> @USER_CREATED_BY_13 [nvarchar](100),
> @USER_ID_NUM_14 [int] OUTPUT,
> @Progress [int] OUTPUT,
> @Errorcode [int] OUTPUT)
>
> AS SET NOCOUNT ON
>
>
> IF EXISTS (SELECT USER_ID FROM MH_USER_TABLE WHERE USER_ID=@USER_ID_2)
> BEGIN
> SET @Progress = 1
> SET @Errorcode = 1 /*user already in system */
> END
> ELSE
> BEGIN
>
> INSERT INTO [MECCA].[dbo].[MH_USER_TABLE]
> (--[UNIT_DEPT_ID_NUM],
> [USER_ID],
> [USER_FIRST_NAME],
> [USER_LAST_NAME],
> [USER_MIDDLE_NAME],
> [USER_CODE],
> [USER_EMAIL_ADDRESS],
> [USER_PASSWORD],
> [USER_STATUS],
> [USER_ACCESS_LEVEL],
> [USER_START_DATE],
> [CREATED_DATE],
> [CREATED_BY],
> [MODIFIED_DATE],
> [MODIFIED_BY],
> [SS_NUM_FULL])
>
> VALUES
> (--@UNIT_DEPT_ID_NUM_1,
> @USER_ID_2,
> @USER_FIRST_NAME_3,
> @USER_LAST_NAME_4,
> @USER_MIDDLE_NAME_5,
> @USER_CODE_6,
> @USER_EMAIL_ADDRESS_8,
> @USER_PASSWORD_9,
> @USER_STATUS_10,
> @USER_ACCESS_LEVEL_11,
> @USER_START_DATE_12,
> getDate(),
> @USER_CREATED_BY_13,
> getDate(),
> @USER_CREATED_BY_13,
> @SS_NUM_FULL)
>
> SET @USER_ID_NUM_14 = @@IDENTITY
> SET @Progress = 0
>
> END
> RETURN
>
> SET NOCOUNT OFF
> GRANT EXECUTE ON [MH_sp_insert] TO public
> GO
> **********asp code**************
>
> Set cmd = Server.CreateObject("ADODB.Command")
> Set cmd.ActiveConnection = dbconn
> cmd.CommandText = "MH_sp_insert_admin_user_table_admin_form_nw1"
>
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Append cmd.CreateParameter("Param2", adVarChar,
> adParamInput,
> 100)
> cmd.Parameters.Append cmd.CreateParameter("Param3", adVarChar,
> adParamInput,
> 100)
> cmd.Parameters.Append cmd.CreateParameter("Param4", adVarChar,
> adParamInput,
> 100)
> cmd.Parameters.Append cmd.CreateParameter("Param5", adVarChar,
> adParamInput,
> 50)
> cmd.Parameters.Append cmd.CreateParameter("Param6", adVarChar,
> adParamInput,
> 100)
> cmd.Parameters.Append cmd.CreateParameter("Param8", adVarChar,
> adParamInput,
> 100)
> cmd.Parameters.Append cmd.CreateParameter("Param9", adVarChar,
> adParamInput,
> 50)
> cmd.Parameters.Append cmd.CreateParameter("Param10", adVarChar,
> adParamInput, 50)
> cmd.Parameters.Append cmd.CreateParameter("Param11", adInteger,
> adParamInput)
> cmd.Parameters.Append cmd.CreateParameter("Param12", adDBTimeStamp,
> adParamInput)
> cmd.Parameters.Append cmd.CreateParameter("Param13", adVarChar,
> adParamInput, 100)
> cmd.Parameters.Append cmd.CreateParameter("Param14", adVarChar,
> adParamInput, 9)
> cmd.Parameters.Append cmd.CreateParameter("Param15",
> adInteger,adParamReturnValue)
> cmd.Parameters.Append cmd.CreateParameter("Param16",
> adInteger,adParamReturnValue)
> cmd.Parameters.Append cmd.CreateParameter("Param17",
> adInteger,adParamReturnValue)
>
> cmd("Param2") = UCase(USER_ID)
> cmd("Param3") = UCase(user_first_name)
> cmd("Param4") = UCase(user_last_name)
> cmd("Param5") = UCase(user_middle_name)
> cmd("Param6") = UCase(USER_ID)
> cmd("Param8") = user_email_address
> cmd("Param9") = user_password
> cmd("Param10") = user_status
> cmd("Param11") = user_access_level
> cmd("Param12") = user_date
> cmd("Param13") = AdminID
> cmd("Param14") = user_ss_num
>
> Set rs = cmd.Execute
> user_id_num = cmd("Param15")
> progress = cmd("Param16")
> Errorcode = cmd("Param17")
>


.

Relevant Pages