ASP Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ASP Programming
Reload this Page

Database search functionality

Discuss Database search functionality in the ASP Programming forum on Dev Shed.
Database search functionality ASP Programming forum discussing Active Server Pages coding techniques and problem solving methods. Use VBScript or Jscript to make dynamic web applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 25th, 2003, 04:26 AM
chuachongchee chuachongchee is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 57 chuachongchee User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Red face Database search functionality
Hi all..

i have done a db search page... it works fine... and perfectly...
i just need to add some more "user-friendly" features...

for instance... showing that.. "record 1 to 10 of 10 pages"
my current db search tells the user that how many pages there are in total and which page he is at... but i need to tell them that showing which records excatly...

here it goes:
-----------------------------------
<%@ Language = "VBScript" %>
<%
Option Explicit
<!-- Variables Declaration -->
Dim I ' Standard looping variable

Dim strDBPath ' path to our Access database (*.mdb) file

Dim strURL ' The URL of this page so the form will work
' no matter what this file is named.

'Page constants:
Const iPageSize = 10 ' The size of our pages.
Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim iRecordsShown 'Record Control

<!-- ADO constants -->
'CursorTypeEnum Values
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'LockTypeEnum Values
Const adLockReadOnly = 1
Const adLockOptimistic = 3

'CommandTypeEnum Values
Const adCmdText = 1
Const adCmdTable = 2
Const adUseClient = 3


<!-- ADO recordset variables -->
Dim iadoConnection, iadoRecordset, iLogRecordset
Dim LogSQL
Dim strSQL ' The SQL Query we build on the fly
Dim strSearch ' The text being looked for

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

' Retrieve the term being searched for. I'm doing it on
' the QS since that allows people to bookmark results.
' You could just as easily have used the form collection.
strSearch = Request.QueryString("search")
strSearch = Replace(strSearch, "'", "''")

' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If
%>
<html>
<head>
<title>Search Page</title>
</head>
<body>

<h1 align="center">Product Search</h1>

<p>&nbsp;</p>
<p>&nbsp;</p>

<p>Search SKU or Product Name:</p>

<form action="<%= strURL %>" method="GET">
<input name="Search" value="<%= strSearch %>" size="20" />
<input type="submit" name="UserSubmit" />
</form>

<hr>
<%
If Len(strSearch) Then

<!-- Database Location -->
strDBPath = Server.MapPath("db/product.mdb")
<!-- Database Connection -->
Set iadoConnection = Server.CreateObject("ADODB.Connection")
<1-- Database open mdb -->
iadoConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & strDBPath
<!-- RecordSet Connection -->
Set iadoRecordset = Server.CreateObject("ADODB.Recordset")
<!-- END -->

'Set the cursor location property
iadoRecordset.CursorLocation = adUseClient
iadoRecordset.PageSize = iPageSize
iadoRecordset.CacheSize = iPageSize

' Build our query based on the input.
strSQL = "SELECT * FROM Products " _
& "WHERE SKU LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR Name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY ID ASC;"

' Open our recordset
iadoRecordset.Open strSQL, iadoConnection, adOpenKeyset, adLockReadOnly, adCmdText

' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = iadoRecordset.RecordCount
iPageCount = iadoRecordset.PageCount

If iRecordCount <> 0 Then
' Move to the page we need to show.
iadoRecordset.AbsolutePage = iPageCurrent

' Show a quick status line letting people know where they are:
%>
<p><%= iRecordCount %> Records Found.</p>
<p>Displaying page <%= iPageCurrent %> of <%= iPageCount %>:</p>
<%
' 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 showing you can do more then
' just spit out the data in the form it is in in the table.
%>
<table border="1" width="100%" id="SearchResults">
<tr bgcolor="#CCCCCC">
<th width="25%" align="center">
Product Name
</th>
<th width="25%" align="center">
Product Category
</th>
<th width="25%" align="center">
Product Price
</th>
<th width="25%" align="center">
Product Description
</th>
</tr>
<% Do While iRecordsShown < iPageSize AND NOT iadoRecordset.EOF AND iPageCurrent
dim introwcolor
If intRowColor = 0 Then
Response.Write "<tr bgcolor=""#EEEEEE"">" & vbCrLf
intRowColor = 1
Else
Response.Write "<tr bgcolor=""#CCCCCC"">" & vbCrLf
intRowColor = 0
End if
%>
<!--<tr>-->
<td width="25%" align="center">
<% = iadoRecordset.Fields("Name") %>&nbsp;
</td>
<td width="25%" align="center">
<% = iadoRecordset.Fields("Category") %>&nbsp;
</td>
<td width="25%" align="center">
<% = FormatCurrency(iadoRecordset.Fields("Price"), 2) %>&nbsp;
</td>
<td width="25%" align="center">
<% = iadoRecordset.Fields("Description") %>&nbsp;
</td>
</tr>
<%
iRecordsShown = iRecordsShown + 1
iadoRecordset.MoveNext
Loop
%>
</table>

<%
' Now we need to show our navigational links:
' Show "previous" and "next" page links which pass the page to
' view our search parameter.

'Show Navigational Links ONLY if more than 1 page
If iPageCount > 1 Then
%>
<!-- Seach Page Navigation -->
<p>
<%
If iPageCurrent > 1 Then
Response.Write " <a href=""" & strURL & "?search=" & Server.URLEncode(strSearch) _
& "&page=" & (iPageCurrent - 1) & """><< Previous</a>&nbsp;&nbsp;" & vbCrLf
Else
Response.Write " << Previous &nbsp;" & vbCrLf
End If

'Show page numbers:
For I = 1 To iPageCount

If I = iPageCurrent Then
Response.Write " <b>" & I & "</b>"
Else
Response.Write " <a href=""" & strURL & "?search=" & Server.URLEncode(strSearch) _
& "&page=" & I & """>" & I & "</a>"
End If 'I

If i <> iPageCount Then
Response.Write "&nbsp; | &nbsp;"
Else
Response.Write "&nbsp;&nbsp;"
End If

Response.Write vbCrLf
Next 'I

If iPageCurrent < iPageCount Then
Response.Write " <a href=""" & strURL & "?search=" & Server.URLEncode(strSearch) _
& "&page=" & (iPageCurrent + 1) & """>Next >></a>"
Else
Response.Write " Next >>"
End If
%>
</p>
<%
End If 'iPageCount > 1
Else
' Display no records error.
%>
<p>No records found. Please try again.</p>
<%
End If 'If iRecordCount <> 0

<!-- Clean up ADO variables -->
'Close RecordSet
iadoRecordset.Close
'Reset RecordSet Variable
Set iadoRecordset = Nothing

'Close Connection
iadoConnection.Close
'Reset Connection Variable
Set iadoConnection = Nothing

End If 'If Len(strSearch)
%>
</body>
</html>
----------------------------------

Thanks for all help in advance....
Reply With Quote
  #2  
Old October 26th, 2003, 04:01 AM
chuachongchee chuachongchee is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 57 chuachongchee User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Need help pls...

**URGENT**
Reply With Quote
  #3  
Old December 1st, 2003, 06:39 AM
chuachongchee chuachongchee is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 57 chuachongchee User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
cant this be done????!!!!

oh gosh.....
Reply With Quote
  #4  
Old January 11th, 2004, 01:45 PM
anoyes anoyes is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: MA, USA
Posts: 38 anoyes User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
not sure if this'll do it, there may be more to it, but...

<%=(x_first)%> displays the starting record number
<%=(x_last)%> displays the ending record number
<%=(x_total)%> displays the total number of records

where x is the db connection name, in this case iadoConnection
Reply With Quote
  #5  
Old January 11th, 2004, 08:09 PM
chuachongchee chuachongchee is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Posts: 57 chuachongchee User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
???

there is such a methid in asp?
Reply With Quote
  #6  
Old January 11th, 2004, 08:39 PM
anoyes anoyes is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2003
Location: MA, USA
Posts: 38 anoyes User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Sorry about that, figures it couldn't have been that easy. The code below I've take directly from Dreamweaver, following the code connecting to the DB.

<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim iadoConnection_total
Dim iadoConnection_first
Dim iadoConnection_last

' set the record count
iadoConnection_total = iadoConnection.RecordCount

' set the number of rows displayed on this page
If (iadoConnection_numRows < 0) Then
iadoConnection_numRows = iadoConnection_total
Elseif (iadoConnection_numRows = 0) Then
iadoConnection_numRows = 1
End If

' set the first and last displayed record
iadoConnection_first = 1
iadoConnection_last = iadoConnection_first + iadoConnection_numRows - 1

' if we have the correct record count, check the other stats
If (iadoConnection_total <> -1) Then
If (iadoConnection_first > iadoConnection_total) Then
iadoConnection_first = iadoConnection_total
End If
If (iadoConnection_last > iadoConnection_total) Then
iadoConnection_last = iadoConnection_total
End If
If (iadoConnection_numRows > iadoConnection_total) Then
iadoConnection_numRows = iadoConnection_total
End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (iadoConnection_total = -1) Then

' count the total records by iterating through the recordset
iadoConnection_total=0
While (Not iadoConnection.EOF)
iadoConnection_total = iadoConnection_total + 1
iadoConnection.MoveNext
Wend

' reset the cursor to the beginning
If (iadoConnection.CursorType > 0) Then
iadoConnection.MoveFirst
Else
iadoConnection.Requery
End If

' set the number of rows displayed on this page
If (iadoConnection_numRows < 0 Or iadoConnection_numRows > iadoConnection_total) Then
iadoConnection_numRows = iadoConnection_total
End If

' set the first and last displayed record
iadoConnection_first = 1
iadoConnection_last = iadoConnection_first + iadoConnection_numRows - 1

If (iadoConnection_first > iadoConnection_total) Then
iadoConnection_first = iadoConnection_total
End If
If (iadoConnection_last > iadoConnection_total) Then
iadoConnection_last = iadoConnection_total
End If

End If
%>

Hopefully that'll do the trick. Then, use the code I gave you in the previous response to display the count.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Programming Languages - More > ASP Programming > Database search functionality

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump



 Free IT White Papers!
 
How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.
 
Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.
 
Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.
 
Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.
 
Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.
 

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2009 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway. 
Stay green...Green IT