mysql value once

[reubenb]

hi

i recently changed servers and before i could get a record more than once from a database, but now i can only get it the first time, then it returns Null.
as in following below i would do objrs("field") once, then if i do it for the same field again, it returns null.

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open SQL, objCon, adOpenForwardOnly, adLockReadOnly, adCmdText


i know i should be making it into a variable if im using it more than once
but is this a setting or what?

thanks

[reubenb]

Nevermind - figured it out

Just changed adOpenForwardOnly to adOpenDynamic for any one who find this issue too

bit weird that it happened all of a sudden, though.??

[Old Pedant]

Not weird. Documented. It's because one of your form fields is a long VARCHAR field (over 255 characters).

What happens is that internally ADO uses "getchunk" to get all the pieces of the long field, but when it does so it has to essentially "move past" the other data in the record. With a forwardonly cursor, it can't come back and get the rest of the data. With a static or dynamic cursor, it can.

But the right answer, anyway, is to ALWAYS get the values from your fields ONE TIME ONLY. It's *MUCH* more efficient.

When you do RS("fieldname"), ASP has to ask ADO to do a lookup-by-name, which involves a hashtable operation. Some people go to the extreme of using ADODB.Recordset.GetRows or to using field numbers, just to avoid this lookup time. And it can make a measurable difference. I'm not that fanatic, but I *do* try to only lookup each field by name one time and one time only, storing the value into a local VBScript variable. Retrieving a value from a VBS variable has essentially zero overhead and is much faster than doing RS("xxx") again. So I would count the fact that you need to do objrs("field") more than once to be an error, of sorts.

[reubenb]

I meant that it was weird that the code was the same, and then I changed servers and this issue occured.

Any idea why?

Thanks for the explanation though, really helpful.

[Old Pedant]

Probably a difference in the version of ADO in use. I had heard that this was changed in ADO version 2.7 or 2.8, but never had a need to test it out. Possibly when you changed servers you actually went back a version in ADO??

Oh...or might be the versions of MyODBC you are using.