ADO recordset null record displayed as 1

[Jon von der Heyden]

Hi

Another quirk I can't see reference to.

I am pulling a small recordset out of an Access database. It is a single field recordset. Some records are null. If I try and load the recordset into an array I get a mismatch error. The field is set as a text fields. On closer inspection I notice that the null value is converted into the number 1 (hence the mismatch).

I found out using:

?m_clsDB.RunScript(strSQL).GetString(adClipString,-1,"|",vbcrlf,vbnull)

(The RunScript function returns a recordset using the recordset open method.)

Which returned:

[B][COLOR="Red"]1[/COLOR][/B]
Development & Projects
IT
Local Buying
Logistics
Mobility
Professional Services

The #1 above is actually a null value in the recordset. Why is it being converted to #1?

 

[Jon von der Heyden]

Ah yes, I have to transpose it because GetRows returns a 2D array and records are in 2nd D. Rats! I think the work-around is to create a delimited string (GetString) and then split it into an array. Or can you think of a better way?

 

[Jon von der Heyden]

GetRows returns the columns as the first dimension, which is why you need to transpose. My preference is to use my own transpose function rather than Application.transpose which seems to be touchy about a lot of things.

Missed this! Thanks Rory. I get what you are saying about Transpose. Can you foresee any problems using string? As these will be distinct lists the strings will be well within character size scope. I'm using the arrays to populate listbox / combobox lists.

 

[RoryA]

If you're dealing with more than one record, I'd just loop through the lot and populate another array:

Function TransposeGetRows(varData) As Variant
   Dim lngRow As Long, lngCol As Long
   Dim varOut()
   ReDim varOut(1 To UBound(varData, 2) + 1, 1 To UBound(varData, 1) + 1)
   For lngRow = LBound(varData, 2) To UBound(varData, 2)
      For lngCol = LBound(varData, 1) To UBound(varData, 1)
         varOut(lngRow + 1, lngCol + 1) = varData(lngCol, lngRow)
      Next lngCol
   Next lngRow
  TransposeGetRows = varOut
End Function

 

[Jon von der Heyden]

Cool thanks Rory. I suppose it's worth having the function in my project rather than string and splitting each time I need to convert it.