Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Question Unanswered: Show all tables in a DB, then Fieldnames

    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
    "Never underestimate a large number of morons"

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    messed up a bit..se below
    Last edited by Td04; 10-18-04 at 11:49.
    "Never underestimate a large number of morons"

  4. #4
    Join Date
    Mar 2004
    Posts
    52
    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 %>
    <!-- #include file="adovbs.inc"-->
    <%
    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>
    Last edited by Td04; 10-18-04 at 12:02.
    "Never underestimate a large number of morons"

  5. #5
    Join Date
    Mar 2004
    Posts
    52
    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
    "Never underestimate a large number of morons"

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    yeah, that will kinda work. I'm not sure why you want to do it quite like that but....

  7. #7
    Join Date
    Mar 2004
    Posts
    52
    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
    "Never underestimate a large number of morons"

  8. #8
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    it's unlikely.

  9. #9
    Join Date
    Mar 2004
    Posts
    52
    would´nt it be possible to shove more than one table in there?
    with sth like?
    objRS.open "Table1","Table2","table3",Conn, , ,adCmdTable
    "Never underestimate a large number of morons"

  10. #10
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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.
    That which does not kill me postpones the inevitable.

  11. #11
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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"
    That which does not kill me postpones the inevitable.

  12. #12
    Join Date
    Mar 2004
    Posts
    52
    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.)
    Attached Thumbnails Attached Thumbnails tables.gif  
    Last edited by Td04; 10-19-04 at 05:13.
    "Never underestimate a large number of morons"

  13. #13
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I dunno about MS Access.. but if I had to guess, it'd be MSysObjects
    That which does not kill me postpones the inevitable.

  14. #14
    Join Date
    Mar 2004
    Posts
    52
    no matching fields I am afraid, only a couple have that..
    "Never underestimate a large number of morons"

  15. #15
    Join Date
    Mar 2004
    Posts
    52
    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
    %>
    "Never underestimate a large number of morons"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •