Order of retrieving fields from SQL?

[!NoItAll]

I am retrieving data from an MS SQL database using SQL2000 on Windows XP. I am using VB6.
I am having a very odd problem with certain fields returning empty - when they are not.
Here is my code:

My Select statement is

Select id1, id2, id8, id14, id35, id38, id186, id5000, id5004 From header_record where record_id=346621
 

I then use the following code to assign the values into variables

Do While (rsObjectSet.BOF = False) And (rsObjectSet.EOF = False)
        dArcDate = ConvertToDate(Byte2Char(rsObjectSet.Fields("id1") & ""))
        dModDate = ConvertToDate(Byte2Char(rsObjectSet.Fields("id2") & ""))
        sAuthor = rsObjectSet.Fields("id186") & ""
        sSlug = rsObjectSet.Fields("id8") & ""
        sBody = rsObjectSet.Fields("id14") & ""
        sTimeCode = ConvertToHMS(Byte2Char(rsObjectSet.Fields("id35") & ""))
        sTapeLen = ConvertToHMS(Byte2Char(rsObjectSet.Fields("id38") & ""))
        sModby = rsObjectSet.Fields("id5000") & ""
        sTape = rsObjectSet.Fields("id5004") & ""
Loop
 

This code calls some functions (ConvertToDate, ConvertToHMS and Byte2Char) that are not really material to my problem...
But here is my problem - depending on the order in which I read the columns - certain columns will return empty - when there is data there! If I rearrange the order in which I execute these statements then different columns will return blank.
For example: in the code above I fill the values of sSlug and sBody, calling for sSlug first.

        sSlug = rsObjectSet.Fields("id8") & ""
        sBody = rsObjectSet.Fields("id14") & ""
 

With it that way then column id14 will return empty. If I simply reverse the order

        sBody = rsObjectSet.Fields("id14") & ""
        sSlug = rsObjectSet.Fields("id8") & ""
 

Then column id8 will return empty

Can anyone tell me what I am doing wrong?

[gpl]

Im not sure if this is relevant, but you do not have a movefirst call before your loop that builds up the values .. neither do you have a movenext at the bottom of the loop

My memories of using recordsets was that a movefirst was necessary before checking the eof/bof (and being booleans, you dont need to check for being equal to false, this would do

Do While (Not (rsObjectSet.BOF or rsObjectSet.EOF))

[!NoItAll]

You are correct - I simply left that out for brevity - I probably should have left the loop part out too. I do have a movefirst just before the loop and a movenext at the bottom (inside) the loop.
I don't believe the movefirst is strictly necessary - but it's there none-the-less.

I like your loop test - it's much more elegant! Thanks!

Des

[gpl]

This is bizarre behaviour, to say the least
I found this on experts-exchange *** Snip - Sorry but it's not ***
-- hope its ok to post external urls, but wanted it properly credited
Graham

quote:
Make sure the cursor type for the recordset is not forward only; use adOpenStatic:

rs.CursorType = adOpenStatic

If you don't have a constant defined for adOpenStatic, use the value instead, which is 3:

rs.CursorType = 3

I have had recordsets doing weird stuff when they're forward only, because you read the last field in the record and then can't go back and read an earlier field in the same record. I'm fairly sure, going from memory, that it didn't error, it just didn't get the correct value. Let me know if this helps...

Flynnious
/quote

[!NoItAll]

Thanks - this appear to not be the source of my issue. I do use adOpenForwardOnly but am aware I can only read the fields once. I do move everything into a structure from each field and work with the data from that structure. I did try adOpenStatic - but it was so slow I deemed it unusable. With forward only it only takes a few seconds to run the Select, with OpenStatic I gave up after an hour and a half...

[ck9663]

I think this is more of a VB6 issue than a sql-server issue...

Good Luck!!!

~~ CK

[NeoPa]

If the ForwardOnly open works much quicker for you, then why not consider working with it more closely. Find the order the fields are processed in, then access them in that order. That way you get the speed and the reliability.

[!NoItAll]

Thanks NeoPa - I think you may be putting me on the right track...

So when I create a select statement such as

select obje_id, obje_date, obje_text, obje_author, obje_appr from header_record where obje_type=2

Does that not also define the order in which I should access the fields? If not - is there a method to determine the processing order?

[NeoPa]

Possibly. I'm afraid I don't know that well enough to say.

What I can say though, is that a SELECT * should get all fields available without compromising any. From there, I would guess that the order the fields are defined in the record would be a good starting approach (for transferring the data to your variables). If that doesn't work first time, then try reversing it. Otherwise, trial and error is all I have, but there may be something else out there with more info on the matter. I've never come across this problem before. I'm just working deductively from what I do know.