Results 1 to 13 of 13
  1. #1
    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.

  2. #2
    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

  3. #3
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    @@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

  5. #5
    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.

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    see if there is something like mycommand.rowsaffected property.

  7. #7
    Join Date
    Apr 2004
    Location
    Malaysia
    Posts
    9

    Cool Thanks!

    Thanks Blindman and Thanks Ruprect. I'll study BOL for details of @@rowcount.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,576
    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

  9. #9
    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!

  10. #10
    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.

  11. #11
    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()

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    see, it was simple!

  13. #13
    Join Date
    Apr 2004
    Location
    Malaysia
    Posts
    9

    Cool

    Yea. Lesson learned! Cheers!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •