frustrated about ADODB.connection

[lok1234]

Dear all,

I just saw this statement in microsoft msdn:

"Only a setting of adOpenStatic is supported if the CursorLocation property is set to adUseClient. If an unsupported value is set, then no error will result; the closest supported CursorType will be used instead." (source: http://msdn.microsoft.com/en-us/library/ms677593(VS.85).aspx)

But i always code like that:

  call fn_SqlOpenRS(aRs, aStrsql, adCmdText, gConnect_Main, adUseClient, adOpenForwardOnly)

OR

Call fn_SqlOpenRS(aRs, aStrsql, adCmdText, gConnect_Main, adUseClient, adOpenDynamic)

(p.s. fn_SqlOpenRs is a function that written by myself to pass the cursorlocation and cursortype to a open the recordset)


Is there any harmful mistake?

[FunkyDexter]

You probably haven't done anything particularly harmful.

The point is that AdUseClient forces the cursor to be maintained on the client rather than the server. It means that the data will be read from the DB and returned as a block to the client. If another user then updates one of the records the update won't show in the cursor unless you refresh the cursor from the database.

A dynamic cursor is one that updates automatically another user updates a value in the underlying data. That can only happen if the cursor is being maintained on the server.

A dynamic cursor is preferable from a data integrity point of view but it's MUCH hungrier in terms of resource and most of us avoid them for that reason. Generally we'll use a static cursor and live with the fact that this means the last update made will override any others.

Edit> You might want to avoid using a static cursor if you're going to work in the way you were in your other thread. In that thread you were reading a bunch of records from table A and then cycling through them and inseting their values into table B. If another user deleted a record from table A while you were working you'd end up with different values in table A than table B. Realistically that's rarely a problem but if it is then you'd want a dynamic cursor. Alternatively you could use the Insert Into TableB ... Select From TableA... structure I posted in that thread in which case it won't matter.

[lok1234]

You probably haven't done anything particularly harmful.

The point is that AdUseClient forces the cursor to be maintained on the client rather than the server. It means that the data will be read from the DB and returned as a block to the client. If another user then updates one of the records the update won't show in the cursor unless you refresh the cursor from the database.

A dynamic cursor is one that updates automatically another user updates a value in the underlying data. That can only happen if the cursor is being maintained on the server.

A dynamic cursor is preferable from a data integrity point of view but it's MUCH hungrier in terms of resource and most of us avoid them for that reason. Generally we'll use a static cursor and live with the fact that this means the last update made will override any others.

Edit> You might want to avoid using a static cursor if you're going to work in the way you were in your other thread. In that thread you were reading a bunch of records from table A and then cycling through them and inseting their values into table B. If another user deleted a record from table A while you were working you'd end up with different values in table A than table B. Realistically that's rarely a problem but if it is then you'd want a dynamic cursor. Alternatively you could use the Insert Into TableB ... Select From TableA... structure I posted in that thread in which case it won't matter.

thanks
Would you mind please explain more the red sentence that highlight above ? coz i am not fully understand.

Further questions:
1. In case i do something like that:

Step 1) gConnect_Access.begintrans
Step 2) strSQL=Update TableA as A set A.amount=100 (original value is 10)
Step 3) gConnect_Access.execute strSQL
Step 4) Open recordset by adUseClient with dynamic cursor which loops TableA
Step 5) What will be the value of aRs!amount as in Step3? Will it be 100 or 10? Under what circumstance will its value be 10 and 100 respectively?
Step 6) gConnect_Access.committran

2. Why sometimes after open an recordset, the aRs.recordcount will return -1 but sometimes return correct recordcount?

3. Actually, will begintrans and committrans affect the use of cursorlocation and cursortype?

Thank you

[FunkyDexter]

Would you mind please explain more the red sentence that highlight above ?

Imagine you have two users, UserA and UserB. Both select a particular customers name from a database table. The customer is called 'Joe'. When they read it, they both see 'Joe'. UserA now changes the name to 'Bob'. If userB is using a static(Client Side) cursor, he will not see that change. He therefore decides to change it from 'Joe' (which is what it still looks like to him) to 'Frank'. At this point UserB's change will overwrite UserA's change and the name will read 'Frank'. There. won't be anything obvious to indicate that UserA ever changed it to 'Bob'.

