Getting Schema Information From Any *.MDB , MSSQL or Other DataBase And Building a Dynamic Table. '

Adi Katz (2 Submissions) Active Server Pages ASP (Active Server Pages) Unknown Difficulty Fri 29^th March 2002 Mon 8^th February 2021

Getting Schema Information From Any *.MDB , MSSQL or Other DataBase And Building a Dynamic Table. 'Change DB Path In (strCn) ConnectionString

Rate Getting Schema Information From Any *.MDB , MSSQL or Other DataBase And Building a Dynamic Table. '

(2(2 Vote))


<%
Dim Cn,Rs,strCn,Sql,Tbl
    Tbl = Request.QueryString("Table") ' Get Table Name For Sql Statement
    strCn = "Driver=Microsoft Access Driver (*.mdb);" & _
			"DBQ=" & Server.MapPath("NWIND.MDB") 'Use Any DB	

private sub OpenConnect()
  set Cn = server.CreateObject("ADODB.Connection")
      Cn.Open strCn
  set Rs = Server.CreateObject("ADODB.Recordset")
End Sub

Private sub SchemaTable()
  set Rs = Cn.OpenSchema(20,Array(Empty,Empty,Empty,"Table"))'20 = adSchemaTable
  If Not Rs.EOF then call BuildMenu()
End sub

Private sub OpenRecset()
  Sql = "SELECT Top 6 * From [" & Tbl & "]" '[] if Table Name is [Order Details]
  Rs.Open Sql,Cn,0,1 ' ForwardOnly,ReadOnly 
  If Not Rs.EOF then call BuildTable()
End sub

private sub CloseAllRes()
 If IsObject(rs) then
    If Rs.State then
       Rs.Close()
       Set Rs = Nothing
    End if
 End if
 
 If Cn.State then  
    Cn.Close()
    set Cn = Nothing
 End if
End sub

'--- Starting Point ---' 
call OpenConnect()
call SchemaTable()
If Request.QueryString("Table") <> "" then
   Rs.Close() 
   Call OpenRecset()
End If
Call CloseAllRes()
'----------------------'
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<BaseFont size=3>
</HEAD>
<BODY>
<%private Sub BuildMenu()%>
  <Table Border> 
    <Form>
     <Tr>
      <TD>
        <SELECT NAME=Table
           OnChange="javascript:document.forms(0).submit();">
           <OPTION><%="<- -SELECT- ->"%></OPTION>
           <%Do While Not (Rs.EOF)%>  
           <OPTION><%=Rs.Fields("TABLE_NAME").Value%></OPTION>
           <%Rs.MoveNext
            loop%>
        </SELECT>
      </TD>
     </Tr>
    </Form>
  </Table> 
<%End sub%>

<%Private sub BuildTable()%>
  <table border>
  
    <%For Each H In Rs.Fields%>
      <th><%=H.name%></th>
    <%Next%>
    
    <%Do While Not (Rs.EOF)%>  
       <tr valign=top>
         <%For Each Tdata In Rs.Fields%>
           <td><%=Tdata.value%></td>
         <%Next%>
       </tr>
    <%Rs.MoveNext
      loop%>
  </table>
<%End sub%>
</BODY>
</HTML>

Download snippet Save to my list

Getting Schema Information From Any *.MDB , MSSQL or Other DataBase And Building a Dynamic Table. ' Comments

No comments yet — be the first to post one!

Getting Schema Information From Any *.MDB , MSSQL or Other DataBase And Building a Dynamic Table. '

Getting Schema Information From Any *.MDB , MSSQL or Other DataBase And Building a Dynamic Table. ' Comments

Post a Comment