...

View Full Version : Create a asp search within Access DB Char count...



themick4u
01-17-2007, 09:52 PM
I have an asp search-engine page that queries an access db of about 55,000 names.

I need to create a custom search (if lastname = 7 characters) similar to something like this:

select * from all_pages where lastname like '%" & strKeyword(0) & "%'

I want to be able to search the field in this db by picking a char count and having it rs. output me the results...

Any ideas? I can provide more info if needed.

Thanks! -Mick

Spudhead
01-18-2007, 11:02 AM
sSQL = "select * from all_pages where LEN(lastname) = " & intCharCount

themick4u
01-18-2007, 06:24 PM
I tried that and was not sucessful. Here is a portion of the code:

' Searching the records for the keywords entered
Select Case UBound(strKeyword)
Case 0 rs.Open "select * from all_pages where len(url)= "& intCharCount(7)

themick4u
01-18-2007, 06:25 PM
I tried that and was not successful. Here is a portion of the code:

' Searching the records for the keywords entered
Select Case UBound(strKeyword)
Case 0 rs.Open "select * from all_pages where len(url)= "& intCharCount(7)

themick4u
01-18-2007, 06:46 PM
Here is the code for the search page:

<%
' ADO Constants
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adUseClient = 3

Dim currentPage
If Len(Request.QueryString("currentPage")) = 0 Then
currentPage = 1
Else
currentPage = CInt(Request.QueryString("currentPage"))
End If

Dim recordsToShow
recordsToShow = 5

' Keyword/s to search
Dim strKeyword
strKeyword = split(Trim(Request.QueryString("look_for")), " ")

' Our Connection Object
Dim con
Set con = CreateObject("ADODB.Connection")
con.Open strDB

' Our Recordset Object
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.PageSize = recordsToShow
rs.CacheSize = recordsToShow

' Searching the records for the keywords entered
Select Case UBound(strKeyword)
Case 0 rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' order by url desc", con, adOpenForwardOnly, adLockReadOnly, adCmdText
Case 1 rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' order by url desc", con
Case 2 rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' and url like '%" & strKeyword(2) & "%' order by url desc", con
Case Else rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' and url like '%" & strKeyword(2) & "%' order by url desc", con
End Select

' If the returning recordset is not empty
If Not rs.EOF Then

Dim totalpages
totalpages = rs.PageCount
rs.AbsolutePage = currentPage

' Showing total number of pages found and the current page number
Response.Write "Displaying Page " & currentPage & " of " & totalPages & "<br>"
Response.Write "Total Records Found : " & rs.RecordCount & "<br>"
Response.Write "Search Results for : " & Request.QueryString("look_for")
Response.Write "<br><br>"

' Showing relevant records
Dim rcount, i, x

For i = 1 To rs.PageSize
rcount = i
If currentPage > 1 Then
For x = 1 To (currentPage - 1)
rcount = 5 + rcount
Next
End If

If Not rs.EOF Then
Response.Write rcount & ") First Name : <b><a href=""" & rs("url") & """>" & rs("title") & "</a></b><br>" & vbcrlf

Response.Write "<b>" & rs("description") & "<br></b>" & vbcrlf

Response.Write "Last Name : <b>" & rs("url") & "</b><br>" & vbcrlf

Response.Write "Last indexed on : " & rs("mydate") & "<br><br>" & vbcrlf

rs.MoveNext
End If
Next

' Links to move through the records
If currentPage > 1 Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?currentPage=" & currentPage - 1 & "&look_for=" & Server.URLEncode(Request.QueryString("look_for")) & """>Back</a>"
Else
Response.Write "<u style=""color : black;"">Back</u>"
End If

Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"

If CInt(currentPage) <> CInt(totalPages) Then
Response.Write "<a href=""" & Request.ServerVariables("SCRIPT_NAME") & "?currentPage=" & currentPage + 1 & "&look_for=" & Server.URLEncode(Request.QueryString("look_for")) & """>Next</a>"
Else
Response.Write "<u style=""color : black;"">Next</u>"
End If

Else
Response.Write "<b>Sorry, no matching records were found.</b>" & vbcrlf

End If

' Done. Now release Objects
con.Close
Set con = Nothing
Set rs = Nothing
%>

Spudhead
01-19-2007, 12:28 PM
Change this:


Select Case UBound(strKeyword)
Case 0 rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' order by url desc", con, adOpenForwardOnly, adLockReadOnly, adCmdText
Case 1 rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' order by url desc", con
Case 2 rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' and url like '%" & strKeyword(2) & "%' order by url desc", con
Case Else rs.Open "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' and url like '%" & strKeyword(2) & "%' order by url desc", con
End Select

to this:



Select Case UBound(strKeyword)
Case 0 sSQL_for_debugging = "select * from all_pages where url like '%" & strKeyword(0) & "%' order by url desc"
Case 1 sSQL_for_debugging = "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' order by url desc"
Case 2 sSQL_for_debugging = "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' and url like '%" & strKeyword(2) & "%' order by url desc"
Case Else sSQL_for_debugging = "select * from all_pages where url like '%" & strKeyword(0) & "%' and url like '%" & strKeyword(1) & "%' and url like '%" & strKeyword(2) & "%' order by url desc"
End Select

response.write(sSQL_for_debugging)
response.end


and post your output.

themick4u
01-19-2007, 05:25 PM
Will do! Thanks for your response. I'll get that up here soon.

themick4u
01-19-2007, 09:26 PM
It doesn't appear to like my "order by url desc" and gives me this:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/search1_debug.asp, line 57

Case 0 sSQL_for_debugging = "select * from all_pages where url like '%" & strKeyword(0) & "%' order by url desc", con, adOpenForwardOnly, adLockReadOnly, adCmdText

themick4u
01-19-2007, 09:32 PM
It doesn't seem to like the '.. strKeyword(0) &' :


Microsoft VBScript compilation error '800a0401'

Expected end of statement

/search1_debug.asp, line 57

Case 0 sSQL_for_debugging = "select * from all_pages where (url) like '%" & strKeyword(0) & "%'", con, adOpenForwardOnly, adLockReadOnly, adCmdText



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum