ADODB.Recordset Error 3704 1

[nidgep]

hi

Im trying to get a simple recordset back from a sql server stored procedure.

The content of the sp can be seen below:

[COLOR=blue]
CREATE Procedure usp_GetSecondaryOwnersByUserID  (
	@SecOwnerId int
)
AS

IF NOT EXISTS (select * from sysobjects where id = object_id('dbo.SecondaryOwners'))
BEGIN
	CREATE TABLE dbo.SecondaryOwners (
		SecOwnerName varchar(255),
		SecOwnerID int
	)ON [PRIMARY]
END
ELSE
	BEGIN

		TRUNCATE TABLE dbo.SecondaryOwners
	END

IF NOT EXISTS (select * from sysobjects where id = object_id('dbo.ThirdOwners'))
BEGIN
	CREATE TABLE dbo.ThirdOwners (
		ThirdOwnerName varchar(255),
		ThirdOwnerID int
	)ON [PRIMARY]
END
ELSE
	BEGIN
		TRUNCATE TABLE dbo.ThirdOwners
	END


--get a list of users whom are listed as being secondaryOwner1
INSERT INTO SecondaryOwners 
SELECT DISTINCT UserSecurity.[Name] AS SecOwnerName, Workload.SecondaryOwner1 AS SecOwnerID
FROM UserSecurity RIGHT JOIN WorkLoad ON UserSecurity.LogOn_ID = WorkLoad.SecondaryOwner1



--get a list of users whom are listed as secondaryOwner2
INSERT INTO ThirdOwners
SELECT DISTINCT UserSecurity.[Name] AS ThirdOwnerName, Workload.SecondaryOwner2 AS ThirdOwnerID
FROM UserSecurity RIGHT JOIN WorkLoad ON UserSecurity.LogOn_ID = WorkLoad.SecondaryOwner2

--show the lists
--select * from secondaryowners
--select * from thirdowners


--get a list of records where the specified SecondaryOwner1 or SecondaryOwner2 id is listed
--basically on any given row there shouild be ONE ID that matches the supplied ID
--and this ID will reside EITHER in the SecOwnerID OR ThirdOwnerID fields but NEVER BOTH
SELECT 
	WorkLoad.RecordID, 
	UserSecurity.[Name], 
	WorkLoad.Owner, 
	SecondaryOwners.SecOwnerName,
	SecondaryOwners.SecOwnerID,
	ThirdOwners.ThirdOwnerName,
	ThirdOwners.ThirdOwnerID
FROM UserSecurity 
	INNER JOIN Workload ON UserSecurity.LogOn_ID = WorkLoad.Owner
	INNER JOIN SecondaryOwners ON SecOwnerID = WorkLoad.SecondaryOwner1
	INNER JOIN ThirdOwners ON ThirdOwnerID = WorkLoad.SecondaryOwner2
WHERE 
	SecondaryOwners.SecOwnerID=@SecOwnerID
	OR 
	ThirdOwners.ThirdOwnerID=@SecOwnerID;
GO

[/color]

Can anyone suggest why i might be getting such an error when attempting to work with the returned recordset? (Using VB6 with ADO 2.6). This error is raised when the code attempts to check for BOF/EOF.

thanks to anyone who can suggest a solution.

 

[Crowley16]

try putting set nocount on at the beginning of your sproc...

--------------------
Procrastinate Now!

 

[nidgep]

thanks Crowley16 - that worked at treat - what part exactly does SET NOCOUNT ON actually play?

 

[Crowley16]

when you run code, sql server automatically logs stuff, for example, when you run a insert statement, you'll get a rows affected message.

the rowcount message is notorious for conflicting with applications...

--------------------
Procrastinate Now!