number of affected records

Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005

--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?

-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?

cmd.Parameters.Add(newSqlParameter("@.returnVal",SqlDbType.Int));

cmd.Parameters["@.returnVal"].Direction =ParameterDirection.ReturnValue;

Please help me out with this.Thanks

Are you positive your INSERT statement isn't inserting records 1 at a time, like in a loop or something to that effect? Can you post your query?

You can retrieve values back using the method you wrote above.|||

oh...now that you pointed out...yes..my insert is inserting 1 record at a time in a loop...i am inserting it using the stored procedure...but i want to know how many got inserted at the end of looping successfully...in fact i know 'how many part' as well..b/c my dropdownList gets updated instantly with the inserted values,so i can look at it<but i don't want to rely on that to decipher out the num of recs>.Instead i wanted to display the num of recs affected ,as soon as i click on 'add' button,some parameters-something which comes out from DB as a result of executing of query.Do u think thats possible?or do i have to check on successful returnValue in a loop and increment the counter,to tell me how many times it inserted successfully?

|||

The proc you are calling to do the INSERT can keep track of number of records affected and increment a counter and send back the value at the end through an OUTPUT parameter.

|||

Thanks a million...Can you please tell me what adjustment i need to make in stored procedure,to account this record tracking?Currently it has just the simple insert based on some parameter,i send.

Also for the output parameter,will this suffice or what i need to change here.

cmd.Parameters.Add(

newSqlParameter("@.outputVal",SqlDbType.Int));

cmd.Parameters[

"@.outputVal"].Direction =ParameterDirection.Output;

cmd.ExecuteNonQuery();

return cmd.Parameters["@.outputVal"].value;

|||The code you have to retrieve the OUTPUT value looks good. If you can post the proc I can look at it.|||

Thanks ..

This is one of the stored proc,i have which gets executed in the loop

ALTER PROCEDURE

dbo.IS_spInsertToHardware

@.insertItem

nvarchar(50)

AS

insert intotblProducts (Product)values(@.insertItem)RETURN|||Oh. So you are calling the proc for each insert?|||

Yes...inside the function,this is how it is : value1, value2, value3, value4 are the values which user can enter in text boxes..at a time user can enter max 4 values and on click of update,it gets added to the dropdownlist.i don't know any better way to do it..

string

[] textValues =newstring[] { value1, value2, value3, value4 };if ((value1 !=string.Empty) || (value2 !=string.Empty) ||

(value3 !=

string.Empty) || (value4 !=string.Empty))

{

for (int i = 0; i < textValues.Length; i++)

{

if (textValues[i] !=string.Empty)

{//some additional condition which i need to check

if ((id1 > 0) && (id2 == 0))

{ retVal = invInsert.insertData(id1, textValues[i], sproc);}

|||

So you are making up to 4 trips for each INSERT? There is a better way of doing it all in one trip. Concatenate all the values into one string and send it to the proc. In your proc, parse the string and strip out each value and do the INSERT. Check for @.@.ERRPR after the INSERT and if its 0, increment a counter. After all the INSERTs are done, return the counter value and it should have the number of successfull INSERTs.

Search this forum for some code on parsing the string and extracting the values. I think I posted some code here.

|||

Thanks Dinaker,,i will look into it.....yes...i don't like making up to 4 trips to DB either for each insert..but i couldn't find any better way of doing it when i was coding..so did it this way...

|||

Hi,

In my opinion, I would put a counter in my C# or VB code and accumulate the return value from ExecuteNonQuery.

It would be more easy than calculating in the stored procedure.

HTH.

|||You are right...thats what i have done at this point..inside my db class i haveint n = cmd.ExecuteNonQuery().. in my C# code i check if n>0,if it is i increment the counter ...But i also would like to learn how it can be done inside the stored procedure...i haven't found yet,.how to parse the string in stored procedure and loop through each value and get the success count,as dinaker suggested...thanks|||

Kevin Yu - MSFT:

In my opinion, I would put a counter in my C# or VB code and accumulate the return value from ExecuteNonQuery. It would be more easy than calculating in the stored procedure.

Kevin

Do you really think making 4 trips do the DB is better than one? The increment can be done either at the application level or in the proc.

|||

Hi,

Of course, this is not a good practice. I would put the validation for these 4 strings in the stored procedure and make the 4 strings as parameters. Then the stored procedure will return rows affected at once.