In this tutorial, we will show you how to use the Repeater Control to display data from a database in VB.NET. The Repeater Control is a loop that will display any information bound to the control. We will also show you how to modify the information to be displayed.
In general, the Repeater Control is used to output an array of data. This is useful to display a list of items in an array or database that are dynamic. Specifically, we will create a SQL Database to fill the Repeater Control. Occasionally it is useful to modify the information in that list of data. You may not want to store formatting HTML tags in the raw data. The OnItemDataBind allows you to modify each item in the databind to your specific wishes.
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
First you can start a new Web Application in Visual Studio 2008. Now right-click on the App_Data folder and add a SQL Database. For our example we will add a Table to the database with the column Name as varchar(50). Now we can add some data to the table. Here is a visual example of our database.
| Name |
| Tomato |
| Orange |
| Apple |
| Banana |
Now that we have created the database we will need to edit the aspx page with the <asp:Repeater> control. You can do this by adding it from the toolbox. Place an <asp:Literal> control inside of the <ItemTemplate> tags. This will allow us to modify each item in the Repeater and display it as we wish.
|
<form id="form1" runat="server">
<div>
<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_OnItemDataBound">
<ItemTemplate>
<asp:Literal ID="Literal1" runat="server" /><br />
</ItemTemplate>
</asp:Repeater>
</div>
</form> |
Notice the OnItemDataBound attribute inside of the <asp:Repeater> control. This will run a function for each item bound to the Repeater control that we will modify and place inside of Literal1.
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
Next we will fill Repeater1 with the database information we entered earlier. This code will be placed inside of the Page_Load sub so that it is loaded when the page is first opened. To connect to the database we will need to create an instance of the SqlConnection class. Then we will need to create an instance of the SqlCommand class to send our sql query string to the database. Finally we will collect the data and bind it to Repeater1.
|
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
cn.Open()
Dim cmd As New SqlCommand("SELECT * FROM Table1", cn)
Dim ds As New DataSet()
Dim da = New SqlDataAdapter(cmd)
da.Fill(ds)
Repeater1.DataSource = ds
Repeater1.DataBind()
cn.Close()
End Sub
End Class |
Notice we need to import System.Data and System.Data.SqlClient so we can use the classes SqlConnection, SqlCommand, and SqlDataAdapter. Once the SqlConnection class is initiated, the connection is opened. The SqlCommand is initiated with the query string and SqlConnection instance cn. The data is collected with the SqlDataAdapater instance and filled into a DataSet. This DataSet is set as Repeater1's DataSource. Finally, the data is bound to the control with the DataBind() method.
Now we need to create the Repeater1_OnItemDataBound sub we referenced earlier in the OnItemDataBound attribute of Repeater1. First we must declare a RepeaterItem class instance with the value of Repeater1. Now we can declare a DataRowView class instance with the value of the Repeater1's DataItem. This will allow us to access every column on the current row of the datasource. In our example, when Table1 is bound to Repeater1, each row will be temporarily stored as drv. Since we only have 1 column to modify, we will copy it to the string variable strName by using the command drv.Row("name").ToString().
For our example we have decided to bold the first letter of each fruit name. We will do this by using the Substring method and setting this value to each Literal1 in the Repeater control. The result is as follows.
|
Protected Sub Repeater1_OnItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
Dim item As RepeaterItem = e.Item
Dim drv As System.Data.DataRowView = DirectCast((e.Item.DataItem), System.Data.DataRowView)
Dim strName As String = drv.Row("name").ToString()
Dim Literal1 As Literal = DirectCast(item.FindControl("Literal1"), Literal)
Literal1.Text = "<b>" & strName.Substring(0, 1) & "</b>" & strName.Substring(1)
End Sub |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
There are multiple ways of accomplishing our final example. The Repeater control can also be used to display results from an ArrayList, Array, etc. To avoid using the Repeater control, you could always create a sub that loops through the database rows. Then you could modify each column and store it to a Literal control that isn't inside of a Repeater control.
Looking for more ASP.NET Database Tutorials? Click Here!