SuperMario
asked on
Using ADO to retrieve field captions
Some code:
<%
Dim rs, conn
Set rs = Server.CreateObject("ADODB .Recordset ")
Set conn = Server.CreateObject("ADODB .Connectio n")
conn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("database.m db")
conn.Open
rs.Open "SELECT * FROM MyTable", conn, 2, 3
' If I wanted to retrieve the field NAMES I would do this
For Each fld In rs.Fields
Response.Write fld.Name & "<br>" & vbCr
Next
' But I want to retrieve the field's
' caption or description!
%>
So in design view my database looks like this:
Field ------ Type ------ Description
Username --- Text ------ The username on this account
Password --- Text ------ The password matching username
I want my for loop to output "The username on this account" and "The password matching username" instead of Username and Password.
Thanks in advance.
Dan
<%
Dim rs, conn
Set rs = Server.CreateObject("ADODB
Set conn = Server.CreateObject("ADODB
conn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("database.m
conn.Open
rs.Open "SELECT * FROM MyTable", conn, 2, 3
' If I wanted to retrieve the field NAMES I would do this
For Each fld In rs.Fields
Response.Write fld.Name & "<br>" & vbCr
Next
' But I want to retrieve the field's
' caption or description!
%>
So in design view my database looks like this:
Field ------ Type ------ Description
Username --- Text ------ The username on this account
Password --- Text ------ The password matching username
I want my for loop to output "The username on this account" and "The password matching username" instead of Username and Password.
Thanks in advance.
Dan
This feature in access in not supported in all other databases. I don't think ado has any builtin capabilities to read this property of a field. You might look into dao which is more access specific, but not optimized for web usage.
John
John
Use this.
<%
Dim rs, conn
Set rs = Server.CreateObject("ADODB .Recordset ")
Set conn = Server.CreateObject("ADODB .Connectio n")
conn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("database.m db")
conn.Open
rs.Open "SELECT * FROM MyTable", conn, 2, 3
Set Fields = rs.Fields
' If I wanted to retrieve the field NAMES I would do this
For Each fld In Fields
Response.Write fld.Name & "<br>" & vbCr
Next
' But I want to retrieve the field's
' caption or description!
%>
<%
Dim rs, conn
Set rs = Server.CreateObject("ADODB
Set conn = Server.CreateObject("ADODB
conn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("database.m
conn.Open
rs.Open "SELECT * FROM MyTable", conn, 2, 3
Set Fields = rs.Fields
' If I wanted to retrieve the field NAMES I would do this
For Each fld In Fields
Response.Write fld.Name & "<br>" & vbCr
Next
' But I want to retrieve the field's
' caption or description!
%>
Actually it is do-able. I was answering a same question in SQLServer, but mgfranz came up with his briliant idea of using Ole db. Check this
Use COLUMN_SIZE,with COLUMN_NAME and TABLE_NAME
<%
Set conn = Server.CreateObject("ADODB .Connectio n")
conn.ConnectionTimeout = 15
conn.CommandTimeout = 10
conn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & "Data Source=" & Server.MapPath("data/datab ase.mdb")
Set rstSchema1 = conn.OpenSchema(4)
Do Until rstSchema1.EOF
Response.Write "<BR>Table name : " & rstSchema1("TABLE_NAME") _
& vbCrLf & ", Column Name : " & rstSchema1("COLUMN_NAME") & vbCrLf
rstSchema1.MoveNext
Loop
rstSchema1.Close
conn.Close
%>
Use COLUMN_SIZE,with COLUMN_NAME and TABLE_NAME
<%
Set conn = Server.CreateObject("ADODB
conn.ConnectionTimeout = 15
conn.CommandTimeout = 10
conn.Open "Provider=Microsoft.Jet.OL
Set rstSchema1 = conn.OpenSchema(4)
Do Until rstSchema1.EOF
Response.Write "<BR>Table name : " & rstSchema1("TABLE_NAME") _
& vbCrLf & ", Column Name : " & rstSchema1("COLUMN_NAME") & vbCrLf
rstSchema1.MoveNext
Loop
rstSchema1.Close
conn.Close
%>
jitjanguy is right and should get points, however he neglected to put in his code the statement I think you are most interested in:
Response.write rstSchema1("DESCRIPTION")
As far as I have seen this only works in Access. Populating the DESCRIPTION is optional for OLEDB providers, however, both OLEDB for ODBC and native JETOLEDB drivers seem to support it just fine.
regards,
Dave Kawliche
http://AccessHelp.net
http://1ClickDB.com
Response.write rstSchema1("DESCRIPTION")
As far as I have seen this only works in Access. Populating the DESCRIPTION is optional for OLEDB providers, however, both OLEDB for ODBC and native JETOLEDB drivers seem to support it just fine.
regards,
Dave Kawliche
http://AccessHelp.net
http://1ClickDB.com
ASKER
Thanks Access...
That's precisely what I need.
Who do you guys think deserves the points?
-Dan
That's precisely what I need.
Who do you guys think deserves the points?
-Dan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AccessHelpNet or robbert, how about the "Caption"? I can't get the caption. "COLUMN_NAME" gives me the column name and not the caption. I found this question using search.
hongjun
hongjun
ASKER