For more information on ASP.net
This article examines you to create a databound listbox using ASP.net. For more information on ASP.net is sure to check out of the articles in ASP.net Article Index. The code in this article was based on the Beta 2 version of ASP.net. To learn more about the "free asp.net Beta 2" is sure to check out this FAQ.
Introduction
One of the most common tasks developers are faced with when working with Data-driven Web sites are the need to page data. Most data is only worthwhile if it can easily being digested by a human, so a data-driven Web site needs to present data in a n easy-to-read format. In situations where a large chunk of the ' data is ' presented to ' user, it helps to break up this information into multiple PA Ges.
Paging data is nothing new, just about every search engine and ecommerce site employs the technique. If you are wonder over to Google and search on ASP you'll get back over five million results! Imagine if Google attempted to show all five million matches on one Web page! Instead, to make the information digestable by human eyes (i.e., yours), Google presents the results in chunks of ten RDS per page.
In this article we'll look at the how to implement paging database results using asp.net. It is surprisingly simple, requiring just a few lines of code!
Database Paging in Classic ASP
Paging in classic ASP is possible via a number of means. One of the most common ways is the paging properties provided in the ADO Recordset object. Even when using this properties, developers still were required to write a lot of the code to handle paging. (For more information on paging results the using this method in classic ASP, being sure to read this article.) The other methods were available as a, such as using a stored procedure as the Client-side script techniques. However, all of the methods required much code and, usually, a hapless intermixing of HTML and script code.
Database paging in asp.net
Fortunately, paging database results in asp.net are much cleaner and more easier with asp.net and than ASP. In this article we'll look at using a DataGrid Web control to implement paging. The beautiful thing about the DataGrid Web control are that it handles paging itself, meaning the amount of actual code we Have to write is very little indeed. The things-A-asp.net web page that binds a DataSet to a DataGrid Web control. (We'll then examine how to page these results.)
Below you can be what the HTML portion of our asp.net page needs. At absolute minimum, it just needs to contain a DataGrid control. I have set some properties in the DataGrid control to enhance its appearance; These ehancements, however, are optional:
<body>
<asp:datagrid id= "Dgpopularfaqs" runat= "server" borderwidth= "0"
cellpadding= "2" width= "100%"
Font-name= "Verdana"
font-size= "smaller"
Headerstyle-horizontalalign= "Center"
Headerstyle-font-bold= "True"
Headerstyle-backcolor= "Navy"
Headerstyle-forecolor= "White"
Alternatingitemstyle-backcolor= "#dddddd" >
</asp:datagrid>
</body>
Next, we need to write code that would query our database, populating a DataSet with the results of some SQL query. Once We have this populated dataset, we'll bind the dataset to the DataGrid Web control, Dgpopularfaqs. The code for connecting to a Microsoft SQL Server 7.0 or greater database can is seen below. (If you are using a different database, alter all instances of SQL below to OLE DB (i.e., change Dim Myda as New Sqldataad Apter () to Dim Myda as New OleDbDataAdapter ()).
<% @Import namespace= "System.Data"%>
<% @Import namespace= "System.Data.SqlClient"%>
<script language= "VB" runat= "Server" >
Sub Page_Load (sender as Object, E as EventArgs)
Binddata ()
End Sub
Sub Binddata ()
' 1. Create a connection
Dim MyConnection as New SqlConnection (<i>ConnectionString</i>)
' 2. Create the Command object, passing in the SQL string
Const strSQL as String = "Select Faqid, Description, dateentered, Viewcount" & _
"From Tblfaq ORDER by faqid"
Dim mycommand as New SqlCommand (strSQL, MyConnection)
' 3. Create the DataAdapter
Dim Myda as New SqlDataAdapter ()
Myda.selectcommand = mycommand
' 4. Populate the DataSet
Dim myds as New DataSet ()
Myda.fill (myDS)
' 5. Set the DataGrid ' s DataSource to the DataSet and DataBind
Dgpopularfaqs.datasource = myDS
Dgpopularfaqs.databind ()
End Sub
</script>
... HTML section omitted for brevity ...
[View a live demo!]
The ConnectionString property should is a valid connection string for the SQL database. For Example:server=ipaddress;uid=username;pwd=password;database=databasename. (Of course, the OLE DB connection string is slightly different; Refer to the documentation for more information.) Note which we are performing five steps here:
Connect to the database.
Create the Command object based upon the SQL query we wish to run and the Connection object we wish to run it.
Create the DataAdapter. This object is needed to fill a DataSet with the database results.
Populate the DataSet object with the results from the database query.
Bind the DataSet to the DataGrid Web control.
Note this with ASP.net it ' s so easy to display database information in a visually appealing format. Do take a moment to view the live demo and the "nice" DataGrid control appears. Also note So and this technique there is no shameless mixing of HTML and script code, as would being the case in a classic ASP page.
In Part 2 We'll look at the improve the "data display of the" DataGrid Web control by has the data paged. As you'll be in the Part 2, this is only requires a few lines of code!
