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

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