ADO Recordset Empty After Running SP

M

m.p.fletcher

Hey,
I am sort of going borderline insane with a problem I am currently
facing. I have written a stored procedure on SQL SERVER (EXPRESS) which
takes 3 parameters and returns a single column. The expected returned
results is a single row. The SP runs successfully and the result is
correctly returned when I run the SP directly on the server. Using the
following code however (irrelevant bits removed) the returned recordset
is EMPTY:

***CODE START
'Connection Objects
Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset

' Establish connection.
Set sqlConn = New ADODB.Connection
sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open

sqlConn.CursorLocation = adUseClient 'Cache data locally
Set sqlCmd = New ADODB.Command
' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = "sp_UI_AddServiceRelationship"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ParentRId").Value =
ReturnRelationshipID(destinationnode.Key)
.Parameters("@ServiceName").Value = draggednode.Text
.Parameters("@Sequence").Value = intNewSeqID
End With
Set sqlRS = sqlCmd.Execute

'!!!! At this point sqlRS recordset should not be EMPTY FFS!!!

'Close the connection to the server
sqlConn.Close
Set sqlConn = Nothing

'Close remaining connections
sqlRS.Close
Set sqlRS = Nothing
Set sqlCmd = Nothing

***CODE END

My current solution to this problem was to write a second SP which
returns the value that 'sp_UI_AddServiceRelationship' should be
returning in the first place as follows:

***CODE START
'Connection Objects
Dim sqlConn As ADODB.Connection
Dim sqlCmd As ADODB.Command
Dim sqlRS As ADODB.Recordset

' Establish connection.
Set sqlConn = New ADODB.Connection
sqlConn.ConnectionString = CurrentProject.Connection
sqlConn.Open

sqlConn.CursorLocation = adUseClient 'Cache data locally
Set sqlCmd = New ADODB.Command
' Open recordset.
With sqlCmd
.ActiveConnection = sqlConn
.CommandText = "sp_UI_AddServiceRelationship"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ParentRId").Value =
ReturnRelationshipID(destinationnode.Key)
.Parameters("@ServiceName").Value = draggednode.Text
.Parameters("@Sequence").Value = intNewSeqID
End With
Set sqlRS = sqlCmd.Execute

'!!!! At this point sqlRS recordset should not be EMPTY FFS!!!

With sqlCmd
.ActiveConnection = sqlConn
.CommandText = "sp_UI_GetLastAddedRID"
.CommandType = adCmdStoredProc
End With
Set sqlRS = sqlCmd.Execute

'!!!! sqlRS recordset now contains the required data!!!

'Close the connection to the server
sqlConn.Close
Set sqlConn = Nothing

'Close remaining connections
sqlRS.Close
Set sqlRS = Nothing
Set sqlCmd = Nothing

***CODE END

Could anyone be so kind and point out the problem??

Kind regards,
Fletch
 
N

Norman Yuan

Could show the SP code here.

Here is my guess: if somehow there are more than one "Select..." in the SP,
you may have to call RecordSet.NextRecordSet to find the record.

However, if you only return one row with one column, it is a single value,
isn't it? So, why not using a output parameter, instead of a recordset to
retrieve a single value from database?
 
S

Sylvain Lafontaine

Make sure that you have set the option SET NOCOUNT ON. If this doesn't
work, then show us the code for these SP.

Finally, don't use the prefix sp_ as it has a special signification for
SQL-Server.
 
M

m.p.fletcher