Actually, I was a bit missleading in my last post because this will apply to a dynamic cursor as well. The only difference is that UserB would immediately see UserA's change. He would know that UserA had changed the name to 'Bob' and that might change his decision to change it to 'Frank'. Generally, though, UserB won't be looking at the record, he'll be looking at a form you've designed that took the value from the record and wrote it to a text box. Meaning that, unless he refreshes his screen he stioll won't see 'Bob'. That's why most of us would just stick with a Client Static cursor and not worry too much about whose update finally gets used.

In answer to your further questions:-
1. Any change you make in a transaction doesn't actually happen until the Commit. So until Step 6 nobody, either you or other users, would see a value of 100. If you hadn't opened a transaction the update would be immediate.

The point of a transaction when used in this way is that ensures evrything happens or nothing does. This is called 'Atomicity' and can be quite important.

2. This one comes up alot and is due to the way ADO works. If you open up a server side cursor the system doesn't actually retrieve the records. It just grabs a pointer to the start of the recordset. (It does this to improve performance). That means when you check the recordcount it reads -1 because it hasn't counted the records yet. If you want it to give a meaningful record count you either need to open a client side cursor or you can issue MoveLast. Because Move Last forces the cursor to work it's way through the recordset it counts the records as it goes and you'll then be able to get a sensible count.

3. No. They start and end a transaction so none of the updates will actually take effect until the transaction is committed. If you rollback the transaction then none of the updates will happen at all. The decision of whether to use a client or server cursor is about whether you want to see changes OTHER users make. The decision about whether to use a transaction is about whether you want to make sure all your updates work or none do.



edit> Lok, from your recent questions you're obviously trying to get to grips with the ADO parameters. That's a really good thing and a full understanding of them will be really valuable to you. They are quite complex though and asking a series of questions is probably going to take forever. I'd recommend you have a good read of this page in our tutorials section which gives a really good explanation. Once you've gone through that ask any questions you've still got and we'll be happy to help. Realisticaly, this tutorial will be able to give you a much better understanding than I can.

[lok1234]

Imagine you have two users, UserA and ...

In answer to your further questions:-
1. Any change you make in a transaction doesn't actually happen until the Commit. So until Step 6 nobody, either you or other users, would see a value of 100. If you hadn't opened a transaction the update would be immediate.
....

very detail, thanks
one more question, in case i am in a transaction:

begintrans
'... do something here
update TableA set amount = 100 where company='ABC'

'... do something here....

' then open recordset to loop tableA
f_openSQLrs = "select company, amount from TableA"
While not aRs.eof
   'At this moment, i want amount of Company ABC is 100 .. How to do that?
   ars.movenext
wend

Update TableB .... 
committrans

[dee-u]

Can it not be "select company, amount from TableA WHERE Amount = 100"?

[lok1234]

Can it not be "select company, amount from TableA WHERE Amount = 100"?

Actually, i just want to know can the updated values immediately be reflected within a begintrans and commitran?

begintrans
update TableA... set amount=100

then, select amount from TableA,
will the aRs!Amount=100?

commitrans

[lok1234]

further question:
i have 4 mshflexgrids to display data in a form.

The speed is very slow. Is it due to the fact that i am using adopendynamic?

Set aRs = New ADODB.Recordset
Call f_SqlOpenRS_flexgrid(aRs, "select ... from...where...", adCmdText, pConnect, adUseClient, adOpenDynamic)
Set pflexgrid.DataSource = pRs

Should I use adUseServer, adOpenforwardonly instead?

Thanks

[si_the_geek]

adUseClient makes it slower, and so does adOpenDynamic.

For advice on which to use in each situation, see the FAQ article What do the parameters of the recordset.Open method mean?

adUseServer and adOpenForwardOnly are probably the right options in this case, but we can only make an educated guess as we can't see your actual code.