Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

Stored procedure not working

I am using ASP classic and MS SQL 2008. I have a stored procedure that is not updating the data at all, I am not sure why because I am displaying the values on the screen and they look correct to me, the SP is simply not doing the job.

Here is the code for the SP.

USE [BlueDot]
GO

/****** Object:  StoredProcedure [dbo].[BlueDotUpdateAddInfo]    Script Date: 09/15/2015 16:45:51 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO



CREATE PROCEDURE [dbo].[BlueDotUpdateAddInfo]
    @UserId INT ,
    @editor1 VARCHAR(8000)
AS
    UPDATE  dbo.Users
    SET     Customtxt = @editor1
    WHERE   UserId = @UserId;

GO

Open in new window


This is the ASP code that calls the SP to update the DB:

<%

Dim sp_updatecustomtxt__UserId
sp_updatecustomtxt__UserId = "0"
if(Request("UserId") <> "") then sp_updatecustomtxt__UserId = Request("UserId")

Dim sp_updatecustomtxt__editor1
sp_updatecustomtxt__editor1 = "0"
if(Request("Editor1") <> "") then sp_updatecustomtxt__editor1 = Request("Editor1")

%>

<%

set sp_updatecustomtxt = Server.CreateObject("ADODB.Command")
sp_updatecustomtxt.ActiveConnection = MM_bluedot_STRING
sp_updatecustomtxt.CommandText = "dbo.BlueDotUpdateAddInfo"
sp_updatecustomtxt.CommandType = 4
sp_updatecustomtxt.CommandTimeout = 0
sp_updatecustomtxt.Prepared = true
sp_updatecustomtxt.Parameters.Append sp_updatecustomtxt.CreateParameter("@RETURN_VALUE", 3, 4)
sp_updatecustomtxt.Parameters.Append sp_updatecustomtxt.CreateParameter("@UserId", 3, 1,4,sp_updatecustomtxt__UserId)
sp_updatecustomtxt.Parameters.Append sp_updatecustomtxt.CreateParameter("@editor1", 200, 1,8000,sp_updatecustomtxt__editor1)
sp_updatecustomtxt.Execute()

%>

Open in new window


And here is the Page source view:

<html>
<head>
<title>Update Additional Information</title>
</head>
<body>
<p>Value of editor 1 :                                             <p>This is a test, value in database should be THIS.</p><p><br></p>
<p>userid : 30638</p>
</body>
</html>

Open in new window

Avatar of lcohan
lcohan
Flag of Canada image

Did you tested just the SP execution in SQL via SSMS?
like:

USE [BlueDot]
GO

EXEC BlueDotUpdateAddInfo  @UserId 1234,   @editor1 = 'bla..bla';

--then


USE [BlueDot]
GO
SELECT Customtxt FROM dbo.Users  WHERE   UserId = 1234;


Just MAKE SURE you don't run what I posted above in a PROD database AS IS...
If that doesn't work you should at least get an error or someting...maybe a trigger on that User table prevents the UPDATE?
Avatar of Aleks

ASKER

I just did and it worked fine.  I ran this:

USE [BlueDot]
 GO

 EXEC BlueDotUpdateAddInfo  @UserId = 30638,   @editor1 = 'Hello world';

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

It looks like all is fine but Ill recheck. Maybe the SP is not getting the values right ... sigh !
Avatar of Aleks

ASKER

You were right :#  .. a parameter was misspelled ... thx tho.
Anytime...