Data from database unable to display when perform searching

[Beng79]

Good day to all,

I have try running the database search asp script below:

http://www.asp101.com/samples/db_search.asp

by amending the sql statements in the script to run the database that I have created.

The problem is that the script runs but no data is displayed that means only the column names of the table is shown but not the data.

Anyone knows what could be the cause of the problem?

 

[emozley]

Please can you post your code.

 

[ksbigfoot]

I would add in some lines for testing like

If rstSearch.EOF Then
Response.Write "No records returned<br>"
Else
Response.Write "There are records to be displayed<br>"
End If

If I run into errors, I usually will commment out code and then uncomment line by line, testing along the way, until I find the error.

Hope this helps

 

[Beng79]

I have solve the problem, its due to the SQL statement

To anyone that is interested, this is the code:
<%
Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.

Dim cnnSearch ' ADO connection
Dim rstSearch ' ADO recordset
Dim strDBPath ' path to our Access database (*.mdb) file

Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for

' Retreive the URL of this page from Server Variables
strURL = Request.ServerVariables("URL")

strSearch = Request.QueryString("search")
'strSearch = Replace(strSearch, "'", "''")
%>
<p>Search our sample db by first or last name. (% returns all)</p>
<form action="<%= strURL %>" method="get">
HI
<input name="search" value="<%= strSearch %>" />
<input type="submit" />
</form>
<p>[Try 'am' or 'er' for an example]</p>
<%
If strSearch <> "" Then

Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

strSQL = "SELECT ProjName, sales " _
& "FROM [Proj] " _
& "WHERE ProjName LIKE '%" & Replace(strSearch, "'", "''") & "%';"_
'& "OR first_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
'& "ORDER BY ProjName;"

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = cnnSearch.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.
' For fun I'm combining some fields and showwing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="1">
<tr>
<th>Name</th>
<th>Sales</th>
</tr>
<%
Do While Not rstSearch.EOF
%>
<tr>
<td><%= rstSearch.Fields("ProjName").Value %> </td>
<td><%= rstSearch.Fields("sales").Value %></td>
</tr>
<%

rstSearch.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSearch.Close
Set rstSearch = Nothing
cnnSearch.Close
Set cnnSearch = Nothing
End If

' That's all folks! See it's really not all that hard.
%>

 

[Bullschmidt]

And for a related link:

Classic ASP Design Tips - Search Criteria on Multiple Fields

http://www.bullschmidt.com/devtip-searchcriteria.asp

Best regards,
-Paul

http://www.Bullschmidt.com

- Freelance Web and Database Developer

http://www.Bullschmidt.com/DevTip.asp

- Classic ASP Design Tips