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 gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Searching Access Database

Status
Not open for further replies.
tekyge

tekyge

Programmer
Dec 11, 2005
125
US
Below is the code and the error I am getting. Can anyone tell me what I am doing wrong? I want this search page to be able to search the database based by client. Searching the table named "projects" field name "ProjectName" and display the results.

My error message is as follows:

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression '[Projects].[ClientID]='.
/cs/login/search.asp, line 17

Code:

<html>
<head>
</head>
<body>

<%
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:\mydata\Data.mdb;" & _
"Jet OLEDB:System Database=c:\mydata\Scur.mdw", _
"xx", "xxxxxxxx"

sql = "SELECT * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1"))
rs.open sql, conn

searchcriteria = Request.Form("searchcriteria")
search = Request("search")
strRowColor = "#ffffff"
submit = Request.Form("submit")
id = Trim(session("myvar1"))
%>

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

Search: <input name="searchcriteria" size="50" maxlength="50" value="<%=searchcriteria%><%=search%>">
<input type="submit" name="submit" value="Find">
</form>
<hr>

<%
If submit <> "" AND searchcriteria <> "" Then

strSQL = "SELECT ID, ProjectsName FROM tblProjects WHERE Title LIKE '%" & searchcriteria & "%' ORDER BY Title"
objRS.Open strSQL, objConn, 3 , 3

If objRS.EOF Then
Response.Write "There are no records that match your criteria."

objRS.Close

Else

Response.Write "Search found " & objRS.RecordCount & " record(s)"
%>

<table border="1">
<tr>
<th>ID</th>
<th>Title</th>
<th>Company</th>
</tr>

<%
Do While Not objRS.EOF
%>

<tr bgcolor="<%=strRowColor%>">
<td><a href="search.asp?search=<%=searchcriteria%>&id=<%=objRS("ID")%>"><%=objRS("ID")%></a>&nbsp;</td>
<td><%=objRS("Title")%>&nbsp;</td>
<td><%=objRS("Company")%>&nbsp;</td>
</tr>

<%
If strRowColor = "#ffffff" Then strRowColor = "#dddddd" Else strRowColor = "#ffffff"
objRS.MoveNext
Loop

Response.Write "</table>"
objRS.Close
End If

End If

If id <> "" AND search <> "" Then

strSQL="SELECT ID, Projects, Description FROM tblProjects WHERE ID=" & id & ";"
objRS.Open strSQL, objConn

Response.Write "<u>" & objRS("Title") & "</u> : " & objRS("Projects") & "<br>" & vbCrlf & _
"<p>Description: <br>" & vbCrlf & _
"<textarea borderline=""0"" disabled cols=""100"" rows=""5"">" & objRS("ProjectName") & "</textarea>" & vbCrlf & _
"</p>"

End If
%>


</body>
</html>
 
Sort by date Sort by votes
From the error msg it looks like session("myvar1") is empty.
 
Upvote 0 Downvote
yeah for some reason it was but now with no changing of the code it works but when I search it gives me this error

Microsoft VBScript runtime error '800a01a8'

Object required: ''

/cs/login/search.asp, line 37
 
Upvote 0 Downvote
...looks like an ado recordset named rs and then later referred to as objRS
 
Upvote 0 Downvote
and you are closing your recordset object at various places...

make sure you have it available if you want to use it...

-DNG
 
Upvote 0 Downvote
Hey Dot here is what I have and it works great but it will only show one record?


<html>
<head>
</head>
<body>
<%
Dim blah
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:\mydata\Data.mdb;" & _
"Jet OLEDB:System Database=c:\mydata\Scur.mdw", _
"xxx", "xxxxxxxx"

sql = "SELECT * FROM [Projects] WHERE [ProjectName] LIKE '%" & searchcriteria & "%' And [Projects].[ClientID]=" &Trim(session("myvar1"))
rs.open sql, conn

searchcriteria = Request.Form("searchcriteria")

blah1=rs("ProjectName")
rs.close
%>


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

Search: <input name="searchcriteria" size="50" maxlength="50" value="<%=searchcriteria%>">
<input type="submit" name="submit" value="Find">
</form>
<hr>
<table border="0" width="454" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tr>
<th width="444">Result</th>
</tr>
<tr bgcolor="<%=strRowColor%>">
<td width="444"><%= (blah1) %>&nbsp;</td>
</tr>
</body>
</html>
 
