...

View Full Version : First record missing from recordset



mn757
04-02-2007, 04:38 PM
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.

Daemonspyre
04-02-2007, 04:52 PM
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.



<%
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!

mn757
04-02-2007, 05:20 PM
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
%>

Daemonspyre
04-02-2007, 05:34 PM
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:



<%
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!

mn757
04-03-2007, 09:11 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum