CODEWITHSUNDEEP
asp.netvb.netwebformsdata-access
David
David

Reputation: 1570

ASP.NET WebForms repeater not displaying all records

I'm working on a legacy ASP.NET 2.0 Web Forms site. For some reason the DataReader control is not getting all the records that are pulled by the SQL Query. I run the query in SQL Management Studio and 3 records are returned. I only get 2 records when I iterate through the DataReader manually and only 2 records are displayed when I databind to a repeater.

VB.NET code:

sql = "SELECT tblImages.ImageID, tblProducts.ProductName FROM tblImages INNER JOIN tblProducts ON tblImages.ProductID = tblProducts.ProductID WHERE tblImages.ProductID = 725 ORDER BY tblImages.Main DESC, tblImages.ImageID ASC"
comm = New SqlCommand(sql, conn)
dr = comm.ExecuteReader()
If dr.Read() = True Then        
    rptGallery.DataSource = dr
    rptGallery.DataBind()
End If
dr.Close()
conn.Close()

Repeater Markup:

<asp:Repeater ID="rptGallery" runat="server">
<HeaderTemplate>
    <div id="slider" class="flexslider">
        <ul class="slides">
</HeaderTemplate>
<ItemTemplate>
    <li>
      <a href="getimage.aspx?id=<%# Eval("ImageID")%>&format=large" data-featherlight="image">
      <img src="getimage.aspx?id=<%# Eval("ImageID")%>&format=medium" alt="<%# Eval("ProductName")%>" /></a>
    </li>
</ItemTemplate>
<FooterTemplate>
      </ul>
    </div>
</FooterTemplate>
</asp:Repeater>

When I do a manual iteration as in the code below I can check the variable ic and it's value after iterating through the records in the datareader is 2 when it should be 3.

If dr.Read() = True Then
    Dim ic As Integer = 0
    While dr.Read()
        ic = ic + 1
    End While
End If

Upvotes: 0

Views: 725

Answers (1)

mason
mason

Reputation: 32694

Each call to dr.Read() advances the cursor. So you're skipping a record. Perhaps instead of reading the records, you should use a DataTable? Just use the Load() function to read the information from your data reader, then bind that to your repeater.

Also, you need to wrap your SqlConnection in a using statement to make sure it gets properly disposed.

In C# it'd be like this:

sql = "SELECT tblImages.ImageID, tblProducts.ProductName FROM tblImages INNER JOIN tblProducts ON tblImages.ProductID = tblProducts.ProductID WHERE tblImages.ProductID = 725 ORDER BY tblImages.Main DESC, tblImages.ImageID ASC";
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection("connectionstring"))
{      
    comm = new SqlCommand(sql, conn);       
    conn.Open();
    dt.Load(comm.ExecuteReader());        
}
rptGallery.DataSource = dt;
rptGallery.DataBind();

In VB.NET it'd be like this (automatic code converter used as I'm not a VB coder)

sql = "SELECT tblImages.ImageID, tblProducts.ProductName FROM tblImages INNER JOIN tblProducts ON tblImages.ProductID = tblProducts.ProductID WHERE tblImages.ProductID = 725 ORDER BY tblImages.Main DESC, tblImages.ImageID ASC"
Dim dt As New DataTable()
Using conn As New SqlConnection("connectionstring")
    comm = New SqlCommand(sql, conn)        
    conn.Open()
    dt.Load(comm.ExecuteReader())       
End Using
rptGallery.DataSource = dt
rptGallery.DataBind()

Upvotes: 1

Related Questions