We help IT Professionals succeed at work.

Updatable recordset (ADO + Oracle)

GoodJun
GoodJun asked
on
Hi. I have a program that is running before, but not working now.

Here is the code that get the error:

  Set rs = New Recordset
  strSQL = "SELECT * FROM tbl1 WHERE id='myname'"
  rs.Open strSQL, cnString, adOpenKeyset,    adLockOptimistic, adCmdText

after I execute the rs.Open command, I always got an updatable recordset, but I got a read-only recordset now.
After the open command, I go to the immediate window check the recordset property
?rs.locktype is 1 instead of 3 as I expected with adLockOptimistic.
?rs.cursortype is 3 and not the adOpenKeyset as I specified.

Can anybody shed some light here.

btw, I use ADO2.5 before, now changed to ADO2.6, when I find the problem, I switched back to ADO2.5, still have the same problem.
Thanks.
Comment
Watch Question

Commented:
You need to specify the cursor location.

Myconnection.cursorlocation = aduseclient

The default location is the server, and you can't change that on the fly.  Those are the server's default cursors and locks.

R.

Author

Commented:
I didn't use connection object explicitly. the cnSting in my rs.open statement is a connection string like this:
    chString = "User Id=" & MyID & _
            ";Password=" & MyPassword & _
            ";Provider=MSDAORA;Data Source=" & MyOracleServer & ";"

How can I set the cursor location to adUseClient in the connection string.

Author

Commented:
I set the rs.cursorlocation=adUseClient
before the open statement and it worked.
You will get the points with A.
One more thing, can you explain why it works before without
I put the rs.cursorlocation=adUseClient.

Commented:
I don't think that you can include that in your Connection String.  I suggest creating a connection object and setting it that way.  I tried adding it to the connection string, but it's a no go.  Still defaults.

Author

Commented:
I reason I don't use a connection object is that the code is in MTS enviroment, and I need the pooling manager to manage the creation of the connection object on the back ground. Thanks for your help. The thing still got me is why it works before without explictly set the asUseClient???? Any idea?

Commented:
My guess as to why it stopped working is when you installed ado2.6, it also updated you to the newest mdac drivers.  Probably a different Oracle ODBC driver.  If you were to use MS SQL as your server, you would also have to specify the cursor location.  

Explore More ContentExplore courses, solutions, and other research materials related to this topic.