Hi,
I have to do one big search on a database and produce a number of tables with the results based on search fields filled out on a form using ASP and Access
Is it possible to do something like
Select * from table1,table2,table3 where
table1_id = table2_id = table3_id = table4_id ?
Or could anyone point me in the right direction
re: examples?
HERE IS MY CODE - NO ERRORS BUT NO OUTPUT
<html>
<head>
<title>MECS READ-ONLY VERSION</title>
<link REL="StyleSheet" HREF="../../css/styles.css" TYPE="text/css">
</head>
<body bgcolor="white" text="black">
<table width="780" border="0" align="center">
<tr>
<td class="topnav" align="left"><a class="main" href="../index.html">«Return To the Dashboard</a><br><br>
<a class="main" href="mecs.html">«Return To MECS Main Menu</a></td>
<td valign="top" align="right"><img src="../images/logo.gif" width="176" height="63" alt="" border="0"></td>
</tr>
<tr>
<td colspan="2" valign="top" height="1" bgcolor="#62BB46"></td>
</tr>
</table>
<%
' Declare variables
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim mecsbackupSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
Dim strSearch2 ' The text being looked for
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")
strSearch2 = Request.QueryString("search2")
strSearch2 = Replace(strSearch2, "'", "''")
' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.
%>
<table width="780" align="center">
<tr>
<td class="main">
<p>Search by <strong>Surname and County</strong> to get individual group details.</p>
<form action="<%= strURL %>" method="get">
<input type="text" name="search" value="<%= strSearch %>" />
<input type="text" name="search2" value="<%= strSearch2 %>" />
<input type="submit" />
</form>
</td>
</tr>
</table>
<%
If strSearch <> "" Then
' MapPath of virtual database file path to a physical path.
strDBPath = Server.MapPath("MECSBACKUP.mdb")
' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set mecsbackupSearch = Server.CreateObject("ADODB.Connection")
' This line is for the Access database:
mecsbackupSearch.Open "DSN=mecsbackup"
' If you are using an SQL Server use this line instead:
'cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
' & "Initial Catalog=samples;User Id=samples;Password=password;" _
'& "Connect Timeout=15;Network Library=dbmssocn;"
' Build our query based on the input.
strSQL = "SELECT * " _
& "FROM VivasLives " _
& "WHERE surname LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "AND address_county LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = mecsbackupSearch.Execute(strSQL)
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
%>
<table border="1" class="main" width="780" align="center" cellpadding="4" cellspacing="0">
<tr>
<th class="mecs">Address</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td class="main" valign="top"><%= rstSearch.Fields("address_line_1").Value %></td>
</tr>
<%
rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
mecsbackupSearch.Close
Set mecsbackupSearch = Nothing
End If
%>
</body>
</html>
Thanks in advance
Ronan
I have to do one big search on a database and produce a number of tables with the results based on search fields filled out on a form using ASP and Access
Is it possible to do something like
Select * from table1,table2,table3 where
table1_id = table2_id = table3_id = table4_id ?
Or could anyone point me in the right direction
re: examples?
HERE IS MY CODE - NO ERRORS BUT NO OUTPUT
<html>
<head>
<title>MECS READ-ONLY VERSION</title>
<link REL="StyleSheet" HREF="../../css/styles.css" TYPE="text/css">
</head>
<body bgcolor="white" text="black">
<table width="780" border="0" align="center">
<tr>
<td class="topnav" align="left"><a class="main" href="../index.html">«Return To the Dashboard</a><br><br>
<a class="main" href="mecs.html">«Return To MECS Main Menu</a></td>
<td valign="top" align="right"><img src="../images/logo.gif" width="176" height="63" alt="" border="0"></td>
</tr>
<tr>
<td colspan="2" valign="top" height="1" bgcolor="#62BB46"></td>
</tr>
</table>
<%
' Declare variables
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.
Dim mecsbackupSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to Access database (*.mdb) file
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for
Dim strSearch2 ' The text being looked for
' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")
' Retreive the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")
strSearch2 = Request.QueryString("search2")
strSearch2 = Replace(strSearch2, "'", "''")
' Since I'm doing this all in one page I need to see if anyone
' has searched for something. If they have we hit the DB.
' O/W I just show the search form and quit.
%>
<table width="780" align="center">
<tr>
<td class="main">
<p>Search by <strong>Surname and County</strong> to get individual group details.</p>
<form action="<%= strURL %>" method="get">
<input type="text" name="search" value="<%= strSearch %>" />
<input type="text" name="search2" value="<%= strSearch2 %>" />
<input type="submit" />
</form>
</td>
</tr>
</table>
<%
If strSearch <> "" Then
' MapPath of virtual database file path to a physical path.
strDBPath = Server.MapPath("MECSBACKUP.mdb")
' Create an ADO Connection to connect to the sample database.
' We're using OLE DB but you could just as easily use ODBC or a DSN.
Set mecsbackupSearch = Server.CreateObject("ADODB.Connection")
' This line is for the Access database:
mecsbackupSearch.Open "DSN=mecsbackup"
' If you are using an SQL Server use this line instead:
'cnnSearch.Open "Provider=SQLOLEDB;Data Source=10.2.1.214;" _
' & "Initial Catalog=samples;User Id=samples;Password=password;" _
'& "Connect Timeout=15;Network Library=dbmssocn;"
' Build our query based on the input.
strSQL = "SELECT * " _
& "FROM VivasLives " _
& "WHERE surname LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "AND address_county LIKE '%" & Replace(strSearch2, "'", "''") & "%' " _
' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = mecsbackupSearch.Execute(strSQL)
' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.
%>
<table border="1" class="main" width="780" align="center" cellpadding="4" cellspacing="0">
<tr>
<th class="mecs">Address</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td class="main" valign="top"><%= rstSearch.Fields("address_line_1").Value %></td>
</tr>
<%
rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
mecsbackupSearch.Close
Set mecsbackupSearch = Nothing
End If
%>
</body>
</html>
Thanks in advance
Ronan
Last edited:
