RecordSet .open error

[Azita79]

Hi All,

I am using VB 6 and SQL server 2000. I have a sproc with a DateTime IN Parameter. I am using a Recordset, pass the parameter to sproc and execute it. Here is the Error I get when I execute the .open line:
" The object you entered is not a valid Recordset property"

However when I copy the sql statement in the VB code and run it in the query Analyzer, it runs fine.


Any help would be greeeeeaaaatly appreciated.

Azita

 

[rorubin]

How about commandType = adCmdText or
commandType = 1

 

[Azita79]

Hi rubin,

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = m_cnOnsite
.Source = strSQL
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open Options:=adCmdText
End With

and strSQL is something like "Exec sproc_Search '1/1/1992' "

and sproc_search:

create sproc_search(@beginDate DateTime) AS ....

Any thoughts anyone???

 

[Azita79]

Create procedure sproc_Search ...

 

[rorubin]

Remove Exec

 

[rorubin]

Hold on.

 

[rorubin]

Yeah get rid of the Exec.

 

[Azita79]

rubin,

I removed the Exec, but still getting the same error!

 

[mattKnight]

Does sproc_search(@beginDate DateTime) return a recordset?
Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[Azita79]

yes it does. It returns a RecordSet

 

[rorubin]

Try this:


Set rst = New ADODB.Recordset
With rst
.ActiveConnection = m_cnOnsite
.Source = strSQL
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.Open , , , , adCmdText
End With

In addition take a look at ADODB.Command

And if all else fails try using the ADODC

 

[Azita79]

still getting the same Error.

 

[rorubin]

Did you try the adodc?

 

[Azita79]

Thanks guys,

The error was in my Stored Procedure

I had to SET NOCOUNT ON

Azita