Insert Stmt returns -1

[Reza Raad]

first paste your stored procedure code here. do you return any thing in your procedure code?

Human knowledge belongs to the world http://www.rad.pasfu.com/index.php

[AB7771]

i dont return any thing from the proc, but the default value returned from an insert proc is 1, in my case donno y it returns -1. I have done some calculations into some variables and a simple insert statement is used to insert the record in the table, I have also used a transaction for this and after the insert statment i have checked if the @intErrorCode is not 0 then rollback transaction, The insert works fine but it returns -1.

Thanks & Regards, Pramod "Everyone is a genius at least once a year"

[Lost User]

Which function you are using ExecuteScaler, executenonquery, etc Please specify

[AB7771]

dbcommand.ExecuteNonQuery();

Thanks & Regards, Pramod "Everyone is a genius at least once a year"

[Reza Raad]

if you call another stored procedure for insert into this procedure? if this is true: call to another procedures into one procedure cause returning -1

Human knowledge belongs to the world http://www.rad.pasfu.com/index.php

[N a v a n e e t h]

Do you mean ExecuteNonQuery returns -1? If yes, see what the documentation says For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Navaneeth How to use google | Ask smart questions

[AB7771]

yes, tht's a problem, i can the values in the table that means the insert statement was executed, and if an error has occured the insert should be rolledback rite, but this rollback is not happening and m not able to find what the error is. wht r the ways to find the error that has caused the rollback?

Thanks & Regards, Pramod "Everyone is a genius at least once a year"

[Wendelius]

In your stored procedure you can use RAISERROR[^] to generate an error and to inform client what has happened.

The need to optimize rises from a bad design.My articles[^]

[N a v a n e e t h]

Since you are using Stored procedures, you can handle the errors inside the procedure and rollback if necessary. Add an output parameter which can be used to inform the client program about the process status. Something like,

CREATE PROCEDURE Foo
@FooColumn int = NULL
@ProcessStatus varchar(100) = NULL
AS
// your insert query
IF @@ERROR <>0
BEGIN
ROLLBACK
SET @ProcessStatus = 'Rollbacked'
END
ELSE
BEGIN
SET @ProcessStatus = 'Completed'
END

Navaneeth How to use google | Ask smart questions

[AB7771]

Thanks guys for all you suggestions, I found the problem with my stored proc, I had written SET NOCOUNT ON; which was causing the -1 to return. Now i have switched oFF that and it returns me 1.

Thanks & Regards, Pramod "Everyone is a genius at least once a year"