Error when calling stored procedure

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)

Error when calling stored procedure

Author

Topic

raj74
Starting Member

1 Post

Posted - 2003-05-04 : 10:13:42

hi guys,

When i try to insert a record from asp by calling a stored procedure in sql server2000 i am getting the following error.

the error is ...

Microsoft OLE DB Provider for SQL Server error '80040e10'

Procedure 'sp_InsertProfile' expects parameter '@Expect', which was not supplied.

my stored procedure is ...

CREATE PROCEDURE sp_InsertProfile
(
@Name varchar(50),
@Expect varchar(500)
)
AS

IF EXISTS(SELECT Serial_No from Profile_Details Where Profile_ID =@Profile_ID)
return(0)
ELSE
INSERT INTO Profile_Details
(Name,Expectation)

VALUES

(@name,@Expect)

RETURN @@IDENTITY

SET NOCOUNT OFF
GO

my asp coding is ...
...
commInsert.Parameters.Append commInsert.CreateParameter("@Expect",adVarChar,1,500,vExpectation)
commInsert.Execute()

thanks in advance
raj74

Edited by - merkin on 05/04/2003 20:01:46

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-04 : 10:47:06

1. Try debugging the sp using the Query Analyzer to see if given the proper input the sp behaves correctly.

2. Place the following

Response.Write vExpectation
Response.End

before

commInsert.Parameters.Append commInsert.CreateParameter("@Expect",adVarChar,1,500,vExpectation)

and what do you see.

3. Are you using commInsert.CommandType = 4 ?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-05-04 : 12:30:27

Raj:
Your procedure needs two parameters, have you supplied them both?

quote:
CREATE PROCEDURE sp_InsertProfile
(
@Name varchar(50),
@Expect varchar(500)
)
AS

Dim commInsert As New Adodb.Command
....
commInsert.Parameters.Append commInsert.CreateParameter("@Name",adVarChar,1,50,vName)
commInsert.Parameters.Append commInsert.CreateParameter("@Expect",adVarChar,1,500,vExpectation)
commInsert.Execute()

OS

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-04 : 14:32:02

When calling from asp it ignores the parameter name and just takes the order.

==========================================
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