 |
| Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
July 25th, 2003, 01:52 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Creating a search
i have a large database with many pages with which to access it, the next task i'm trying to tackle is a page that would search the database, i have no idea how to go about doing this or if i can even do it with asp. any ideas?
thanks
|
|
July 25th, 2003, 02:19 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
You can set this up by creating a form which gathers data to search for, and posting this information to a second page which opens SQL, and either only returns records which match field information, or by returning all and using objRS.Filter to filter the records. An example:
Code:
'view.asp
<form action="../../admin/search.asp" method="post" name="search_form">
Search by: <select name="fields">
<option value="ID">ID</option>
<option value="Date" selected>Date</option>
<option value="Author">Author</option>
<option value="Recipient">Recipient</option>
<option value="Description">Description</option>
</select><br>
<input type="text" size="20" name="entered_text" value="Type keyword(s) here" onDblClick="document.search_form.entered_text.value=''"><br>
Type: <select name="docs">
<option value="" selected>All</option>
<option value="Contracts">Contract</option>
<option value="Letters">Letter</option>
<option value="Proposals">Proposal</option>
<option value="Reports">Report</option>
</select>
<input type="Button" name="cmdSearch" value="Search">
</form>
'search.asp
<%
dim myFilter
dim objCommand, objRS
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.CommandType = adCmdText
objCommand.ActiveConnection = strConnect
Select Case docs
Case "Contracts"
objCommand.CommandText = "SELECT documents.ID, documents.Yr, documents.IDinYr, documents.Version, documents.FileType, documents.fileptr, documents.SecurityLevel, contracts.num_cust, contracts.project_name, contracts.other_parties, contracts.start_date, contracts.end_date, contracts.amount, contracts.manager, contracts.manager_cust FROM documents LEFT JOIN contracts ON contracts.id = documents.id ORDER BY documents.id DESC"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
objRS.Filter = "Filetype = 'TC'"
Set objCommand = Nothing
Case "Letters"
objCommand.CommandText = "SELECT documents.ID, documents.Yr, documents.IDinYr, documents.Version, documents.FileType, documents.fileptr, documents.SecurityLevel, letters.letter_date, letters.letter_author, letters.letter_recipient, letters.letter_des FROM documents LEFT JOIN letters ON letters.id = documents.id ORDER BY documents.id DESC"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
objRS.Filter = "Filetype = 'TL'"
Set objCommand = Nothing
Case "Proposals"
objCommand.CommandText = "SELECT documents.ID, documents.Yr, documents.IDinYr, documents.Version, documents.FileType, documents.fileptr, documents.SecurityLevel, proposals.proposal_date, proposals.proposal_author, proposals.proposal_recipient, proposals.proposal_des FROM documents LEFT JOIN proposals ON proposals.id = documents.id ORDER BY documents.id DESC"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
objRS.Filter = "Filetype = 'TP'"
Set objCommand = Nothing
Case "Reports"
objCommand.CommandText = "SELECT documents.ID, documents.Yr, documents.IDinYr, documents.Version, documents.FileType, documents.fileptr, documents.SecurityLevel, reports.report_date, reports.report_author, reports.report_recipient, reports.report_des FROM documents LEFT JOIN reports ON reports.id = documents.id ORDER BY documents.id DESC"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
objRS.Filter = "Filetype = 'TR'"
Set objCommand = Nothing
Case Else
objCommand.CommandText = "SELECT documents.ID, documents.Yr, documents.IDinYr, documents.Version, documents.FileType, documents.fileptr, documents.SecurityLevel, contracts.num_cust, contracts.project_name, contracts.other_parties, contracts.start_date, contracts.end_date, contracts.amount, contracts.manager, contracts.manager_cust, letters.letter_date, letters.letter_author, letters.letter_recipient, letters.letter_des, proposals.proposal_date, proposals.proposal_author, proposals.proposal_recipient, proposals.proposal_des, reports.report_date, reports.report_author, reports.report_recipient, reports.report_des FROM documents LEFT JOIN contracts ON contracts.id = documents.id LEFT JOIN letters ON letters.id = documents.id LEFT JOIN proposals ON proposals.id = documents.id LEFT JOIN reports ON reports.id = documents.id ORDER BY documents.id DESC"
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
End Select
Select Case fields
Case "ID"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.CommandType = adCmdText
objCommand.ActiveConnection = strConnect
objCommand.CommandText = "SELECT documents.ID, documents.Yr, documents.IDinYr, documents.Version, documents.FileType, documents.fileptr, documents.SecurityLevel, contracts.num_cust, contracts.project_name, contracts.other_parties, contracts.start_date, contracts.end_date, contracts.amount, contracts.manager, contracts.manager_cust, letters.letter_date, letters.letter_author, letters.letter_recipient, letters.letter_des, proposals.proposal_date, proposals.proposal_author, proposals.proposal_recipient, proposals.proposal_des, reports.report_date, reports.report_author, reports.report_recipient, reports.report_des FROM documents LEFT JOIN contracts ON contracts.id = documents.id LEFT JOIN letters ON letters.id = documents.id LEFT JOIN proposals ON proposals.id = documents.id LEFT JOIN reports ON reports.id = documents.id WHERE documents.ID LIKE '%" & entered_text & "%' ORDER BY documents.id DESC"
Set objRS = objCommand.Execute
Set objCommand = Nothing
Case "Date"
if myFilter <> "" then
myFilter = myFilter & " AND "
end if
Select Case docs
Case "Contracts"
myFilter = myFilter & "start_date LIKE '" & entered_text & "' OR end_date LIKE '" & entered_text & "'"
Case "Letters"
myFilter = myFilter & "letter_date LIKE '" & entered_text & "'"
Case "Proposals"
myFilter = myFilter & "proposal_date LIKE '" & entered_text & "'"
Case "Reports"
myFilter = myFilter & "report_date LIKE '" & entered_text & "'"
Case Else
myFilter = myFilter & "(start_date LIKE '" & entered_text & "' OR end_date LIKE '" & entered_text & "' OR letter_date LIKE '" & entered_text & "' OR proposal_date LIKE '" & entered_text & "' OR report_date LIKE '" & entered_text & "')"
End Select
objRS.Filter = myFilter
Case "Author"
if myFilter <> "" then
myFilter = myFilter & " AND "
end if
Select Case docs
Case "Contracts"
myFilter = myFilter & "manager LIKE '*" & entered_text & "*'"
Case "Letters"
myFilter = myFilter & "letter_author LIKE '*" & entered_text & "*'"
Case "Proposals"
myFilter = myFilter & "proposal_author LIKE '*" & entered_text & "*'"
Case "Reports"
myFilter = myFilter & "report_author LIKE '*" & entered_text & "*'"
Case Else
myFilter = myFilter & "(manager LIKE '*" & entered_text & "*' OR letter_author LIKE '*" & entered_text & "*' OR proposal_author LIKE '*" & entered_text & "*' OR report_author LIKE '*" & entered_text & "*')"
End Select
objRS.Filter = myFilter
Case "Recipient"
if myFilter <> "" then
myFilter = myFilter & " AND "
end if
Select Case docs
Case "Contracts"
myFilter = myFilter & "manager_cust LIKE '*" & entered_text & "*'"
Case "Letters"
myFilter = myFilter & "letter_recipient LIKE '*" & entered_text & "*'"
Case "Proposals"
myFilter = myFilter & "proposal_recipient LIKE '*" & entered_text & "*'"
Case "Reports"
myFilter = myFilter & "report_recipient LIKE '*" & entered_text & "*'"
Case Else
myFilter = myFilter & "(manager_cust LIKE '*" & entered_text & "*' OR letter_recipient LIKE '*" & entered_text & "*' OR proposal_recipient LIKE '*" & entered_text & "*' OR report_recipient LIKE '*" & entered_text & "*')"
End Select
objRS.Filter = myFilter
Case "Description"
if myFilter <> "" then
myFilter = myFilter & " AND "
end if
Select Case docs
Case "Contracts"
myFilter = myFilter & "project_name LIKE '*" & entered_text & "*'"
Case "Letters"
myFilter = myFilter & "letter_des LIKE '*" & entered_text & "*'"
Case "Proposals"
myFilter = myFilter & "proposal_des LIKE '*" & entered_text & "*'"
Case "Reports"
myFilter = myFilter & "report_des LIKE '*" & entered_text & "*'"
Case Else
myFilter = myFilter & "(project_name LIKE '*" & entered_text & "*' OR letter_des LIKE '*" & entered_text & "*' OR proposal_des LIKE '*" & entered_text & "*' OR report_des LIKE '*" & entered_text & "*')"
End Select
objRS.Filter = myFilter
End Select
%>
I know it's a pretty lengthy example. Also, I did not include code to transfer the variables or to print the objRS, but I think you can figure that out. If not, email me at [email protected]
|
|
July 25th, 2003, 02:21 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Also, forgot to add that you need to make the search button actually search, mine doesn't do that because it calls a validation function which then submits it. Just change <input type=""button"" name=""cmdSearch"" value=""Search""> to <input type=""Submit"" value=""Search"">
|
|
July 29th, 2003, 05:29 PM
|
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I'm having trouble with the help you have provided becasue you used vb and
i have been using C# and am much more familur with it, are you or is anyone
else know how to create a search using C#,
thanks
|
|
July 31st, 2003, 03:59 PM
|
|
Authorized User
|
|
Join Date: Jul 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Yeah, but the code I submitted is ASP compliant. I can't help with the C# sorry.
|
|
|
