Populate drop down box with all field names from DB 1

[theScien]

I would like to dynamically populate a drop down box with all field names from a database, also the value of each new drop down box entry should also be the name of the field, somehow, just can't find a way to do it.

Also all searches produced no results.

I'm using ASP.

Thanks.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[QatQat]

Open your recordset, if you are using DSN

<%
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;YourDSN&quot; , &quot;root&quot;, &quot;password&quot;
%>

Example: to populate a list of countries from a table

create table countries
(
CountryID int primary key unsigned auto_increment
CountryName varchar (30)
);


<%
strSQL = &quot;select * from countries order by CountryName ASC&quot;
Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
RS.Open strSQL, Conn, 3, 3
%>



Now, in your page, create a select that will pass the CountryID but display the CountryName. WIth relational databases you must always avoid passing the proper countryname but always pass the ID.


<select name=&quot;CountryID&quot;>
<%

DO While not RS.EOF
ID = RS(&quot;CountryID&quot
Name = RS(&quot;CountryName&quot
%>
<option value=&quot;<%=RS(&quot;CountryID&quot%>&quot;><%= RS(&quot;CountryName&quot%></option>
<%
RS.Movenext
LOOP
RS.Close
Set RS=nothing
%>
</select>



Bye


Qatqat

Life is what happens when you are making other plans.

 

[theScien]

Thanks for answering but I know already how to pass values from tables to populate the drop down list, my question was how to populate a drop down list with the field names, or column names as they call it in MySQL.

Check here the topology:

Database.Table.Field.Value

I want the fields not the values.

The drop down values must be identical to the table field names, the name can be whatever I want, but the values must be the same as the table field names, so I can pass the selected value to a query for a search operation on that selected field only.

Thanks.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

To retrieve the field names, I would execute the query &quot;DESCRIBE <tablename>&quot; and process the return.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

DESCRIBE <tablename> ??

How do I process the return, in other words, how do I retrieve the field names, are they automatically associated to the recordset?

Thanks.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[QatQat]

I am not sure MyODBC will allow you to do that.

I have never tried it. I will try it myself and let you know.

Bye

Qatqat

Life is what happens when you are making other plans.

 

[QatQat]

Sleipnir214,

It does work but, as I supposed, not through ODBC.

If you use a dsnless connection however it works perfectly


<%
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open (&quot;Driver={MySQL}; SERVER=192.168.1.x; DATABASE=dbname; UID=root; PASSWORD=password&quot
%>



<%
strSQL = &quot;describe tablename&quot;
Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
RS.Open strSQL, Conn, 3, 3
%>


<%DO WHILE NOT RS.EOF %>

<%=RS(&quot;Field&quot%> , <%=RS(&quot;Type&quot%>, etc...

<%
RS.movenext
Loop
RS.Close
Set RS = Nothing
%>


will display fields and related information.

TheScien, I think this answers your question.


Bye

Qatqat


Life is what happens when you are making other plans.

 

[theScien]

Hey, it worked out fine, however, it associates all fields to <%= RS(&quot;Field&quot %>, how do I get individual fields?

I tryied to create an array, but it failed.

Thanks.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[sleipnir214]

The information about each field is returned in individual rows.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[theScien]

Actually, it comes out all in one big list, like this: fieldNAME1 fieldNAME2 fieldNAME3 etc... To get all field names associated to independent variables I have created an array, and it works fine, here's the code for anyone who might need it:

<%
Dim nx(30), i
i = 0

Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=dbNAME; UID=userNAME; PWD=userPASS;&quot;
Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot

strSQL = &quot;DESCRIBE tableNAME&quot;
Set RS = Conn.Execute(strSQL)

Do while Not RS.EOF
nx(i) = RS(&quot;Field&quot 'Could also use (Type, Null, Key, Default and Extra)
%>
Field: <%= i %> - <%= nx(i) %><BR>
<%
i = i + 1
RS.MoveNext
Loop

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing
%>

<BR><BR>

Selected filed name (4): <%= nx(4) %>

Special thanks to QatQat, you are getting a star for it.

Thanks once again to all that have helped.

If you haven't heard of it, then you most likely don't need it.
---------------------------------------------------------------------

http://www.mylittlehost.com

---------------------------------------------------------------------