Recordeset Fieldnames/values loop 
Author Message
 Recordeset Fieldnames/values loop

I have some ASP VBScript code which returns an entire (all fields) single
record from a db.

I would like to use it to display the results of this query in a 2 column
table, column 1 containing the field name and column 2 containing the value.

Currently I am hardcoding each  field name into the vbscript on outputting
them in turn. the code is as follows:

Quote

sql = "SELECT * FROM DataBook WHERE (AddressID = '" & Request("AddressID") &
"')"
Set db = Server.CreateObject("adodb.Connection")
db.Open "<connstring>"

Set rec = Server.CreateObject("adodb.Recordset")
rec.Open sql, db

  Response.Write "<h2>Databook search results</h2>"
  Response.Write "<table>"
 Response.Write "<tr>"
 Response.Write "<td>" & "CompanyName" & "</td>"
 Response.Write "<td>" & rec("CompanyName") & "</td>"
    Response.Write "</tr>"
 Response.Write "<tr>"
 Response.Write "<td>" & "Address1" & "</td>"
 Response.Write "<td>" & rec("Address1") & "</td>"
    Response.Write "</tr>"
 Response.Write "<tr>"
 Response.Write "<td>" & "Address2" & "</td>"
 Response.Write "<td>" & rec("Address2") & "</td>"
etc. etc
endQuote

This works ok, but it is not very flexible.

I would really like to be able to extract the field names from the recordset
into an array and then loop thorough the array outputting 2 <td>'s, one with
the field name and the second with the value.

Can anyone suggest some code that that might achieve this for me ?

My vbscript skills are quite minimal at present so I would much appreciate
it.

TIA

--
Feargal Hogan



Fri, 01 Jul 2005 19:17:43 GMT  
 Recordeset Fieldnames/values loop
Hi Feargal

You may want,

for each f in rec.fields
a=a & "<tr><td>" & f.name & "</td><td>" & rec(f.name) & "</td></tr>"
next

a="<table>" & a & "</table>"

Written inline.
--
Have a nice day.
Han Pohwan, Microsoft MVP, Korea


Quote:
> I have some ASP vbscript code which returns an entire (all fields) single
> record from a db.

> I would like to use it to display the results of this query in a 2 column
> table, column 1 containing the field name and column 2 containing the
value.

> Currently I am hardcoding each  field name into the vbscript on outputting
> them in turn. the code is as follows:

> Quote

> sql = "SELECT * FROM DataBook WHERE (AddressID = '" & Request("AddressID")
&
> "')"
> Set db = Server.CreateObject("adodb.Connection")
> db.Open "<connstring>"

> Set rec = Server.CreateObject("adodb.Recordset")
> rec.Open sql, db

>   Response.Write "<h2>Databook search results</h2>"
>   Response.Write "<table>"
>  Response.Write "<tr>"
>  Response.Write "<td>" & "CompanyName" & "</td>"
>  Response.Write "<td>" & rec("CompanyName") & "</td>"
>     Response.Write "</tr>"
>  Response.Write "<tr>"
>  Response.Write "<td>" & "Address1" & "</td>"
>  Response.Write "<td>" & rec("Address1") & "</td>"
>     Response.Write "</tr>"
>  Response.Write "<tr>"
>  Response.Write "<td>" & "Address2" & "</td>"
>  Response.Write "<td>" & rec("Address2") & "</td>"
> etc. etc
> endQuote

> This works ok, but it is not very flexible.

> I would really like to be able to extract the field names from the
recordset
> into an array and then loop thorough the array outputting 2 <td>'s, one
with
> the field name and the second with the value.

> Can anyone suggest some code that that might achieve this for me ?

> My vbscript skills are quite minimal at present so I would much appreciate
> it.

> TIA

> --
> Feargal Hogan



Fri, 01 Jul 2005 19:52:35 GMT  
 Recordeset Fieldnames/values loop
Perfect.

Thanks. Works great

--
Feargal Hogan

Quote:
> Hi Feargal

> You may want,

> for each f in rec.fields
> a=a & "<tr><td>" & f.name & "</td><td>" & rec(f.name) & "</td></tr>"
> next

> a="<table>" & a & "</table>"

> Written inline.
> --
> Have a nice day.
> Han Pohwan, Microsoft MVP, Korea



> > I have some ASP vbscript code which returns an entire (all fields)
single
> > record from a db.

> > I would like to use it to display the results of this query in a 2
column
> > table, column 1 containing the field name and column 2 containing the
> value.

> > Currently I am hardcoding each  field name into the vbscript on
outputting
> > them in turn. the code is as follows:

> > Quote

> > sql = "SELECT * FROM DataBook WHERE (AddressID = '" &

Request("AddressID")

- Show quoted text -

Quote:
> &
> > "')"
> > Set db = Server.CreateObject("adodb.Connection")
> > db.Open "<connstring>"

> > Set rec = Server.CreateObject("adodb.Recordset")
> > rec.Open sql, db

> >   Response.Write "<h2>Databook search results</h2>"
> >   Response.Write "<table>"
> >  Response.Write "<tr>"
> >  Response.Write "<td>" & "CompanyName" & "</td>"
> >  Response.Write "<td>" & rec("CompanyName") & "</td>"
> >     Response.Write "</tr>"
> >  Response.Write "<tr>"
> >  Response.Write "<td>" & "Address1" & "</td>"
> >  Response.Write "<td>" & rec("Address1") & "</td>"
> >     Response.Write "</tr>"
> >  Response.Write "<tr>"
> >  Response.Write "<td>" & "Address2" & "</td>"
> >  Response.Write "<td>" & rec("Address2") & "</td>"
> > etc. etc
> > endQuote

> > This works ok, but it is not very flexible.

> > I would really like to be able to extract the field names from the
> recordset
> > into an array and then loop thorough the array outputting 2 <td>'s, one
> with
> > the field name and the second with the value.

> > Can anyone suggest some code that that might achieve this for me ?

> > My vbscript skills are quite minimal at present so I would much
appreciate
> > it.

> > TIA

> > --
> > Feargal Hogan



Fri, 01 Jul 2005 21:25:23 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. FieldName = Fieldname + 1 possible in SQL?

2. Can ADO return Fieldname values?

3. rs!field vs. rs.fields(fieldname).value

4. sql strings and openning recordesets

5. delete recordeset

6. VBScript, MSOutlook, MSAccess, and recordesets

7. How can I loop and tier values

8. how to check a value in an array without looping

9. looping through checkbox values

10. Reset value of variables in a Select Case loop

11. Loop Through Tbl Values

12. Cout in a loop when updating a value!

 

 
Powered by phpBB® Forum Software