calling update stored procedure from asp page

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

Transact-SQL (2000)

calling update stored procedure from asp page

Author

Topic

robson
Starting Member

22 Posts

Posted - 2007-08-09 : 19:55:03

Hi Everyone,

I have the following stored procedure:

ALTER PROCEDURE [updateCategory]

@category_id int,
@category_name varchar(60)

AS
UPDATE category
SET
category_name = @category_name
WHERE category_id = @category_id

I am calling this from an ASP page, now I also have an Add Stored Procedure which works fine. The Update Stored Procedure does not.
I am getting a HTTP 500 - Internal server error. I believe it is something to do with the parameters for the Stored Procedure.

The ASP code which calls the Stored Procedure is:

Set rsCmd = Server.CreateObject("ADODB.Command")
rsCmd.ActiveConnection = DBActiveConnection()
rsCmd.CommandText = "updateCategory"
rsCmd.CommandType = 4

set sp_category_name = rsCmd.CreateParameter
("category_name",200,1,60,category_name)
rsCmd.Parameters.Append sp_category_name
set sp_category_id = rsCmd.CreateParameter
("category_id",131,3,0,category_id)
rsCmd.Parameters.Append sp_category_id

rsCmd.Execute

Please can you help me. Thank you.

Robson.

chrisrock
Starting Member

12 Posts

Posted - 2007-08-09 : 21:59:50

You have to turn off "Show friendly http error messages" in IE to see the real error. Tools -> Internet Options -> Advanced. Uncheck "Show friendly http error messages".

Let us know what the real error is.

robson
Starting Member

22 Posts

Posted - 2007-08-10 : 04:27:57

Hi chrisrock,

I did what you asked and get the following error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

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

/db_test.asp, line 29

Line 29 is: rsCmd.Execute

Thanks.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-10 : 04:33:25

I havent worked with asp for a while now, but I think you should be naming your parameters exactly as they are named in the sproc ie with the "@" at the front of the name:

something like this:

("@category_name",200,1,60,category_name)
rsCmd.Parameters.Append sp_category_name
set sp_category_id = rsCmd.CreateParameter
("@category_id",131,3,0,category_id)
rsCmd.Parameters.Append sp_category_id

Duane.

robson
Starting Member

22 Posts

Posted - 2007-08-10 : 04:52:36

This does not work. We do not append the @ before the same variable name in the asp as in the stored procedure.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-10 : 04:59:38

Maybe you don't, but these people and I do.
http://www.webconcerns.co.uk/asp/sqlqueries/sqlqueries.asp

Duane.

iminore
Posting Yak Master

141 Posts

Posted - 2007-08-10 : 08:06:03

Robson you're making heavy weather of that ASP. This should do it.

Dim objConn, catname, catid
set objConn=Server.CreateObject("ADODB.Connection")
call objConn.Open("Provider=SQLOLEDB;Data Source=servername;database=dbname;
UserID=user;Password=passcode")
catid = 1
catname = 'jkjk'
objConn.execute("updateCategory ('" & catname & "'," & catid & ")")

Easier to use the same procedure for adding catagories - just pass a zero id.
Also good idea to put 'set nocount on' as first statement in procedure.

robson
Starting Member

22 Posts

Posted - 2007-08-11 : 20:53:31

I am trying to use Stored Procedures on MS SQL 2000 with ASP. I could have easily done this by executing SQL statements, but have to use Stored Procedures. Using Stored Procedures for the first time, so lacking in the experience.

I have changed the following parameter for the stored procedure to the one given below it.

FROM:
set sp_category_id = rsCmd.CreateParameter("category_id",131,3,0,category_id)

TO:
set sp_category_id = rsCmd.CreateParameter("@category_id",3,1,,category_id)

I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to int.

Please can you help/advise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources