ASP stored proc insert problem

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)

ASP stored proc insert problem

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-02-03 : 21:25:36

This should be simple. I have two other scripts using the same code (different stored proc) and they work fine. I'm getting the useful error message below.

===========================
error message:
===========================

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.
/adotest.asp, line 35

===========================
Here is the asp code:
===========================
Dim erID, sRole, sName, sExternal, sFunction, sParticipation
erID = 1280
sRole = "not selected"
sName = "John Doe"
sExternal = "0"
sFunction = "not selected"
sParticipation = "100"

Set cmd = Server.CreateObject("ADODB.Command")
Conn.Open

With cmd
.ActiveConnection = conn
.CommandText = "adm_ERPrjGovernance_Add"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@iID", adInteger,adParamInput,, erID)
.Parameters.Append .CreateParameter("@Role", adVarChar, adParamInput, 25,sRole)
.Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 100,sName)
.Parameters.Append .CreateParameter("@IsExt", adBoolean, adParamInput, 1,sExternal)
.Parameters.Append .CreateParameter("@Function", adVarChar, adParamInput,50,sFunction)
.Parameters.Append .CreateParameter("@Participation", adDecimal, adParamInput, ,sParticipation)
.Execute
End With

Conn.Close
set conn = nothing
set cmd = nothing

===========================
Here is the stored proc:
===========================
CREATE PROCEDURE adm_MyTable_Add

(@iID int,
@Role varchar(25),
@Name varchar(100),
@IsExt bit,
@Function varchar(50),
@Participation decimal
)

AS
Declare @iDescID int
SELECT @iDescID = desc_id FROM tbl_desc WHERE my_id = @iID

Declare @RoleID int
SELECT @RoleID = role_id FROM tbl_roles WHERE role_name = @Role
if @@rowcount = 0
begin
SELECT @RoleID = 1
end

Declare @FunctionID int
SELECT @FunctionID = function_id FROM tbl_functions WHERE function_name = @Function
if @@rowcount = 0
begin
SELECT @FunctionID = 1
end

INSERT INTO tbl_MyTable
(desc_id, myid, role_id, resource, iexternal, function_id, participation)
VALUES
(@iDescID, @iID, @RoleID, @Name, @IsExt, @FunctionID, @Participation)
GO

===========================
The stored proc works fine using Query Analyzer

Thanks in advance
-david

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-02-03 : 21:35:47

Hi

Try wrapping the commands inside your proc in SET NOCOUNT ON and SET NOCOUNT OFF.

I.E.

Create Proc Foo

AS
SET NOCOUNT ON

--do stuff

SET NOCOUNT OFF

GO

Without that, each of your commands returns a "n Rows Affected" message to ADO which it considers to be a separate result set.

Damian

davidliv
Starting Member

45 Posts

Posted - 2004-02-03 : 21:38:49

Same problem.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-02-03 : 21:51:09

Ahhh ok
Then I think your problem is probably passing a string "0" to a boolean parameter. "0" is a character, not a boolean value. Try passing true or false.

Damian

davidliv
Starting Member

45 Posts

Posted - 2004-02-03 : 22:28:39

Nope. I fixed it though. Problem delt w/ the adDecimal field.
I added the following to my asp code (just before the ".Execute")and all is well

.Parameters("@Participation").Precision=18
.Parameters("@Participation").NumericScale = 2

Thanks for you help though.

-david

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources