[AccessD] OT-Get Value Of @@ROWCOUNT Using VB6 Calling SQL Server 2005 Update Stored Procedure

The simplest thing to do is declare a variable in VB6 code, then pass it in
as an OUT parameter. Sprocs recognize two kinds of parameters, IN and OUT.
You can set the value of any of the OUT parameters within your sproc, and
then when VB6 gets control again it will have access to the values. Like so:

Dim lngMyVar as Long

<SQL>
Create Procedure Genesis_Update_LogOnID
(
@nvPayroll [nvarchar](100),
@myOutVar [int] OUT
)
....
-- somewhere near the end of your procedure
SET @myVar = @@RowCount
</SQL>

Now when you call your procedure, pass both the logon and the lngMyVar. When
SQL finishes, lngMyVar will have the value you want.

hth,
Arthur

On 3/13/08, paul.hartland at fsmail.net <paul.hartland at fsmail.net> wrote:
>
> To all,
>
> I have a vb6 app that executes an update SP on SQL 2005 as below:
> Genesis_Update_LogOnID
> (
> @nvPayroll [nvarchar](100)
> )
> AS
> UPDATE tblPersonnel
> SET LogOnID = SQ1.NewLogOn, NewRASUser = 1
> FROM (SELECT dbo.tblPersonnel.PayrollNo, dbo.tblPersonnel.LogOnID,
> dbo.tblPersonnel.FinishDate, dbo.tblPersonnel.NewRASUser, CASE WHEN
> CHARINDEX(' ',
> [Forename/s]) < 1 THEN [Forename/s] ELSE LEFT([Forename/s], CHARINDEX(' ',
> [Forename/s]) - 1) END + '.' + CASE WHEN CHARINDEX(' ', [Surname])
> < 1 THEN [Surname] ELSE LEFT([Surname], CHARINDEX(' ', [Surname]) - 1) END
> AS NewLogOn
> FROM dbo.tblPersonnel INNER JOIN
> dbo.tblPersonnelCategoriesSelected ON dbo.tblPersonnel.PayrollNo =
> dbo.tblPersonnelCategoriesSelected.PayrollNo INNER JOIN
> dbo.tblPersonnelCategories ON
> dbo.tblPersonnelCategoriesSelected.CategoryID =
> dbo.tblPersonnelCategories.CategoryID
> WHERE (dbo.tblPersonnelCategories.CategoryID <> 100) AND (
> dbo.tblPersonnelCategories.CategoryID <> 101) AND
> (dbo.tblPersonnelCategories.CategoryID <> 102) AND (
> dbo.tblPersonnelCategories.ManagementStaff = 1) AND
> dbo.tblPersonnel.PayrollNo = @nvPayroll
> GROUP BY dbo.tblPersonnel.PayrollNo, dbo.tblPersonnel.LogOnID,
> dbo.tblPersonnel.FinishDate, dbo.tblPersonnel.NewRASUser, CASE WHEN
> CHARINDEX(' ',
> [Forename/s]) < 1 THEN [Forename/s] ELSE LEFT([Forename/s], CHARINDEX(' ',
> [Forename/s]) - 1) END + '.' + CASE WHEN CHARINDEX(' ', [Surname])
> < 1 THEN [Surname] ELSE LEFT([Surname], CHARINDEX(' ', [Surname]) - 1) END
> HAVING (dbo.tblPersonnel.LogOnID IS NULL OR
> dbo.tblPersonnel.LogOnID = N'' OR
> dbo.tblPersonnel.LogOnID = N' ') AND (dbo.tblPersonnel.FinishDate IS NULL)
> ) AS SQ1
> The calling line is mydataconn.execute("Genesis_Update_LogOnID '" &
> PublicPayrollNo & "'")
> I have never really used @@ROWCOUNT and returning values before, could
> someone tell me how to modify my SP and calling line in VB6 to return the
> value of @@ROWCOUNT, have tried looking on the web but can't seem to find
> exactly what I am looking for (mind you I am having a few blonde moments
> today)
>
> Thanks in advance for all your help on this....
>
>
> Paul Hartland
> paul.hartland at fsmail.net
> 07730 523179
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>