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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Apr 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    First record missing from recordset

    Hi all,

    Can anyone help - I'm displaying sql server recordset into an asp page via a loop. Althgough this works, I'm always missing the first record (it's fine when I open the view in sql server) - Can anyone help, its driving me mad.

    thanks,

    Martin N.

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Depending on your code, you may need to change your starting counter value. If we could have the code we could fix it, but it sounds as though you are starting at 1 while all ASP Recordsets start at 0.

    Code:
    <%
    SET rs = Server.CreateObject("ADODB.Recordset")
    SET Conn = Server.CreateObject("ADODB.Connection")
    '
    rs.open "SELECT * FROM tblLooper", conn
    '
    arrLooper = rs.GetRows()
    iRowCounter = ubound(arrLooper,2)
    '
    for Counter = 0 to iRowCounter                 'this is probably your mistake. You probably have "for Counter = 1" instead of "for Counter = 0"
       ' do something with your records
    next
    %>
    HTH!

  • #3
    New to the CF scene
    Join Date
    Apr 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing records

    Hi, thanks for reply. I'm not using for counter=1 which may be where I've gone wrong. I've copied my code below to see if it explains any further.

    many thanks

    <%

    'Get the Courseid

    CourseID=request.querystring("CourseID")



    'create the sql statement
    strSQL="exec RBC_MN_MarchCourses '" & CourseID & "'" 'Create SQL string

    SET DbObj = Server.CreateObject("ADODB.Connection") 'set up ADO connection

    DbObj.Open "DSN=x;uid=y;pwd=z;" 'Connection details

    SET oRs = DbObj.Execute(strSQL) ' Execeute the SQL

    %>


    <div id="title"><h2>List of children enrolled</h2></div>

    <a href="/form1.asp">Return to course selction list</a>
    <br /><br />
    <% = oRs.Fields("Description") %>

    <div id="results">

    <table>
    <thead>
    <th class="colTick">Tick box</th>

    <th class="colName">Names</th>
    <th class="colAge">Age</th>
    <th class="colAdd">Address</th>
    <th class="colPass">Leisure Pass No.</th>
    <th class="colTel">Phone number</th>
    <th class="colMedicalNotes">Medical Info</th>
    <th class="colID">ID</th>
    <%


    DO WHILE NOT oRs.EOF
    on error resume next
    %>


    <tr>
    <td></td>

    <td><% = oRs.Fields("Name") %></td>
    <td><% = oRs.Fields("Age") %></td>
    <td></td>
    <td><% = oRs.Fields("Leisure pass number") %></td>
    <td></td>
    <td></td>
    <td><% = oRs.Fields("BookingID") %></td>
    </tr>



    <% = oRs.Fields("Name") %>

    <% oRs.MoveNext %>

    <% Loop %>



    <% DbObj.Close
    SET DbObj = Nothing
    %>

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Try turning off your On Error Resume Next.

    That will probably tell you why you are missing the first record.

    Also, there are quite a few coding "oops" that you have in here, many of which would save you a lot of head/heartache.

    1) Don't jump in and out of ASP so much. <% %> can be hard on the server.

    2) Make sure that you close the recordset as well as the connection.

    3) Some people will say that you shouldn't use the Recordset to do data manipulation, as in my example above. To ease your transition into ASP, I have used the RS that you used.

    See the updated code below:

    Code:
    <%
    CourseID=request.querystring("CourseID")				'Get the Courseid
    
    'create the sql statement
    strSQL="exec RBC_MN_MarchCourses '" & CourseID & "'" 	'Create SQL string
    
    SET DbObj = Server.CreateObject("ADODB.Connection") 	'set up ADO connection
    
    DbObj.Open "DSN=x;uid=y;pwd=z;" 						'Connection details
    
    SET oRs = DbObj.Execute(strSQL) 						'Execute the SQL
    %>
    
    
    <div id="title"><h2>List of children enrolled</h2></div>
    <a href="/form1.asp">Return to course selction list</a>
    <br /><br />
    
    <% = oRs.Fields("Description") %>
    
    <div id="results">
    <table>
    	<tr>
    		<th class="colTick">Tick box</th>
    		<th class="colName">Names</th>
    		<th class="colAge">Age</th>
    		<th class="colAdd">Address</th>
    		<th class="colPass">Leisure Pass No.</th>
    		<th class="colTel">Phone number</th>
    		<th class="colMedicalNotes">Medical Info</th>
    		<th class="colID">ID</th>
    	</tr>
    	
    <%
    'on error resume next					'taking out for testing
    oRs.movefirst							'you didn't have this here. Make sure you "movefirst" into your first record. Then you can "movenext"
    	while not oRs.EOF					'OR DO WHILE NOT oRs.EOF
    	'
    	response.write("<tr>" & vbCrLf)
    	response.write("	<td></td>" & vbCrLf)
    	response.write("	<td>" & oRs("Name") & "</td>" & vbCrLf)
    	response.write("	<td>" & oRs("Age") & "</td>" & vbCrLf)
    	response.write("	<td></td>" & vbCrLf)
    	response.write("	<td>" & oRs("Leisure pass number") & "</td>" & vbCrLf)
    	response.write("	<td></td>" & vbCrLf)
    	response.write("	<td></td>" & vbCrLf)
    	response.write("	<td>" & oRs("BookingID") & "</td>" & vbCrLf)
    	response.write("</tr>" & vbCrLf)
    	response.write("<tr><td colspan=""8"">" & oRs("Name") & "</td></tr>" & vbCrLf)
    '
    	oRs.MoveNext
    wend									'OR Loop
    oRs.close								'Make sure you close your recordset
    '
    DbObj.Close
    SET DbObj = Nothing
    %>
    Let us know if you have more questions!
    Last edited by Daemonspyre; 04-02-2007 at 06:45 PM.

  • #5
    New to the CF scene
    Join Date
    Apr 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you

    Hi,

    Many thanks for your help and coding example. This is exactly what I need. As you can tell I'm very new to ASP but i've learnt from your post and hopefully next time I'll have some tidier code!

    thanks,

    Martin.


  •  

    Posting Permissions

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