Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Database Search

Status
Not open for further replies.
tekyge

tekyge

Programmer
Dec 11, 2005
125
US
Below is the code we use in but would like to be able to add search capabilities to the sql string based on a search criteria?

dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Data\Data.mdb;" & _
"Jet OLEDB:System Database=c:\Data\secur.mdw", _
"xx", "xxxxxxxxxxx"

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn
 
Sort by date Sort by votes
Lets see I am trying to implement a search function so the client can search threw records that are there’s. the above code works for showing all records but what I would like to do is have them be able to search there records and give back the results base on there search terms, and only display say the top ten records or ten per page starting with the most relevant. Thanks for your help.



<%
dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Data\Data.mdb;" & _
"Jet OLEDB:System Database=c:\Data\secur.mdw", _
"xx", "xxxxxxxxxxx"

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn
%>

<form name="searchForm" action="search.asp" method="POST">

Search: <font size="5"><u><i> <input name="searchcriteria" size="50" maxlength="50">
<input type="submit" name="submit" value="Find">
</form></i></u></font></b></td>
</tr>
<tr>
<td width="100%">
<IMG SRC=" BORDER="0" width="100%" height="20"></td></td>
</tr>
<tr>
<td width="100%">
<%
Do While NOT rs.EOF
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>" & rs("ProjectName") & "</b><br>Date Of Last Payment:&nbsp;" & rs("LastPaymentDate") & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;" & _
"Payments Received:&nbsp;" & FormatCurrency(rs("Paid")) & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Outstanding Balance:&nbsp;" & FormatCurrency(rs("TotalPlus")) &"<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
rs.MoveNext
Loop

rs.Close
Conn.Close
%>
</td>
 
Upvote 0 Downvote
ok lets see all the options...

1. query that returns all the records(your original one)

Code: 
sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " ORDER BY [Projects].[ProjectName] ASC"

2. query that returns top 10 records(this number can be changed)...

assuming that you have a form field where user is entering the number of records he wants seee

Code: 
sql = "SELECT TOP "&request.form("noofrecs")&" * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " ORDER BY [Projects].[ProjectName] ASC"

when user enters lets says 5 the query becomes...

sql = "SELECT Top 5 * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " ORDER BY [Projects].[ProjectName] ASC"

3. for including other fields...you need to provide a form with all the search fields that the user can or want to search for...then in your query you need to all of them in your WHERE clause...

-DNG
 
Upvote 0 Downvote
I am getting this error where the results should be?

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.
 
Upvote 0 Downvote
Where specifically are you getting that error message? At a guess (without seeing any code), is it possible that the SQL code you are using has failed and therefore you have no recordset? I would do a Response.Write of your SQL (followed by a Response.End so that you will get the code that is being sent to the database) and then test that in your database.

You can also post your code back here if you continue to have issues with this.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)
 
Upvote 0 Downvote
i code i provided is just a sample code and you cannot use it as it is...

please tell us at what line are you getting the error...

-DNG
 
Upvote 0 Downvote
I changed the code up a bit and I hope I am on the right track?

Microsoft VBScript compilation error '800a03ea'

Syntax error

/cs/login/search.asp, line 38

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & LIKE " &Trim(request.form("searchcriteria")) & " ORDER BY [Projects].[ProjectName] ASC"
-----------------------------------------------------------------------------------------^
 
Upvote 0 Downvote
This one works but it shows all the records not just the ones in the search criteria


sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " like '%%"& Trim(request.form("searchcriteria")) & "%%'" & " ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn
 
Upvote 0 Downvote
not sure what you are trying to do...

is this what you are trying to do:

Code: 
sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1"))& " AND someField LIKE '%" &Trim(request.form("searchcriteria")) & "%' ORDER BY [Projects].[ProjectName] ASC"

please note all the changes carefully...

-DNG
 
Upvote 0 Downvote
Thanks guys got it:

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1"))& " AND [Projects].[ProjectName] LIKE '%" &Trim(request.form("searchcriteria")) & "%' ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn
 
Upvote 0 Downvote
This is the results section of the code above incase anywone needs it

<%
If Not rs.EOF="" Then
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>No Results Found!<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
Else
Do While NOT rs.EOF
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>" & rs("ProjectName") & "</b><br>Date Of Last Payment:&nbsp;" & rs("LastPaymentDate") & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;" & _
"Payments Received:&nbsp;" & FormatCurrency(rs("Paid")) & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Outstanding Balance:&nbsp;" & FormatCurrency(rs("TotalPlus")) &"<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
rs.MoveNext
Loop

rs.Close
Conn.Close
End If
%>
 
Upvote 0 Downvote
Sorry This is the right code: Had to change "" to False for it to work right.

<%
If Not rs.EOF=False Then
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>No Results Found!<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
Else
Do While NOT rs.EOF
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>" & rs("ProjectName") & "</b><br>Date Of Last Payment:&nbsp;" & rs("LastPaymentDate") & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;" & _
"Payments Received:&nbsp;" & FormatCurrency(rs("DebtorPaid")) & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Outstanding Balance:&nbsp;" & FormatCurrency(rs("TotalPlus")) &"<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
rs.MoveNext
Loop

rs.Close
Conn.Close
End If
%>
 
Upvote 0 Downvote
Sorry for all the correction finaly got it right this time so that rather there is no record found or the serch form field is black it will return a "No Results Found!" in the results. Thanks guys for all your help

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1")) & " like '%%"& Trim(request.form("searchcriteria")) & "%%'" & " ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn


'--
Results
<%
If Not rs.EOF=False Or Request.Form("searchcriteria")="" Then
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>No Results Found!<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
Else
Do While NOT rs.EOF
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>" & rs("ProjectName") & "</b><br>Date Of Last Payment:&nbsp;" & rs("LastPaymentDate") & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;" & _
"Payments Received:&nbsp;" & FormatCurrency(rs("DebtorPaid")) & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Outstanding Balance:&nbsp;" & FormatCurrency(rs("TotalPlus")) &"<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
rs.MoveNext
Loop

rs.Close
Conn.Close
End If
%>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
481
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
1K
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
324
leifoet
leifoet
gesrod
  • Locked
  • Question
Multiple search criteria
Replies
2
Views
226
gesrod
gesrod
leifoet
  • Locked
  • Question
Problem with updating database reords.
2
Replies
31
Views
709
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top