I am using cursors on my stored procedures. On the asp side, i use a Visual Basic Dll containing the following code for the database connection..
Function sp(ByVal SP As String, ParamArray params() As Variant) As ADODB.Recordset
Dim rs As ADODB.Recordset, cmd As ADODB.Command
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = ...
cmd.CommandText = SP
cmd.CommandType = adCmdStoredProc
'note : if i change this to cmd.CommandType = adCmdText then i get the syntax error message on the asp page:
'(0x80040E14)Syntax error or access violation
collectParams cmd, params
rs.CursorLocation = ...
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing
'return the damn recordset :(
Set sp= rs
Now in many cases i have seen that when i use the dll to run my stored procedures and return recordsets, the page breaks and i get the following error:
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.
Syntax error or access violation..
I get stuck on this error .. and in most of these stored procedures, I use either multiple select statements (not to return values, but to assign inner variables..)
for example : i use the following in a cursor..
set @Exists = (Select @myvar where @myvar in (Select myvarfield from [tablename] where <condition>
Inner Join table1 ON <condition>))
Query analyzer returns perfect values, but only my asp page breaks..and i have really got fed up of this error..have almost become paranoid of it ..
help me fast...please..SOS
DeEl In ThE DiGiTaLL WoRlD