Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Create a asp search within Access DB Char count...

    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

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    sSQL = "select * from all_pages where LEN(lastname) = " & intCharCount

  • #3
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #4
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #5
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    %>

  • #6
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Change this:

    Code:
    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:

    Code:
    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.

  • #7
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Will do! Thanks for your response. I'll get that up here soon.

  • #8
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #9
    New to the CF scene
    Join Date
    Jan 2007
    Location
    S Florida
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •