2

I have a VB code that contains the below SQL update:

UPDATE table_A SET UPD_FLG = 'N' WHERE UPD_FLG = 'Y'

I need to find out the number of rows updated by the above query in a separate variable/record set. Currently I m using a select query to get the same. But is there a way in which this can be accomplished using VB functions like EXECUTE?

I am using VB 6.0

Freelancer
  • 9,008
  • 7
  • 42
  • 81
Sid
  • 195
  • 1
  • 4
  • 11

2 Answers2

1
   Set gCommand = New ADODB.Command
        gCommand.CommandType = adCmdText
        gCommand.CommandText = sSql1
        gCommand.ActiveConnection = gconDb
        gCommand.Execute (cnt)
Sid
  • 195
  • 1
  • 4
  • 11
0

You can right simple procedure for this purpose as follows:

CREATE PROCEDURE UpdateTables
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @RowCount1 INTEGER
    DECLARE @RowCount2 INTEGER
    DECLARE @RowCount3 INTEGER
    DECLARE @RowCount4 INTEGER

    UPDATE Table1 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount1 = @@ROWCOUNT
    UPDATE Table2 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount2 = @@ROWCOUNT
    UPDATE Table3 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount3 = @@ROWCOUNT
    UPDATE Table4 Set Column = 0 WHERE Column IS NULL
    SELECT @RowCount4 = @@ROWCOUNT

    SELECT @RowCount1 AS Table1, @RowCount2 AS Table2, @RowCount3 AS Table3, @RowCount4 AS Table4
END

Reference:

Return number of rows affected by UPDATE statements

OR

How can I get the number of records affected by a stored procedure?

Use of this procedure in VB:

http://www.dbforums.com/microsoft-sql-server/993294-how-get-return-value-number-rows-affected-update-command.html

Community
  • 1
  • 1
Freelancer
  • 9,008
  • 7
  • 42
  • 81
  • Set gCommand = New ADODB.Command gCommand.CommandType = adCmdText gCommand.CommandText = sSql1 gCommand.ActiveConnection = gconDb gCommand.Execute (cnt) – Sid Apr 23 '13 at 05:58