Procedure ''sp_AddressBook'' expects parameter ''@User_ID'', which was not supplied.

Question

Procedure ''sp_AddressBook'' expects parameter ''@User_ID'', which was not supplied.

Westerfield Akinyele

SSC Journeyman

Points: 85
More actions
January 14, 2005 at 1:53 am

#163420

Hi, I'm new to stored procedures and I have managed to write one but when I call it from my ASP page, I get the error "Procedure 'sp_AddressBook' expects parameter '@User_ID', which was not supplied."
Can anyone point out my blunder?:
CREATE PROCEDURE sp_AddressBook

@User_UserName varchar(20),
@User_Password varchar(20),
@User_Title varchar(5),
@User_FirstName varchar(20),
@User_LastName varchar(20),
@User_EmailAddress varchar(50),
@User_Telephone varchar(20),
@User_Mobile varchar(20),
@User_EmailShot int,
@User_Address1 varchar(30),
@USer_Address2 varchar(30),
@User_Address3 varchar(30),
@User_Area varchar(20),
@User_State nchar(2),
@User_Country varchar(5),
@User_YOB nchar(4),
@User_ID int OUTPUT

AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT User_ID from tbl_ibizsearchUsers WHERE User_UserName=@User_UserName)
BEGIN
  INSERT INTO tbl_ibizsearchUsers(User_UserName,User_Password, User_Title, User_FirstName, User_LastName,
   User_EmailAddress, User_Telephone, User_Mobile, User_EmailShot, User_Address1, USer_Address2, USer_Address3,
   User_Area, User_State, User_Country, User_YOB, User_RegistrationDate)
  VALUES(@User_UserName, @User_Password, @User_Title, @User_FirstName, @User_LastName, @User_EmailAddress,
   @User_Telephone, @User_Mobile, @User_EmailShot, @User_Address1, @USer_Address2, @USer_Address3, @User_Area,
   @User_State, @User_Country, @User_YOB, GetDate())
  SELECT @User_ID = @@IDENTITY
END
ELSE
SELECT @User_ID=-1
RETURN
GO

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

vadba SSChampion Points: 11133 More actions · Answer 1

I assume you've successfully compiled the SP and tested it within Query Analyzer. The problem is most likely in the call from your ASP page. Are you sure you've included a value each parameter? Can you post the ASP code that calls the SP?

Westerfield Akinyele SSC Journeyman Points: 85 More actions · Answer 2

Hi, Thank you for your reply. My initial error was caused by my code accessing an outdated asp page. I have rectified that now but I am now getting a new error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/mybizsearch/LIVE/posted.asp, line 94

THe stored procedure is the same and the asp code is included below:

Dim User_UserName 'holds the user's username

Dim User_Password 'holds the user's password

Dim User_Title 'holds user's title i.e Mr. etc

Dim User_FirstName 'holds user's first name

Dim User_LastName 'holds user's last name

Dim User_EmailAddress 'holds user's email address

Dim User_Telephone 'holds user's telephone number i.e land or fixed wireless

Dim User_Mobile 'holds user's mobile number

Dim User_EmailShot 'holds user's emailshot option

Dim User_Address1 'holds user's first of address

Dim User_Address2 'holds user's second of address

Dim User_Address3 'holds user's third of address

Dim User_Area 'holds user's address area

Dim User_State 'holds user's address state

Dim User_Country 'holds user's address country

Dim User_YOB 'holds user's year of birth

'Dim User_RegistrationDate 'holds user's registration date

'getting form input from

User_UserName = request.Form("txtusername")

User_Password = request.Form("txtpassword")

User_Title = request.Form("selTile")

User_FirstName = request.Form("txtforename")

User_LastName = request.Form("txtsurname")

User_EmailAddress = request.Form("txtemail")

User_Telephone = request.Form("txtphone")

User_Mobile = request.Form("txtmobile")

if request.Form("rademailopt") = "yes" then

User_EmailShot = 1

else

User_EmailShot = 0

end if

User_Address1 = request.Form("txtaddress1")

User_Address2 = request.Form("txtaddress2")

User_Address3 = request.Form("txtaddress3")

User_Area = request.Form("txtarea")

User_State = request.Form("selState")

User_Country = request.Form("selcountry")

User_YOB = request.Form("txtyob")

'User_RegistrationDate = date()

Dim objCmd, objRtn 'objConn

Set objCmd = Server.CreateObject("ADODB.Command")

objCmd.ActiveConnection = objConn

objCmd.CommandText = "sp_AddressBook"

objCmd.CommandType = adCmdStoredProc

'objCmd.ActiveConnection = objConn

objCmd.Parameters.append objCmd.CreateParameter("User_UserName", adVarChar, adParamInput,20,User_UserName)

objCmd.Parameters.append objCmd.CreateParameter("User_Password", adVarChar, adParamInput,20,User_Password)

objCmd.Parameters.append objCmd.CreateParameter("User_Title", adVarChar, adParamInput,5,User_Title)

objCmd.Parameters.append objCmd.CreateParameter("User_FirstName", adVarChar, adParamInput,20,User_FirstName)

objCmd.Parameters.append objCmd.CreateParameter("User_LastName", adVarChar, adParamInput,20,User_LastName)

objCmd.Parameters.append objCmd.CreateParameter("User_EmailAddress", adVarChar, adParamInput,50,User_EmailAddress)

objCmd.Parameters.append objCmd.CreateParameter("User_Telephone", adVarChar, adParamInput,20,User_Telephone)

objCmd.Parameters.append objCmd.CreateParameter("User_Mobile", adVarChar, adParamInput,20,User_Mobile)

objCmd.Parameters.append objCmd.CreateParameter("User_EmailShot", adInteger, adParamInput,4,User_EmailShot)

objCmd.Parameters.append objCmd.CreateParameter("User_Address1", adVarChar, adParamInput,30,User_Address1)

objCmd.Parameters.append objCmd.CreateParameter("User_Address2", adVarChar, adParamInput,30,User_Address2)

objCmd.Parameters.append objCmd.CreateParameter("User_Address3", adVarChar, adParamInput,30,User_Address3)

objCmd.Parameters.append objCmd.CreateParameter("User_Area", adVarChar, adParamInput,20,User_Area)

objCmd.Parameters.append objCmd.CreateParameter("User_State", adNumeric, adParamInput,2,User_State)

objCmd.Parameters.append objCmd.CreateParameter("User_Country", adVarChar, adParamInput,5,User_Country)

objCmd.Parameters.append objCmd.CreateParameter("User_YOB", adNumeric, adParamInput,4,User_YOB)

Set objRtn = objCmd.CreateParameter("User_ID", adInteger, adParamOutput,4)

objCmd.Parameters.append objRtn

objCmd.execute '----------- this is line 94

if objRtn.value=-1 then

'rediret user to error page

Response.Redirect ("index.asp?ref=register&resp=uname_exist")

else

'rediret user to acknowledgment page

Response.Redirect ("index.asp?ref=register&resp=ok")

end if

vadba SSChampion Points: 11133 More actions · Answer 3

I see two data type mismatches involving the @User_State and @User_YOB parameters. The stored procedure uses unicode character data (nchar), while the ASP code is creating numeric parameters. If the stored procedure is correct, use the adWChar type in ASP (wide character).

(Why are you using double-byte unicode for just those two parameters, and single-byte varchar for all other character data?)

Your stored procedure is declared as (note bold entries)

CREATE PROCEDURE sp_AddressBook

@User_UserName varchar(20),

@User_Password varchar(20),

@User_Title varchar(5),

@User_FirstName varchar(20),

@User_LastName varchar(20),

@User_EmailAddress varchar(50),

@User_Telephone varchar(20),

@User_Mobile varchar(20),

@User_EmailShot int,

@User_Address1 varchar(30),

@USer_Address2 varchar(30),

@User_Address3 varchar(30),

@User_Area varchar(20),

@User_State nchar(2),

@User_Country varchar(5),

@User_YOB nchar(4),

@User_ID int OUTPUT

AS

while your setup in ASP is