recordset via cmd object

[smk23]

I would like to return a recordset using a command object:

Set mCmd = New ADODB.Command
With mCmd
.ActiveConnection = mConn
.CommandText = brSelectCommandText
.CommandType = adcmdStoredProc
Set mrst = New ADODB.Recordset
Set mrst = .Execute

When I run this, it returns the error "syntax error or access violation" on
the Execute line. The connection is checked prior to this and confirmed open.
When I run the text in .CommandText from Query Analyzer (SQL server 2000),
there is no problem (returns the recordset). WHat can I do now to find the
error?

Sam

 

[Dirk Goldgar]

I would like to return a recordset using a command object:

Set mCmd = New ADODB.Command
With mCmd
.ActiveConnection = mConn
.CommandText = brSelectCommandText
.CommandType = adcmdStoredProc
Set mrst = New ADODB.Recordset
Set mrst = .Execute

When I run this, it returns the error "syntax error or access violation"
on
the Execute line. The connection is checked prior to this and confirmed
open.
When I run the text in .CommandText from Query Analyzer (SQL server 2000),
there is no problem (returns the recordset). WHat can I do now to find the
error?

From what I can see, that code ought to work, provided that you have
properly declared the objects and previously opened the connection mConn --
though it doesn't make sense to set mrst to a new ADODB recordset, and then
immediately discard that recordset for the one returned by the Execute
method. You don't need the line, "Set mrst = New ADODB.Recordset".

Is that a direct copy/paste from your module, though? I ask because the
constant "adcmdStoredProc" isn't capitalized correctly, suggesting the
possibility that you may have redefined it.

If that's not the problem, can you verify that brSelectCommandText is the
name of a stored procedure in the connected database?