CODEWITHSUNDEEP
sqlvbavb6
Sid
Sid

Reputation: 195

SQL records updated using VB

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

Upvotes: 2

Views: 303

Answers (2)

Sid
Sid

Reputation: 195

   Set gCommand = New ADODB.Command
        gCommand.CommandType = adCmdText
        gCommand.CommandText = sSql1
        gCommand.ActiveConnection = gconDb
        gCommand.Execute (cnt)

Upvotes: 1

Freelancer
Freelancer

Reputation: 9074

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

Upvotes: 0

Related Questions