Show all tables in a DB, then Fieldnames

Td04 · #1 (**permalink**) 10-14-04, 10:27

I wonder if it is possible with ASP to first get all the Tables in a MDB into a recordset.
Then show the recordset (tablenames) as a in a dropdown, as critera for another SQL that shows the chosen tables Fieldnames as a new rs in a table.
Any ideas?
Thanks//M

rokslide · #2 (**permalink**) 10-14-04, 19:49

try this to get the tables...

Code:

set adoxConn = CreateObject("ADOX.Catalog") 
set adodbConn = CreateObject("ADODB.Connection") 
adodbConn.open ConnStr 
adoxConn.activeConnection = adodbConn 
for each table in adoxConn.tables 
  ' create your option entries for your select box
next

if you have a look at the help for the ADOX.Catalog component you should be able to find a way of getting field names.

Td04 · #3 (**permalink**) 10-18-04, 10:43

messed up a bit..se below

Td04 · #4 (**permalink**) 10-18-04, 10:45

Thanks for your help!.
I ended up doing it a bit different as it became alot of fields...
So instead I have a dropdown of the tables then when chosing that table I get a table with all the fields of that table..

Is there a way which I can put a critera that checks the occurencies of a certain fieldname?
as sth like
if objRS.Fields(iLoop).Name = "TheTable" then
msbbox("yes thats the one")
end if

Here is the code I use

<%@Language=VBScript %>

<%
fraga = request.Form("tabell")

' Create a connection object
Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "DSN=BR"
dim objRS
set objRS = server.createobject("ADODB.Recordset")

if fraga <> "" then
objRS.open ""& fraga &"",Conn, , ,adCmdTable
else
objRS.open "BSkyddsrumObjekt",Conn, , ,adCmdTable
end if

%>

<html>
<head>
<title>Untitled</title>
</head>

<body bgcolor="buttonface">
<center>
<table border="1" bgcolor="white">
<%
dim iLoop
for iLoop = 0 to objRS.Fields.Count -1
'Response.write "Name - " & _
'objRS.Fields(iLoop).Name & "<BR>"
%><tr><td><%=objRS.Fields(iLoop).Name%></td></tr>
<%
Next
objRS.Close
set objRS = Nothing
Conn.Close
set Conn = Nothing
%>
</table>
</center>
</body>
</html>

Td04 · #5 (**permalink**) 10-18-04, 11:11

ok It worked!

I used java instead..

dim iLoop
for iLoop = 0 to objRS.Fields.Count -1
'Response.write "Name - " & _
'objRS.Fields(iLoop).Name & "<BR>"
%><tr><td><%=objRS.Fields(iLoop).Name%></td></tr>
<%
if objRS.Fields(iLoop).Name = "Grupp" then
%><script>
alert("YES That is the one!!"); </script><%
end if

Next

rokslide · #6 (**permalink**) 10-18-04, 19:47

yeah, that will kinda work. I'm not sure why you want to do it quite like that but....

Td04 · #7 (**permalink**) 10-19-04, 01:59

now I start to wonder if there is a better way to search for a column name in a database...I look for a kommon key field in databases..

Chers//M

rokslide · #8 (**permalink**) 10-19-04, 02:02

it's unlikely.

Td04 · #9 (**permalink**) 10-19-04, 03:16

would´nt it be possible to shove more than one table in there?
with sth like?
objRS.open "Table1","Table2","table3",Conn, , ,adCmdTable

Seppuku · #10 (**permalink**) 10-19-04, 03:32

Quote:

Originally Posted by Td04

now I start to wonder if there is a better way to search for a column name in a database...I look for a kommon key field in databases..

Chers//M

I'm coming into this conversation late, but this depends on your database. The major DB servers have a master DB that contains all the the details regarding the other DBs, their tables, the columns in the tables, etc. Querying the master DB for such information can yield quite a bit of information. The only drawback is that this master DB would need to be SELECTable from the user account accessing the DB. This could be a security hole.

Seppuku · #11 (**permalink**) 10-19-04, 03:44

Quote:

Originally Posted by Td04

would´nt it be possible to shove more than one table in there?
with sth like?
objRS.open "Table1","Table2","table3",Conn, , ,adCmdTable

technically, no... each table is going to have it's own columns... you can't mix results from one table with another table unless the columns match or you do some special joins. Even if they did, you couldn't query it as described in your example. You'd have to do a SQL query using "adCmdText" and it would be something like "SELECT * FROM Table1, Table2". If you have some matching columns in the two tables, you could target just those columns like "SELECT column1, column2 FROM Table1, Table2"

Td04 · #12 (**permalink**) 10-19-04, 03:53

I have a planin old access DB. So How do I know which is the master table?
I have some systables in it..(se attachement.)

Seppuku · #13 (**permalink**) 10-19-04, 03:55

I dunno about MS Access.. but if I had to guess, it'd be MSysObjects

Td04 · #14 (**permalink**) 10-19-04, 03:58

no matching fields I am afraid, only a couple have that..

Td04 · #15 (**permalink**) 10-19-04, 04:46

I tried to get contact with the systables..no luck there
And with the earlier "open table to se fields app." do not opn them either so I dont know what the recordset would be..
Could I use a SQL sth like below or is there a special "SYS field" I can use as recordset.?

MinCon.Open "DSN=BR"
SQL = "SELECT ('*') as VOL From MSysObjects"
rs.Open SQL, MinCon
Do While Not rs.EOF
%>
<TR><TD><%=rs("VOL")%></td></TR>
<%
Loop
%>

Internet Services

Web Development

Digital Marketing

Consumer Tech