The object you have entered is not a valid RecordSet Property 1

[Azita79]

Hi All,

I have been struggling with this error for 3 days now.I have a stored procedure as following:

Create procedure dbo.Search
(@BeginDate DateTime,
@EndDate DateTime)
As

DECLARE @vBeginDate NVARCHAR(20)
DECLARE @vEndDate NVARCHAR(20)
DECLARE @vStrSQL NVARCHAR(2000)

IF @BeginDate IS NOT NULL OR @EndDate IS NOT NULL
BEGIN
IF @BeginDate IS NULL
SELECT @BeginDate = MIN(A.ApprecdDate)
FROM WQOnsite.dbo.tblApplication A

IF @EndDate IS NULL
SELECT @EndDate = MAX(A.ApprecdDate)
FROM WQOnsite.dbo.tblApplication A

SET @vBeginDate = convert(VARCHAR(12), @begindate, 102)
SET @vEndDate = convert(VARCHAR(12), @EndDate, 102)
SET @vstrsql = 'SELECT * FROM tblApplication A where a.appRecddate between ''' +
@vBeginDate + ''' and ''' + @vEndDate + ''''

END

exec (@vStrSQL)

*********
when either of the prameters are Null I get an error message saying:

The object you have entered is not a valid RecordSet Property.

here is the VBA code:

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = m_cnOnsite
.Source = "Exec search '1/1/1992', NULL "
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open Options:=adCmdText
End With


The error occur on .Open


Any help would be greatly appreciated


Azita

 

[VicM]

Azita:

Apparently, the top part of your code is written in another language, possibly VB6. I'm not familiar with VB, but extensively use VBA.

What I'm seeing in the upper code is that you refer to each of the date variables with two different names:

@BeginDate and @vBeginDate

@EndDate and @vEndDate

Could this be the source of your problem?

HTH,

Vic

 

[Azita79]

the upper code is a SQL Stored Procedure. I am using Access unbound Forms with SQL 2000 backend.

 

[VicM]

Azita:

I understand your using the SQL procedure and accessing it from Access, but my question still stands:

Why are you referencing the date variables using two different names?

DECLARE @vBeginDate NVARCHAR(20)
DECLARE @vEndDate NVARCHAR(20)
DECLARE @vStrSQL NVARCHAR(2000)

IF @BeginDate IS NOT NULL OR @EndDate IS NOT NULL BEGIN
No v-^ No v-^
IF @BeginDate IS NULL ... etc.
No v-^

Or is this something peculiar to the SQL code?

Vic

 

[cmmrfrds]

In your stored procedure set nocount on

Set nocount on
DECLARE @vBeginDate NVARCHAR(20)

What is probably happening is that your sql string is not resolving to the correct syntax and you are receiving something back from the SP that is not a recordset. Check out your string.

You could add an output parm to your list of parms and put the string in it and then look at it in your vba code.

(@BeginDate DateTime,
@EndDate DateTime,
@myoutput varchar(200) as output) 'check this syntax since you may not need the 'as'.

select myoutput = @vstrsql

If one of the dates is null your string won't be created correctly.

 

[Azita79]

VicM, the @BeginDate and @EndDate are the parameters passed to the sporc and @vBeginDate and @vEndDate are local variables I declared to convert the IN parameters form datetime to nvarchar.

The if statement checks for the null values of the IN params.

 

[Azita79]

cmmffrds,

set nocount on did it, it works fine now. But I would like to know what nocount is doing?


Thank you so much for your help.


Azita

 

[Azita79]

I looked it up,

SET NOCOUNT
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

Syntax
SET NOCOUNT { ON | OFF }

 

[VicM]

Akita

Thanks Akita, now I understand.

Vic