ExecuteNonQuery is returning -1 though the record has been inserte

12 Jan 2007 12:52 PM

Hari

Hi,

I am new to ADO.NET. I am develpoing an application to manage employee data.
I am using Microsoft Enterprised Library Dlls to connect to the database and
execute the stored procedure.

Currently I am having problem in executing in Stored Procedure used to
insert data in to table.

My StoredProcedure is

EMP_Add
    @ID varchar(50),
                @Name varchar(50),
    @DOB datetime,
    @DOJ datetime,
    @RecordedBy int,
    @Comments varchar(1000)
AS
BEGIN TRANSACTION

BEGIN
                SET NOCOUNT ON;

    INSERT INTO EMP([ID],[Name],[DOB],[DOJ],[RecordedBy],[Comments])
VALUES(@ID,@Name,@DOB,@DOJ,@RecordedBy,@Comments)
END

IF @@ERROR <> 0 ROLLBACK

COMMIT TRANSACTION

When i Execute the stored procedure using DBCommand.ExecuteNonQuery method,
i am getting -1 as the output.But the table is getting updated with all the
values.

Can some one help me with this problem

Regards
hari

12 Jan 2007 1:15 PM

Stephany Young

What do you expect to get back seeing as you have SET NOCOUNT ON?

Show quote

"Hari" <H***@discussions.microsoft.com> wrote in message
news:0C80E50F-0B11-421E-92D3-32E32D679F4F@microsoft.com...
> Hi,
>
> I am new to ADO.NET. I am develpoing an application to manage employee
> data.
> I am using Microsoft Enterprised Library Dlls to connect to the database
> and
> execute the stored procedure.
>
> Currently I am having problem in executing in Stored Procedure used to
> insert data in to table.
>
> My StoredProcedure is
>
> EMP_Add
> @ID varchar(50),
> @Name varchar(50),
> @DOB datetime,
> @DOJ datetime,
> @RecordedBy int,
> @Comments varchar(1000)
> AS
> BEGIN TRANSACTION
>
> BEGIN
> SET NOCOUNT ON;
>
> INSERT INTO EMP([ID],[Name],[DOB],[DOJ],[RecordedBy],[Comments])
> VALUES(@ID,@Name,@DOB,@DOJ,@RecordedBy,@Comments)
> END
>
> IF @@ERROR <> 0 ROLLBACK
>
> COMMIT TRANSACTION
>
> When i Execute the stored procedure using DBCommand.ExecuteNonQuery
> method,
> i am getting -1 as the output.But the table is getting updated with all
> the
> values.
>
> Can some one help me with this problem
>
> Regards
> hari
>

12 Jan 2007 6:06 PM

William (Bill) Vaughn

Precisely. When you execute a SP to perform the action Commands
(UpdateCommand, InsertCommand etc.) you need to return (via the "Count"
feature) a value of 1. Setting SET NOCOUNT ON drops this rowcount packet
from the returning TDS stream sent from the server. See Chapter 12.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

Show quote

"Stephany Young" <noone@localhost> wrote in message
news:OIGcOvkNHHA.2236@TK2MSFTNGP02.phx.gbl...
> What do you expect to get back seeing as you have SET NOCOUNT ON?
>
>
> "Hari" <H***@discussions.microsoft.com> wrote in message
> news:0C80E50F-0B11-421E-92D3-32E32D679F4F@microsoft.com...
>> Hi,
>>
>> I am new to ADO.NET. I am develpoing an application to manage employee
>> data.
>> I am using Microsoft Enterprised Library Dlls to connect to the database
>> and
>> execute the stored procedure.
>>
>> Currently I am having problem in executing in Stored Procedure used to
>> insert data in to table.
>>
>> My StoredProcedure is
>>
>> EMP_Add
>> @ID varchar(50),
>> @Name varchar(50),
>> @DOB datetime,
>> @DOJ datetime,
>> @RecordedBy int,
>> @Comments varchar(1000)
>> AS
>> BEGIN TRANSACTION
>>
>> BEGIN
>> SET NOCOUNT ON;
>>
>> INSERT INTO EMP([ID],[Name],[DOB],[DOJ],[RecordedBy],[Comments])
>> VALUES(@ID,@Name,@DOB,@DOJ,@RecordedBy,@Comments)
>> END
>>
>> IF @@ERROR <> 0 ROLLBACK
>>
>> COMMIT TRANSACTION
>>
>> When i Execute the stored procedure using DBCommand.ExecuteNonQuery
>> method,
>> i am getting -1 as the output.But the table is getting updated with all
>> the
>> values.
>>
>> Can some one help me with this problem
>>
>> Regards
>> hari
>>
>
>