Get Field Names

[vanillapod]

Hi, i have a crosstab query that uses peoples names as the column heading. I'm gonna use the select * From so that i get every column but i'm stuck on how to show the data on the page cuz one time there may be 2 names (coulms) next time there may be 22 columns etc.

So my question is, can i call the column name using something similar to:

recordset1.fields.item("ColumnName&quot.value

or is there any other way of doing it? Otherwise I won't know what the column anmes coming out of the crosstab are on my asp...


Cheers

 

[Tarwn]

I'm not sure why you have peoples names coming up as column headers, but...

You could loop through the fields of the first row in the recordset. A field object has a value and name property, so it would look somehting like this:

Response.Write recordset1.fields(i).name

-Tarwn

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]

http://www.tiernok.com/

The never-completed website

 

[jpbrassard]

You can simply spin through the Fields collection off your Recordset object, and if you need the individual Field (column) names for display purposes, you can grab those too, like so:

[tt]
Dim strValue, strDisplay
For i = 1 To recordset1.Fields.Count
strDisplay = recordset1.Fields(i).Name
If Not IsNull(recordset1.Fields(i).Value) Then
strValue = recordset1.Fields(i).Value
Else
strValue = ""
End If
Next
[/tt]

HTH,
jp

 

[Tarwn]

Hmm...I guess I could have supplied the For/Next loop for that i variable

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]

http://www.tiernok.com/

The never-completed website

 

[vanillapod]

Nice one guys cheers,

 

[jpbrassard]

Heh. The For/Next loop is what took me that few seconds longer...

But really, the For/Next should be surround by a Do/While for recordset1, so it should've taken me even longer...

HTH,
jp

 

[vanillapod]

I've typed it in as:

Dim strValue, strDisplay
For i = 1 To recordset1.Fields.Count -line 38
strDisplay = recordset1.Fields(i).Name
If Not IsNull(recordset1.Fields(i).Value) Then
strValue = recordset1.Fields(i).Value
Else
strValue = ""
End If
Next

however I'm getting line38 - item cannot be found in collection to the requested name or odinal.. am i missing something obvious?

 

[vanillapod]

my mistake, its saying it can't find

recordset1.fields(i).count
recordset1 is the name of my recordset though...

 

[Tarwn]

I don't see anythiong off the top of my head, but then again I have been writing documentation all morning, so anything not bright green and flashing tends to get by me today

you could try a For Each instead of using the count:

For Each f in recordset1.Fields
   Response.Write f.name
   'etc
Next

-Tarwn

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]

http://www.tiernok.com/

The never-completed website

 

[jpbrassard]

Tarwn's suggestion should work, or you could change the For/Next loop to start with 0 instead of 1 (according to the ADO 2.8 documentation at MSDN, the Fields collection is zero-based, not one-based):

[tt]
For i = 0 To recordset1.Fields.Count - 1
'// code in here should remain the same.
Next
[/tt]

Ideally, you should do as Tarwn first suggested - only get the Field names when you're on the first row of your recordset. To do that:

[tt]
Dim i, Ctr, strDisplay, strValue
Dim strDispHTML, strValHTML
Ctr = 0
Do While Not recordset1.EOF
For i = 0 to recordset1.Fields.Count
If Ctr = 0 Then
strDisplay = recodset1.Fields(i).Name
End If
If Not IsNull(recordset1.Fields(i).Value) Then
strValue = recordset1.Fields(i).Value
Else
strValue = " "
End If
If Ctr = 0 Then
If i = 0 Then
strDispHTML = &quot;<tr>&quot; & vbCr
End If
strDispHTML = strDispHTML & &quot;<td>&quot; & strDisplay & &quot;</td>&quot; & vbCr
If i = recordset1.Fields.Count - 1 Then
strDispHTML = strDispHTML & &quot;</tr>&quot;
End If
End If
If i = 0 Then strValHTML = strValHTML & &quot;<tr>&quot; & vbCr
strValHTML = strValHTML & &quot;<td>&quot; & strValHTML & &quot;</td>&quot; & vbCr
If i = recordset1.Fields.Count - 1 Then
strValHTML = strValHTML & &quot;</tr>&quot; & vbCr
End If
Next
Ctr = Ctr + 1
recordset1.MoveNext
Loop[/tt]

That's probably more of what you're looking for...

HTH,
jp