by Michael Highguy (7 Submissions)
Category: Active Server Pages
Compatability: ASP (Active Server Pages)
Difficulty: Unknown Difficulty
Originally Published: Fri 14th June 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Universal SQL Query Builder. Read *.mdb Files from directory and build any Query You want.
VBcoders
<%dim Rs, Cn, Sql, Cnn, MyFso, MyFolder
set Cn = Server.CreateObject("ADODB.connection")
set Rs = Server.CreateObject("ADODB.recordset")
if Request.QueryString("dbname") <> "" then Session("dbname") = Request.QueryString("dbname")
if Request.QueryString("tblname") <> "" then Session("tblname") = Request.QueryString("tblname")
if Request.QueryString("fld") <> "" then
Sql = "SELECT TOP " & Request.QueryString("howmany") & " " & Request.QueryString("fld") & " FROM [" & Session("tblname") & "]"
if Request.QueryString("orderBy") <> "" then Sql = Sql & " ORDER BY [" & Request.QueryString("orderBy") & "]"
end if
private sub HowMany
if Session("dbname") <> "" and Session("tblname") <> "" then%>
<select id=howmany name=howmany>
<%for i = 5 to 20%>
<option><%=i%></option>
<%next%>
</select>
<%end if
end sub
private sub BuildCheckBoxes
if Session("dbname") <> "" and Request.QueryString("tblname") <> "" then
Rs.Close
Rs.Open "SELECT TOP 1 * FROM [" & Session("tblname") & "]"
for each Item in Rs.Fields%>
<INPUT type="checkbox" id="<%=Item.Name%>" name="<%=Item.Name%>" onclick="analize()"><%=Item.Name%><br>
<%next
end if
end sub
private sub GetTables
if Session("dbname") <> "" then
Cnn = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("data\" & Session("dbname"))
Cn.Open Cnn
set Rs = Cn.OpenSchema(20,Array(empty,empty,empty,"Table"))%>
<SELECT name=tblname id=tblname onchange="javascript:window.main.submit()">
<option></option>
<%while not Rs.EOF%>
<option><%=Rs.Fields("TABLE_NAME").Value%></option>
<%Rs.MoveNext
wend%>
</SELECT>
<%end if
end sub
private sub GetDBFiles
dim TempFileName
set MyFso = Server.CreateObject("Scripting.FileSystemObject")
set MyFolder = MyFso.GetFolder(Server.MapPath("data\"))%>
<SELECT name=dbname id=dbname onchange="javascript:window.main.submit()">
<option></option>
<%for each Item in MyFolder.Files
TempFileName = LCase(Item.Name)
if Right(TempFileName,3) = "mdb" then%>
<option value=<%=TempFileName%>><%=Left(TempFileName,InStr(1,TempFileName,".mdb")-1)%></option>
<%end if
next%>
<SELECT>
<%end sub%>
<HTML>
<HEAD>
<SCRIPT LANGUAGE=javascript>
<!--
function send() {
str = ""
Count = window.main.elements.length
for (i = 0; i < Count; i++)
if (window.main.elements[i].type == "checkbox" && window.main.elements[i].checked)
str += (str=="") ? "[" + window.main.elements[i].name + "]" : ",[" + window.main.elements[i].name + "]"
if (str=="") alert("You did not choose anything!")
else {
window.main.fld.value = str
window.main.submit()
}
}
function analize() {
str = ""
Count = window.main.elements.length
for (i = 0; i < Count; i++)
if (window.main.elements[i].type == "checkbox" && window.main.elements[i].checked)
str += "<INPUT type=radio id=orderBy name=orderBy value=" + window.main.elements[i].name + ">" + window.main.elements[i].name + "<br>"
insrd.innerHTML = str
}
//-->
</SCRIPT>
</HEAD>
<BODY>
<FORM method=get id=main name=main>
<table border=1 align=center id=tab name=tab>
<tr bgcolor=Silver>
<td>
<b>Select DB file:</b>
<%call GetDBFiles()%>
</td>
<td>
<b>Select the table:</b>
<%call GetTables()%>
</td>
<td>
<b>How many records to show:</b>
<%call HowMany()%>
</td>
</tr>
<tr bgcolor=Gainsboro>
<td>
<b><u>Choose fields to show:</u></b><br>
<%call BuildCheckBoxes()%>
</td>
<td align=center valign=middle><b>Easy DB Explorer<br>by Michael Highguy</b></td>
<td valign=top>
<b><u>Choose field to order by:</u></b><br>
<span name=insrd id=insrd></span>
</td>
</tr>
<%if Session("dbname") <> "" and Session("tblname") <> "" then%>
<tr><td colspan=3 align=center><INPUT type="button" value="Click for results" onclick="send()"></td></tr>
<%end if%>
</table>
<input type=hidden name=fld id=fld>
</FORM>
<%if Sql <> "" then%>
<table align=center border=1>
<tr>
<%if Rs.State then Rs.Close
Rs.Open Sql,Cn,adOpenForwardOnly
for each Item in Rs.Fields%>
<td align=center><%=Item.Name%></td>
<%next%>
</tr>
<%while not Rs.EOF%>
<tr>
<%for each Item in Rs.Fields%>
<td align=center><%=Item.Value%></td>
<%next%>
</tr>
<%Rs.MoveNext
wend%>
</table>
<%end if%>
</BODY>
</HTML>
<%if Cn.State then
Cn.Close
set Cn = nothing
end if
if Rs.State then
Rs.Close
set rs = nothing
end if%>
No comments have been posted about Universal SQL Query Builder. Read *.mdb Files from directory and build any Query You want.. Why not be the first to post a comment about Universal SQL Query Builder. Read *.mdb Files from directory and build any Query You want..
