Erroor with execute of insert sp 1

[abienz]

Hi there, I hope someone can help me with this strange error i'm getting
when simply executing a stored procedure to insert data to a table.

here's my code for the ASP page...

set Cmd = Server.CreateObject("ADODB.Command&quot
Cmd.ActiveConnection = MyConnection
Cmd.CommandText = "sp_insert_stp_portfolio_content"
Cmd.CommandType = 4
Cmd.CommandTimeout = 0
Cmd.Prepared = true
Cmd.Parameters.Append Cmd.CreateParameter("RETURN_VALUE", 3, 4)
Cmd.Parameters.Append Cmd.CreateParameter("@portfolio_id", 3,
1,4,Cmd__portfolio_id)
Cmd.Parameters.Append Cmd.CreateParameter("@EpicCode", 129,
1,4,Cmd__EpicCode)
Cmd.Parameters.Append Cmd.CreateParameter("@purchase_date", 135,
1,8,Cmd__purchase_date)
Cmd.Parameters.Append Cmd.CreateParameter("@MidPrice", 14,
1,9,Cmd__MidPrice)
Cmd.Parameters.Append Cmd.CreateParameter("@Differential", 14,
1,9,Cmd__Differential)
Cmd.Parameters.Append Cmd.CreateParameter("@PricePurchase", 14,
1,9,Cmd__PricePurchase)
Cmd.Parameters.Append Cmd.CreateParameter("@Quantity", 3, 1,4,Cmd__Quantity)
Cmd.Parameters.Append Cmd.CreateParameter("@profit", 5, 1,8,Cmd__profit)
Cmd.Parameters.Append Cmd.CreateParameter("@last_update", 135,
1,8,Cmd__last_update)
Cmd.Parameters.Append Cmd.CreateParameter("@ISIN", 129, 1,12,Cmd__ISIN)
Cmd.Parameters.Append Cmd.CreateParameter("@status", 16, 2)
Cmd.Execute()

and the code for my Sp is here...

CREATE PROCEDURE [sp_insert_stp_portfolio_content]
(@portfolio_id [int],
@EpicCode [char](4),
@purchase_date [datetime],
@MidPrice [decimal],
@Differential [decimal],
@PricePurchase [decimal],
@Quantity [int],
@profit [float],
@last_update [datetime],
@ISIN [char](12),
@status tinyint OUTPUT)

AS INSERT INTO [stepinvest].[thestp].[stp_portfolio_content]
( [portfolio_id],
[EpicCode],
[purchase_date],
[MidPrice],
[Differential],
[PricePurchase],
[Quantity],
[profit],
[last_update],
[ISIN])

VALUES
( @portfolio_id,
@EpicCode,
@purchase_date,
@MidPrice,
@Differential,
@PricePurchase,
@Quantity,
@profit,
@last_update,
@ISIN)

if @@rowcount = 0 or @@error !=0
begin
ROLLBACK TRANSACTION
SET @Status = 0
return
end
COMMIT TRANSACTION
SET @Status = 1

It's all pretty simple code that I''ve used before so I don't know why I'm
getting this 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.

and if I load this page onto my website I get this error!!
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
Does anyone know why?
Cheers,
Alex.

 

[stoolpigeon]

I hope I'm helping here and not muddying the waters.

I had some trouble a bit back w/an insert stored procedure and it was because Insert follows batch rules and can only do one thing at a time.

I'm not sure how the error trapping at the end of your procedure is viewed in that light-but you may want to look at the sql server books online under 'Batches'. It goes into more detail there on how to divide up a procedure so that batches are executed succesfully.

Hope that helps,
Ron

 

[grahamm]

Stored procedures can be multistep. I noticed that you have a rollback transaction, but no begin transaction. Assuming that the sp is a self contained transaction you should alter your sp to include a begin transaction

create procedure ...... as
begin transaction
insert ...
if ...
rollback transaction
..
commit work