The SP Code is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------
-- sp_UI_AddServiceRelationship
-- INPUT: <@ParentRId> Parent Relationship Id
--
-- <@ServiceName> Child Service Name
--
-- <@Sequence> Sequence for the new relationship
-- The UI will set this to the last
-- sequence number in that parent.
--
-- <@ErrorLevelId> The error level for this relationship
-- if this is null then the default is used
-- for that service.
--
-- OUTPUT: ServiceRId The new relationship Id that was just created
--
-- All inputs must be specified
-- The child service name must exist in the tbl_SERVICES
-- If the @ParentRId is NULL (or not specified) then the parent name is
set to
-- NULL and the child is a root node. root nodes cannot already be in
the
-- table as a root node.
--
--
-- VERSION 0.1
ALTER PROCEDURE [dbo].[sp_UI_AddServiceRelationship]
(
@ParentRId Int = NULL, --Parent Relationship Id
@ServiceName Varchar(35) = NULL, --Child Service Name
@Sequence Int = NULL, --Relationship Sequence
@ErrorLevelId Int = NULL --Error level Id
)
AS
BEGIN
DECLARE @ParentName varchar(35) --The Parent name is needed in the
insert
--ensure the childname and the sequence are not null.
IF (@ServiceName is NULL OR @Sequence is NULL)
BEGIN
RAISERROR('@ChildName or @Sequence cannot be NULL',16,1)
Return -1
END
--childname must exist in the services table
IF (SELECT COUNT([Name]) FROM [Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName) != 1
BEGIN
SET @ServiceName = CAST(@ServiceName + ' does not exist as a Service'
as Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--set the error level
ELSE IF @ErrorLevelId is NULL
BEGIN
SET @ErrorLevelId = (SELECT DefaultErrorLevel FROM
[Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName)
END
--if the parent relationship id is null then the sequence must be 0
and the root
-- node cannot exist already
IF @ParentRId IS NULL
BEGIN
--the sequence must be 0 as this is a root node
SET @Sequence = 0
--root nodes cannot exist already
IF (SELECT COUNT(Child_SVS_Name) FROM
[Archstore].[dbo].tbl_SVS_RELATIONSHIPS
WHERE Child_SVS_Name = @ServiceName AND
Parent_SVS_NAME is NULL) > 0
BEGIN
SET @ServiceName = CAST(@ServiceName + ' Already a root service' as
Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--the Parent name is null
SET @ParentName = NULL
END
-- if the parent Id is not null the parent name is retreived
-- (the parent id of the service that is being added is the
child_svs_id in the
-- svs relationships table, since we are passing the id of the
service we want
-- to add to)
ELSE IF @ParentRId IS NOT NULL
BEGIN
SET @ParentName = (SELECT Child_SVS_Name
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE Id = @ParentRId)
END
--Add the service relationship.
INSERT INTO [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
([Parent_SVS_Name]
,[Child_SVS_Name]
,[Sequence]
,[ErrorLevelId])
VALUES
(@ParentName, @ServiceName, @Sequence, @ErrorLevelId)
--return the new service id
SELECT Id AS ServiceRID
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE (Parent_SVS_Name = @ParentName AND
Child_SVS_Name = @ServiceName AND
Sequence = @Sequence)

END
 
A

aaron.kempf

god I can't wait for try/catch in ADP 2007

-Aaron



The SP Code is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------
-- sp_UI_AddServiceRelationship
-- INPUT: <@ParentRId> Parent Relationship Id
--
-- <@ServiceName> Child Service Name
--
-- <@Sequence> Sequence for the new relationship
-- The UI will set this to the last
-- sequence number in that parent.
--
-- <@ErrorLevelId> The error level for this relationship
-- if this is null then the default is used
-- for that service.
--
-- OUTPUT: ServiceRId The new relationship Id that was just created
--
-- All inputs must be specified
-- The child service name must exist in the tbl_SERVICES
-- If the @ParentRId is NULL (or not specified) then the parent name is
set to
-- NULL and the child is a root node. root nodes cannot already be in
the
-- table as a root node.
--
--
-- VERSION 0.1
ALTER PROCEDURE [dbo].[sp_UI_AddServiceRelationship]
(
@ParentRId Int = NULL, --Parent Relationship Id
@ServiceName Varchar(35) = NULL, --Child Service Name
@Sequence Int = NULL, --Relationship Sequence
@ErrorLevelId Int = NULL --Error level Id
)
AS
BEGIN
DECLARE @ParentName varchar(35) --The Parent name is needed in the
insert
--ensure the childname and the sequence are not null.
IF (@ServiceName is NULL OR @Sequence is NULL)
BEGIN
RAISERROR('@ChildName or @Sequence cannot be NULL',16,1)
Return -1
END
--childname must exist in the services table
IF (SELECT COUNT([Name]) FROM [Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName) != 1
BEGIN
SET @ServiceName = CAST(@ServiceName + ' does not exist as a Service'
as Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--set the error level
ELSE IF @ErrorLevelId is NULL
BEGIN
SET @ErrorLevelId = (SELECT DefaultErrorLevel FROM
[Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName)
END
--if the parent relationship id is null then the sequence must be 0
and the root
-- node cannot exist already
IF @ParentRId IS NULL
BEGIN
--the sequence must be 0 as this is a root node
SET @Sequence = 0
--root nodes cannot exist already
IF (SELECT COUNT(Child_SVS_Name) FROM
[Archstore].[dbo].tbl_SVS_RELATIONSHIPS
WHERE Child_SVS_Name = @ServiceName AND
Parent_SVS_NAME is NULL) > 0
BEGIN
SET @ServiceName = CAST(@ServiceName + ' Already a root service' as
Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--the Parent name is null
SET @ParentName = NULL
END
-- if the parent Id is not null the parent name is retreived
-- (the parent id of the service that is being added is the
child_svs_id in the
-- svs relationships table, since we are passing the id of the
service we want
-- to add to)
ELSE IF @ParentRId IS NOT NULL
BEGIN
SET @ParentName = (SELECT Child_SVS_Name
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE Id = @ParentRId)
END
--Add the service relationship.
INSERT INTO [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
([Parent_SVS_Name]
,[Child_SVS_Name]
,[Sequence]
,[ErrorLevelId])
VALUES
(@ParentName, @ServiceName, @Sequence, @ErrorLevelId)
--return the new service id
SELECT Id AS ServiceRID
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE (Parent_SVS_Name = @ParentName AND
Child_SVS_Name = @ServiceName AND
Sequence = @Sequence)

END


Norman said:
Could show the SP code here.

Here is my guess: if somehow there are more than one "Select..." in the SP,
you may have to call RecordSet.NextRecordSet to find the record.

However, if you only return one row with one column, it is a single value,
isn't it? So, why not using a output parameter, instead of a recordset to
retrieve a single value from database?
Click to expand...
Click to expand...
 
N

Norman Yuan

Seeing your SP code, it seems the suggestion from Sylvain Lafontaine applies
to your code. However, I still recommend to use output parameter to retuen
single value back to client app instead of creating a RecordSet on the
client side
The SP Code is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------
-- sp_UI_AddServiceRelationship
-- INPUT: <@ParentRId> Parent Relationship Id
--
-- <@ServiceName> Child Service Name
--
-- <@Sequence> Sequence for the new relationship
-- The UI will set this to the last
-- sequence number in that parent.
--
-- <@ErrorLevelId> The error level for this relationship
-- if this is null then the default is used
-- for that service.
--
-- OUTPUT: ServiceRId The new relationship Id that was just created
--
-- All inputs must be specified
-- The child service name must exist in the tbl_SERVICES
-- If the @ParentRId is NULL (or not specified) then the parent name is
set to
-- NULL and the child is a root node. root nodes cannot already be in
the
-- table as a root node.
--
--
-- VERSION 0.1
ALTER PROCEDURE [dbo].[sp_UI_AddServiceRelationship]
(
@ParentRId Int = NULL, --Parent Relationship Id
@ServiceName Varchar(35) = NULL, --Child Service Name
@Sequence Int = NULL, --Relationship Sequence
@ErrorLevelId Int = NULL --Error level Id
)
AS
BEGIN
DECLARE @ParentName varchar(35) --The Parent name is needed in the
insert
--ensure the childname and the sequence are not null.
IF (@ServiceName is NULL OR @Sequence is NULL)
BEGIN
RAISERROR('@ChildName or @Sequence cannot be NULL',16,1)
Return -1
END
--childname must exist in the services table
IF (SELECT COUNT([Name]) FROM [Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName) != 1
BEGIN
SET @ServiceName = CAST(@ServiceName + ' does not exist as a Service'
as Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--set the error level
ELSE IF @ErrorLevelId is NULL
BEGIN
SET @ErrorLevelId = (SELECT DefaultErrorLevel FROM
[Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName)
END
--if the parent relationship id is null then the sequence must be 0
and the root
-- node cannot exist already
IF @ParentRId IS NULL
BEGIN
--the sequence must be 0 as this is a root node
SET @Sequence = 0
--root nodes cannot exist already
IF (SELECT COUNT(Child_SVS_Name) FROM
[Archstore].[dbo].tbl_SVS_RELATIONSHIPS
WHERE Child_SVS_Name = @ServiceName AND
Parent_SVS_NAME is NULL) > 0
BEGIN
SET @ServiceName = CAST(@ServiceName + ' Already a root service' as
Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--the Parent name is null
SET @ParentName = NULL
END
-- if the parent Id is not null the parent name is retreived
-- (the parent id of the service that is being added is the
child_svs_id in the
-- svs relationships table, since we are passing the id of the
service we want
-- to add to)
ELSE IF @ParentRId IS NOT NULL
BEGIN
SET @ParentName = (SELECT Child_SVS_Name
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE Id = @ParentRId)
END
--Add the service relationship.
INSERT INTO [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
([Parent_SVS_Name]
,[Child_SVS_Name]
,[Sequence]
,[ErrorLevelId])
VALUES
(@ParentName, @ServiceName, @Sequence, @ErrorLevelId)
--return the new service id
SELECT Id AS ServiceRID
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE (Parent_SVS_Name = @ParentName AND
Child_SVS_Name = @ServiceName AND
Sequence = @Sequence)

END


Norman said:
Could show the SP code here.

Here is my guess: if somehow there are more than one "Select..." in the
SP,
you may have to call RecordSet.NextRecordSet to find the record.

However, if you only return one row with one column, it is a single
value,
isn't it? So, why not using a output parameter, instead of a recordset to
retrieve a single value from database?
Click to expand...
Click to expand...
 
M

m.p.fletcher

Many thanks, I never got the recordset to work, but using an output
parameter did get me where I wanted. I hope I dont have the same
problem in future when I really do need access to the recordset.

Regards,
Marc

Norman said:
Seeing your SP code, it seems the suggestion from Sylvain Lafontaine applies
to your code. However, I still recommend to use output parameter to retuen
single value back to client app instead of creating a RecordSet on the
client side
The SP Code is as follows:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--------------------------------------------------------------------
-- sp_UI_AddServiceRelationship
-- INPUT: <@ParentRId> Parent Relationship Id
--
-- <@ServiceName> Child Service Name
--
-- <@Sequence> Sequence for the new relationship
-- The UI will set this to the last
-- sequence number in that parent.
--
-- <@ErrorLevelId> The error level for this relationship
-- if this is null then the default is used
-- for that service.
--
-- OUTPUT: ServiceRId The new relationship Id that was just created
--
-- All inputs must be specified
-- The child service name must exist in the tbl_SERVICES
-- If the @ParentRId is NULL (or not specified) then the parent name is
set to
-- NULL and the child is a root node. root nodes cannot already be in
the
-- table as a root node.
--
--
-- VERSION 0.1
ALTER PROCEDURE [dbo].[sp_UI_AddServiceRelationship]
(
@ParentRId Int = NULL, --Parent Relationship Id
@ServiceName Varchar(35) = NULL, --Child Service Name
@Sequence Int = NULL, --Relationship Sequence
@ErrorLevelId Int = NULL --Error level Id
)
AS
BEGIN
DECLARE @ParentName varchar(35) --The Parent name is needed in the
insert
--ensure the childname and the sequence are not null.
IF (@ServiceName is NULL OR @Sequence is NULL)
BEGIN
RAISERROR('@ChildName or @Sequence cannot be NULL',16,1)
Return -1
END
--childname must exist in the services table
IF (SELECT COUNT([Name]) FROM [Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName) != 1
BEGIN
SET @ServiceName = CAST(@ServiceName + ' does not exist as a Service'
as Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--set the error level
ELSE IF @ErrorLevelId is NULL
BEGIN
SET @ErrorLevelId = (SELECT DefaultErrorLevel FROM
[Archstore].[dbo].tbl_SERVICES
WHERE [NAME] = @ServiceName)
END
--if the parent relationship id is null then the sequence must be 0
and the root
-- node cannot exist already
IF @ParentRId IS NULL
BEGIN
--the sequence must be 0 as this is a root node
SET @Sequence = 0
--root nodes cannot exist already
IF (SELECT COUNT(Child_SVS_Name) FROM
[Archstore].[dbo].tbl_SVS_RELATIONSHIPS
WHERE Child_SVS_Name = @ServiceName AND
Parent_SVS_NAME is NULL) > 0
BEGIN
SET @ServiceName = CAST(@ServiceName + ' Already a root service' as
Varchar(100))
RAISERROR(@ServiceName ,16,1)
Return -1
END
--the Parent name is null
SET @ParentName = NULL
END
-- if the parent Id is not null the parent name is retreived
-- (the parent id of the service that is being added is the
child_svs_id in the
-- svs relationships table, since we are passing the id of the
service we want
-- to add to)
ELSE IF @ParentRId IS NOT NULL
BEGIN
SET @ParentName = (SELECT Child_SVS_Name
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE Id = @ParentRId)
END
--Add the service relationship.
INSERT INTO [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
([Parent_SVS_Name]
,[Child_SVS_Name]
,[Sequence]
,[ErrorLevelId])
VALUES
(@ParentName, @ServiceName, @Sequence, @ErrorLevelId)
--return the new service id
SELECT Id AS ServiceRID
FROM [Archstore].[dbo].[tbl_SVS_RELATIONSHIPS]
WHERE (Parent_SVS_Name = @ParentName AND
Child_SVS_Name = @ServiceName AND
Sequence = @Sequence)

END


Norman said:
Could show the SP code here.

Here is my guess: if somehow there are more than one "Select..." in the
SP,
you may have to call RecordSet.NextRecordSet to find the record.

However, if you only return one row with one column, it is a single
value,
isn't it? So, why not using a output parameter, instead of a recordset to
retrieve a single value from database?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot change the ActiveConnection property of a recordset object.... 1
Run a SQL Stored Procedure from VBA 0
Return Values from SQL Server to MS Access 1
Stored procedure recordset not opening 9
Return Values from SQL Server to MS Access 1
Access update query 2
Stored Procedure Based Recordset 2
Function not returning a recordset 5

Top