Upvote 0 Downvote
If you want to show your entire recordset perhaps you could use a loop:[tt]
<%
Do while not rs.EOF
%>
<tr bgcolor="<%=strRowColor%>">
<td width="444">
<%= rs("ProjectName") %>
&nbsp;
</td>
</tr>
<%
rs.MoveNext
Loop
rs.close
set rs = nothing
%>
[/tt]
 
Upvote 0 Downvote
Sheco is correct, in order to show all records, you need to loop through each matching record until EOF = True.

Code: 
<%
Do While NOT rs.EOF
  Response.Write("<tr bgcolor='" & strRowColor & "'>")
  Response.Write("<td width='444'>" & ProjectName & "</td>")
  Response.Write("</tr>")
  rs.MoveNext
Loop

rs.Close
Conn.Close
%>

I hope this helps!

-Brian-
I'm not an actor, but I play one on TV.
 
Upvote 0 Downvote
Thanks guys for your help. I am wanting to show the entire record set only when the field is left blank any other time I want the search to be as relevant as possible. Any help there ?
 
Upvote 0 Downvote
If possable limit the number of results per page?
 
Upvote 0 Downvote
If you only really care about the first X number of records then you can achieve this very easily by changing your SELECT statement to use the TOP keyword:
[tt]SELECT TOP 10 * FROM [Projects] WHERE ... [/tt]

If you want to show all results but only a certain number per page like a search engine then it gets more complicated... If you'll only ever have a few hundred or so hits then you could get away with using the ADO recordset object, if your resultset goes into the thousands than it is better to do the paging inside a stored procedure on your database.
 
Upvote 0 Downvote
It still shows all the record even when I added TOP 10 *
 
Upvote 0 Downvote
Here is my code am I anywhere close to getting this to work? It shows results of search as:

[Projects].[ProjectName] Like '%%' And

[Projects].[ProjectName] Like '%%' And

[Projects].[ProjectName] Like '%%' And
'--


sql = "SELECT TOP 10 * FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("myvar1"))
rs.open sql, conn

results = strWhere & "[Projects].[ProjectName] Like '%" & searchcriteria & "%' And"
%>
<h4>SEARCH</h4>
<form name="searchForm" action="search.asp" method="POST">

Search: <input name="searchcriteria" size="50" maxlength="50">
<input type="submit" name="submit" value="Find">
</form>
<hr>
<table border="0" width="454" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tr>
<th width="444">Result</th>
</tr>
<%
Do While NOT rs.EOF
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='444'>" & results & "<p></td>")
Response.Write("</tr>")
rs.MoveNext
Loop

rs.Close
Conn.Close
%>
 
Upvote 0 Downvote
Ok, so to clarify, you would like the following to happen:

If the search is blank, return all records in the table. If the search is not black, return only the records that match the criteria. Is this correct?

If so, I believe this is what you will need:
Code: 
Dim SearchCriteria
Dim DBResults
Dim DBResultSQL

Set DBResults = Server.CreateObject("ADODB.Recordset")
SearchCriteria = Request.Form("SearchCriteria")

If SearchCriteria <> "" Then
  DBResultSQL = "SELECT * FROM tblProjects " &_
                "WHERE tblProjects.Title LIKE " &_
                "'%" & SearchCriteria & "';"
Else
  DBResultSQL = "SELECT * FROM tblProjects;" 
End If

DBResults.Open DBResultSQL, Conn

This should produce either a recordset containing all projects if there are no criteria specified, or a recordset of only the projects that match the specified criteria.

-Brian-
I'm not an actor, but I play one on TV.
 
Upvote 0 Downvote
Darn lack of edit capability! Line 11 from above should read:
Code: 
                "'%" & SearchCriteria & "%';"

-Brian-
I'm not an actor, but I play one on TV.
 
Upvote 0 Downvote
sorry it took so long to responed. below is the code that works for us but I need to add the ablity to filter the results via searchcriteria field on this page.

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
 
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
340
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
790
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
206
leifoet
leifoet
leifoet
  • Locked
  • Question
Classic ASP multiple record update with Access
Replies
7
Views
331
dhookom
dhookom
sdagger2
  • Locked
  • Question
Classic asp: Insert new record to access database from successful PayPal payment
Replies
2
Views
153
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top