If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to get return value for the number of rows affected by update command

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Arrow How to get return value for the number of rows affected by update command

Hi,

i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?

Below is the normal way i did in vb.net, but how to check for the return value. Please help.


========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========

Thank you.
Reply With Quote
  #2 (permalink)  
Old
12 Monkey Method
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 565
you can add either of these statements to the SQL being called
[BOL} @@rowcount
[BOL] Rowcount_big

the difference is in the datatypes rowcount _big returns a bigint
and @@rowcount returns int

if you have over 2 billion rows user rowcount_big
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Hi Ruprect, thanks for your reply. My sql statement is a very simple insert query without using any parameters just like the one below:

sql = "INSERT INTO [Subscriber] ([SubID], [SubName], [SubEmail], [Status], [MailID], [SubscribeDate]) VALUES (SubID, SubName, SubEmail, 'Pending', MailID ,getDate())"

I'm unsure of how to include the " [BOL} @@rowcount ". Do you mean that i should add a parameter to return @@rowcount or there is other way to do it? I'm new to this, would you please give me an example.

Thanks for your time.
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
@@Rowcount stored the number of records affected by the immediately prior statement. The value is lost as soon as another statement is executed, so you must either use it immediately or store it in a procedure variable:

declare @RecordsAffected Int
.
.
.
.
.
Update/Select/Delete some records from somewhere....
set @RecordsAffected = @@RowCount

Look up @@Rowcount in Books Online for more details.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #5 (permalink)  
Old
12 Monkey Method
 
Join Date: Feb 2004
Location: San Antonio, TX
Posts: 565
thanks BLIND MAN
i didnt getthis until late
[BOL] stands for Books Online it's the sql server help file
i was giving you the article title

and since blindman got it exactly i've no need to reiterate
good luck.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 522
see if there is something like mycommand.rowsaffected property.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Cool Thanks!

Thanks Blindman and Thanks Ruprect. I'll study BOL for details of @@rowcount.
Reply With Quote
  #8 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
You should also follow ms_sql_dba's suggestion to see if there is a method to return the value via VB.

It might be more appropriate if you are going to use the value in your VB code.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Smile

Hi ms_sql_dba, there isn't any rowsaffected property, however there is this UpdatedRowSource and others ..

Thanks for your suggestion, although i'm unsure of their usage, i'll look into it and see if i can find something which stores the value of number of rows affected!
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Thumbs up

Sure Blindman, i'll study both ways and see which one is more applicable for my situation. You have a great day.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Hi Everyone,

I managed to find another solution to my question. Just simply assign the value like this line:-

rowsAffected = myCommand.ExecuteNonQuery()
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 522
see, it was simple!
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Malaysia
Posts: 9
Cool

Yea. Lesson learned! Cheers!!!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On