search option...error 3219 RESOLVED

[pame1la]

when a user performs the search on my system for the second time i get error "3219 Operation not allowed in this context".... and has an error with the following code when i trying to create the rs for the second time

With rs
If .State = adStateOpen Then
.Close - has an error with this line
End If
.CursorLocation = adUseClient
rs.Open str, cnnSV, adOpenDynamic, adLockOptimistic
End With

I dont understand why its got problem with this line, I have tried introducting rs.Close straight after I retrieve the data from the rs but it has a problem with that 2

WOT AM I DOIN RONG????

[smartrajesh]

if the operation is invalid. it through this error number "3219". so go through ur code once again. u might be open a connection before close it. so go throuhg ur code .....

Regards
Rajesh

^ ^
* *
O

[pame1la]

i know i dont close the rs that holds the result of the search... but when i try to close it by adding rs.Close in the code, it appears with the same error but at the new added line!!!

With rs
If .State = adStateOpen Then
.Close
End If
.CursorLocation = adUseClient
rs.Open st, gcnExcalibur, adOpenDynamic, adLockOptimistic
End With

doesnt the first three lines of this code mean, if the state of the connection is open then close it???? (it errors on the third line - .Close)
PLEASE HELP

[hyousuf2]

this might be cause of several reasons... u may have closed the rs n r trying to re-close it n same goes for the connection object as well, also if ure trying to open the rs n have forgotten to mention the connection object(the second parameter) n then u'll also get the same error... go thru ur code again

[pame1la]

it seems to work sometimes and sometimes causes the error...any ideas???

[hyousuf2]

wat seems to work sometimes n sometimes not ?!?!

[smartrajesh]

sorry,
i am little bussy...this is my code...(i am just checked out)

rs.Open "select * from tablename", Db, adOpenDynamic, adLockOptimistic
If rs.State = adStateOpen Then rs.Close
rs.CursorLocation = adUseClient
rs.Open "select * from tablename", Db, adOpenDynamic, adLockOptimistic

MsgBox rs.RecordCount


this is code working perfectly .. so i think error on ur code. so again go through it.

bye..
Regards
Rajesh
^ ^
* *
()

[pame1la]

i dont get it! why would it be trying to close if its already closed:

If .State = adStateOpen Then
.Close


that if statements only close if it is in state open??!!!!

& how *** it wrks sumtimes and not sumtimes!!!!

[pame1la]

wat seems to work sometimes n sometimes not ?!?!

what I mean is sometimes when I run the application it allows you to perform the search as many times as you like and doesnt have any problem!

but then sometimes, anywhere I have

With rs
If .State = adStateOpen Then
.Close

it comes up with that error with line 3

[Hack]

Do you set it to Nothing each time you close it?

Perhaps there are times when it is still active in memory even though it has been closed.

[smartrajesh]

yes pame... i am closed the rs when the rs is open. see my code i mentioned in if statement.
if rs.state=adopenstate then rs.close
here the if conditions is become true then only rs will close the current recordset and open a new one..

now i am not get u .. why ur code throughing an error sometimes. in vb one morething is some times the recordcount of a recordset will return a value -1. even the recordset having a records.. till i am not know the reason.. same situation now ur facing......


bye...

[hyousuf2]

yea it must be coz that sometimes the OS still has some threads wid ur rs thats y
try adding these line after rs.close
set rs = nothing
rs.open Query, connection, ............

[pame1la]

set rs = nothing
rs.open Query, connection, ............

yeah but it doesnt get to that line cos its got error with line 3

With rs
If .State = adStateOpen Then
.Close
Set rs = Nothing
End If
.CursorLocation = adUseClient
rs.Open str, gcnExcalibur, adOpenDynamic, adLockOptimistic
End With

[smartrajesh]

will u post ur code from the connection string....