Forums
This topic is locked
search in multiple tables
Posted 12 Oct 2007 03:29:39
1
has voted
12 Oct 2007 03:29:39 David Reichard posted:
I have a database with 3 tables that I have joined using an extension in the CS developers toolbox.By default I want all records appear. But i Want to add a search facility that will search in a particular field from the 3 tables and display the results of the search in the same format as the default display.
The normal way I do it would be to add some like statements with % as the default value so if the field is left blank it will show all results but this wont work when added to the sql of existing statement.
I have included a blank form at the top of the code so you can see what i want to search for
Cheers
<%
Dim master1Races
Dim master1Races_cmd
Dim master1Races_numRows
Set master1Races_cmd = Server.CreateObject ("ADODB.Command"
master1Races_cmd.ActiveConnection = MM_babywatch_STRING
master1Races_cmd.CommandText = "SELECT * FROM Races ORDER BY Race_Name"
master1Races_cmd.Prepared = true
Set master1Races = master1Races_cmd.Execute
master1Races_numRows = 0
%>
<%
Dim detail2Race_Fields__MMColParam
detail2Race_Fields__MMColParam = "123456789"
If (Request("MM_EmptyValue"
<> "" Then detail2Race_Fields__MMColParam = Request("MM_EmptyValue"
End If
%>
<%
Dim detail2Race_Fields
Dim detail2Race_Fields_cmd
Dim detail2Race_Fields_numRows
Set detail2Race_Fields_cmd = Server.CreateObject ("ADODB.Command"
detail2Race_Fields_cmd.ActiveConnection = MM_babywatch_STRING
detail2Race_Fields_cmd.CommandText = "SELECT * FROM Races, Race_Fields, Horses WHERE Races.Race_ID = Race_Fields.Race_ID AND Race_Fields.Horse_Name = Horses.Horse_Name AND Races.Race_ID = ? ORDER BY Horses.Horse_Name ASC"
detail2Race_Fields_cmd.Prepared = true
detail2Race_Fields_cmd.Parameters.Append detail2Race_Fields_cmd.CreateParameter("param1", 200, 1, 255, detail2Race_Fields__MMColParam) ' adVarChar
Set detail2Race_Fields = detail2Race_Fields_cmd.Execute
detail2Race_Fields_numRows = 0
%>
<%
Dim nst_sw
nst_sw = false
%>
<%
Dim Repeat_master1Races__numRows
Dim Repeat_master1Races__index
Repeat_master1Races__numRows = -1
Repeat_master1Races__index = 0
master1Races_numRows = master1Races_numRows + Repeat_master1Races__numRows
Dim Repeat2__numRows
Dim Repeat2__index
Repeat2__numRows = -1
Repeat2__index = 0
breeding_numRows = breeding_numRows + Repeat2__numRows
%>
<body>
<form id="form1" name="form1" method="post" action="">
<p>
<input type="text" name="race_name" id="race_name" />
<br />
<input type="text" name="horse_name" id="horse_name" />
<br />
<input type="text" name="sire" id="sire" />
<br />
<input type="submit" name="button" id="button" value="Submit" />
</p>
</form>
<p> </p>
<table border="1">
<%
While ((Repeat_master1Races__numRows <> 0) AND (NOT master1Races.EOF))
%>
<tr>
<td><b><%=(master1Races.Fields.Item("Race_Name"
.Value)%></b></td></tr>
<%
nst_sw = false
detail2Race_Fields_cmd.Parameters("param1"
.Value = CInt("0" + master1Races.Fields.Item("Race_ID".Value)Set detail2Race_Fields = detail2Race_Fields_cmd.Execute
While (NOT detail2Race_Fields.EOF)
'Nested repeat
%>
<tr>
<td> <%=(detail2Race_Fields.Fields.Item("Horse_Name"
.Value)%> <%=(detail2Race_Fields.Fields.Item("Sire".Value)%><%=(detail2Race_Fields.Fields.Item("Dam".Value)%></td></tr>
<%
' Nested move next
detail2Race_Fields.MoveNext()
Wend
%>
<%
Repeat_master1Races__index=Repeat_master1Races__index+1
Repeat_master1Races__numRows=Repeat_master1Races__numRows-1
master1Races.MoveNext()
Wend
%>
</table>
<p> </p>
</body>
</html>
<%
master1Races.Close()
Set master1Races = Nothing
%>
<%
detail2Race_Fields.Close()
Set detail2Race_Fields = Nothing
%>
<%
searchkey = request.querystring("searchkey"
if searchkey<>"" then
arr = split(searchkey," "
strCondition = ""
if ubound(arr)>0 then
for each item in arr
if strCondition = "" then
strCondition = "field_name like '%" & item & "%'"
else
strCondition = strCondition & " OR field_name like '%" & item & "%'"
end if
next
else
strCondition = "field_name like '%" & searchkey & "%'"
end if
strSQL = "SELECT * FROM tablename WHERE " & strCondition
response.write strSQL 'for debugging purposes
'other codes here
end if
%>