locked
Access and ASP - Reading and displying record set with LIKE operator - ONLY GET 1 record RRS feed

  • Question

  • User499604314 posted

    My connection string is:-

    Set connAPPv1= Server.CreateObject("ADODB.Connection")
    connAPPv1.Open "DRIVER={Microsoft Access Driver (*.mdb)}; Dbq=C:\Inetpub\wwwroot\BVRA\database\BVRA_Ver1.mdb; DefaultDir=; UID=; PWD=;"


    ASP code:

    <%
        strSurname = request("Surname")
    %>

    <p class="BVRA"> <br>
    This is a list of matches for your selected surname of <strong>"<%=strSurname%>"</strong>. Clicking on an entry, will show the available details;- <br>
     &nbsp;&nbsp;   Headstone photograph, inscription and location, which can then be shown on the graveyard site map.
    </p>
    <table width="100%"  border="1" cellspacing="1" cellpadding="1" align='center'>
      <tr>
        <td>Surname</td>
        <td>Name / Initials</td>
        <td>Date of Birth</td>
        <td>Date of Death</td>
        <td>Grave Location</td>
      </tr>
         <%
                sql1 = "Select * from GraveYard  where surname like '" & strSurname & "*'"

    ' note for wildcard use % for SQL * for access

                'response.write(sql1)
                'response.end()
                Set rs1 = Server.CreateObject("ADODB.Recordset")
                rs1.Open sql1, connAPPv1, 1, 3
        
        if not (rs1.bof or rs1.eof) then
        %>
        <tr>
            <td class="Value"><div align="center"><a class="link" href="Grave_Details_1.asp?Grave=<%=(rs1.Fields.Item("Grave_Plot").value)%>"><%=(rs1.Fields.Item("Surname").Value)%></a></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("Name").Value)%></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("DoB").Value)%></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("DoD").Value)%></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("Grave_Section").Value)%> /<%=(rs1.Fields.Item("Grave_Plot").Value)%></div></td>
        </tr>
    <%
        rs1.movenext
        end if
    %>
    </table>    
    <%
        if rs1.eof then
        %> <strong>NO MORE MATCHES for "<%=strSurname%>"</strong>. <%
        rs1.close
        end if    
    %>

    I have oodles of matching data in the dB, but I only ONE record is presented. What have I got wrong?  When I output the select statement and run in the SQL editor within Access I get a full (LOTS) record set show.

    Monday, September 12, 2011 1:48 PM

Answers

  • User499604314 posted

    Thanks.

    Problem solved:

    Connction string changed to

    ' Access
    '
      Set connAPPv1 = Server.CreateObject("ADODB.Connection")
       connAPPv1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\BVRA\database\BVRA_Ver1.mdb  ;"

    Select wild card used % (not *)

    With the do while suggestion

    Many thanks to all



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 12, 2011 3:20 PM

All replies

  • User2019981500 posted

    Hi,

    you are not doing anything wrong except you have to open msaccess and make sure that your select query is returning only one record.

    or just change you query like

    SELECT TOP 1 from GraveYard  where surname like '" & strSurname & "*'" to see the result

    I believe you have more than one record with your select query criteria in database.

    regards

    Monday, September 12, 2011 2:12 PM
  • User499604314 posted

    Thanks for the reply.  I am after the FULL Sub-set of records e.g. W* should return Willis and Willams and Willber etc...

    I am actullay only getting one record presented on my ASP page.

    Regards

    Rob

    Monday, September 12, 2011 2:18 PM
  • User2019981500 posted

    Fact is that you are retrieving only one record .if you want all records then use

    Do While Not rs.EOF

    %>

      <tr>
            <td class="Value"><div align="center"><a class="link" href="Grave_Details_1.asp?Grave=<%=(rs1.Fields.Item("Grave_Plot").value)%>"><%=(rs1.Fields.Item("Surname").Value)%></a></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("Name").Value)%></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("DoB").Value)%></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("DoD").Value)%></div></td>
            <td class="Value"><div align="center"><%=(rs1.Fields.Item("Grave_Section").Value)%> /<%=(rs1.Fields.Item("Grave_Plot").Value)%></div></td>
        </tr>

    <%

    rs.MoveNext
    Loop

    %>

    Monday, September 12, 2011 2:26 PM
  • User499604314 posted

    I'm now getting intermittantly appearing

    Error Type:
    Provider (0x80004005)
    Unspecified error
    /bvra/Pages/connection.asp, line 12


    When I get pas this I npw see NO records!.

    I think I'm gonna kick Access in touch and try something else!

    Monday, September 12, 2011 3:00 PM
  • User-1199946673 posted

    these forums are for ASP.NET (.aspx) only, for classic ASP (.asp) questions, find another forum....

    Monday, September 12, 2011 3:11 PM
  • User499604314 posted

    Thanks.

    Problem solved:

    Connction string changed to

    ' Access
    '
      Set connAPPv1 = Server.CreateObject("ADODB.Connection")
       connAPPv1 = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Inetpub\wwwroot\BVRA\database\BVRA_Ver1.mdb  ;"

    Select wild card used % (not *)

    With the do while suggestion

    Many thanks to all



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 12, 2011 3:20 PM