Hi,
I recently needed to create a datagrid in ASP .NET pages whose data was not autogenerated (read my previous blogs about my adventures in it). I did not want to add paging to my datagrid but the records being returned were so many that one had to scroll a lot in the browser. So I thought, why not put a scrollbar for my datagrid. Ofcouse, there is no direct property that I could switch on in the Datagrid. And I realized I had to use the div tag which is actually pretty simple.
Adding a ScrollBar
Enclose your datagrid in a <div> tag and set the overflow style to auto/scroll. For those of you who prefer to do it through the design mode, there is an excellent step-by-step instructions at this link:http://www.dotnetjunkies.com/HowTo/E833AEAE-8D7E-4D1F-821B-D848D965F70A.dcik
Now my datagrid had a neat little scrollbar and I could fit it into the browser with losing sight of the input information I had entered above. But I had one more problem. The Column headers would not stay still and would scroll away. How do I fix this?
Fixed Headers
The best way to achieve this is by using style sheets (I have seen around suggestions like adding a table which has your column names as the columns for this table and you stick it on top of the datagrid, and you set the datagrid header visible property to false. Trust me, CSS is way cooler than that).
Add this style to the CSS class or to the HTML code itself.
<style type="text/css">
.DataGridFixedHeader { POSITION: relative; ; TOP: expression(this.offsetParent.scrollTop); BACKGROUND-COLOR: white }
</style>
And refer this style in your ASP datagrid, by setting this value to the CSSClass property of the HeaderStyle of the datagrid. For ex:
<asp:datagrid id="resultGrid" tabIndex="-1" runat="server" Width="900px" Height="224px" BorderStyle="None">
<HeaderStyle Font-Names="Arial" Font-Bold="True" HorizontalAlign="Center" ForeColor="White" BorderStyle="Solid"
BorderColor="Black" VerticalAlign="Bottom" BackColor="LightSlateGray" CssClass="DataGridFixedHeader"></HeaderStyle>
..........
</asp:datagrid>
You can set this property from the design mode too by navigating to the HeaderStyle property.
My colleague kmarshall has posted a very interesting article about using CSS gridviews here. Check it out, you may get some interesting ideas:
http://blogs.claritycon.com/blogs/kevin_marshall/archive/2006/02/23/234.aspx
BTW, I could have used Infragistics WebGrid and it has the options of setting a scrollbar and a fixed data header. But for the complex template columns I had to use for my grid, I was better off using the DataGrid than the WebGrid with these nice workarounds.
I ran into a weird issue today. I reimaged my machine and had Visual Studio 2003 and 2005 installed on it. I had an ASP .Net web service application created using VS 2003. So I tried to debug this application and all I would get was Unable to start debugging on the Web server
I could run the webservice from the browser by typing in the URL. I could run it from IIS by right-clicking on the aspx page and click "Browse", but debug I could not!
The IDE would not say anything and the help would ask me to perform the following steps:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtbshttpservererrors.asp
I performed all of these and some things in addition but not luck. I was totally vexed and was almost about to give up when I gave one last shot. I went to the IIS server, right clicked on my Web application and looked at the Properties and verfiied each tab, and when I clicked on the ASP .NET tab, this is where I found the cause of my error. The ASP .NET version had been defaulted to 2.0 and my application was written in version 1.1 (I hadn't converted my web app to the newer version as I needed it to run on VS 2003).
I manually changed the version of this webservice application to 1.1 and now I'm back to debugging! So do not assume that opening a web service application in VS 2003 will have the IIS server set the right version for you. You may have to manually configure it esp if you plan to use both VS 2003 and VS 2005
How do you select the first N rows in various databases?
A quick thing to remember is that rows are not stored in any particular sequence in a database. They can be placed in random data blocks in the database without any order whatsoever. If a query returns X rows, they definitely do not mean that these rows are stored in exactly this same order. It is just that these rows have satisfied your select query and are being displayed in the order they are found (unless you specify an ORDER BY clause). So how do I obtain only the first N rows from a query which actually returns more than N rows?
Here is a quick overview of how it can be done.
MS SQL
SELECT TOP 10 name, description
FROM TABLEA
ORDER BY name
The above query will return the first 10 rows sorted by name. How do I get the bottom 10 rows? Use the DESC keyword in the ORDER BY clause and it will sort it in reverse order.
ORACLE
SELECT name, description
FROM TABLEA
WHERE rownum <=10
ORDER BY NAME
One has to be careful when trying a similar thing in ORACLE. The above code will not work, simply because the query will return the first 10 rows and then order them, which is definitely not what you wanted. ROWNUM is a pseudo column that is assigned values based on the results returned. So the above code would have worked if there was no ORDER BY clause. The right way would be:
SELECT * FROM
(SELECT name, description
FROM TABLEA
ORDER BY NAME)
WHERE rownum <=10
A good resource for various questions on Oracle is:
http://www.orafaq.com/
DB2
SELECT name, description
FROM TABLEA
ORDER BY NAME
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 10 ROWS
Working with datagrids can be fun and interesting and sometimes challenging too. Everything is fine as long as you are using the basic features of Datagrid and generating the columns from the dataset itself. But they tend to get a little challenging when you need to customize columns and design it the way you want and provide more customization that is not available in the DataSet itself.
This article discusses some key scenarios when dealing with Datagrid.
1. Custom Column formatting
Now all columns need not be of the same datatype or you may want to format a certain numeric column to display commas etc. So how can we do it?
i. Specifying the DataStringFormat
Before I delve into the different formats, here is how you can add/change the format. Right click on the Datagrid in design view and click on Property Builder (or in the Datagrid properties, click on the Columns Collection).
Choose the Columns tab. You will see a list of Available Columns and Selected Columns. If you don’t have any selected columns, add a Bound Column. Now click on the selected Column and you will see the option for “Data Formatting expression”. This is how you enter the data format.
You can also directly add this attribute to the aspx code which you is discussed later in this article.
The single-most important rule to understand is the way the format specification. The specification is:
{0:myFormat} where myFormat is the format you want to specify, for example
{0:C}
People often forget the curly braces 0 i.e. {0}. YOU NEED THIS EVEN IF YOU ARE ENTERING IT THROUGH PROPERY BUILDER
The {0} specifies the current row and cell to which the format needs to be applied to.
ii. Various formats
a. Currency format
{0:C}
The currency format is according to the culture info set in the web config file
b. Numeric format
{0:000}
This specifies zero padded numbers of length 3
c. Decimal format
{0:0.00}
You can find more info about standard numeric formats at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
and custom numeric formats at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
d. Date format
{0:dd-MMM-yyyy HH:mm:ss}
You can find more standard date formats at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp
and custom date formats at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomdatetimeformatstrings.asp
iii. Modifying the format of the custom column in the ASPX page
You can add the DataFormatString attribute directly in the aspx page as shown below:
<asp:BoundColumn DataField="LOGDATE" HeaderText="Log Date" DataFormatString="{0:yyyy/MM/dd HH:mm:ss}"></asp:BoundColumn>
2. Adding a Row Number to your Datagrid
You are displaying a ton of rows in your custom datagrid and you wish you could have a row number to identify each row easily. How do we do that?
It is very simple actually. By using a template column. (For info on how to create template columns, refer to the links below in this section).
In the ItemTemplate element, you can define the content that will be rendered for each item in the column.
Below is the sample for this:
<asp:templatecolumn headertext="Row Number">
<itemtemplate>
<%# Container.ItemIndex+1 %>
</itemtemplate>
</asp:templatecolumn>
ItemIndex is the row index indexed from 0. So add 1 to it and you get your row numbers!!
Now an interesting point to note is the Container object which basically refers to the current row in the DataGrid and is of type DataGridItem. So you can practically use any methods defined on the DataGridItem and utilize them in displaying values in your column, and this is how we have displayed the row number. You can invoke the Container.DataItem to get the row itself and you can choose whichever column you want from it by using DataBinder.Eval(). An example is discussed in the later section.
There is a good article on creating templates at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskaddingtemplatecolumntodatagridwebcontrol.asp
A very good article on the usages of template columns can be found at:
http://aspnet.4guysfromrolla.com/articles/061002-1.aspx
3. Adding a HyperLink column to your Datagrid
This is pretty simple thanks to the customizable columns. You can do it in 2 ways (that I know of):
i. Using HyperLinkColumn
Using the property builder, you can add a HyperLinkColumn and specify the URL that it needs to point to. You can add other attributes like target location, the Header Text etc. Here is a sample:
<asp:HyperLinkColumn
Target="_blank"
DataNavigateUrlField="ID"
DataNavigateUrlFormatString="Form2.aspx?MyID={0}"
DataTextField="ID"
HeaderText="Details" >
</asp:HyperLinkColumn>
- The DataTextField is the column to be displayed. (If you don’t want to display this value but put in a common value of let’s say “More Info” to each cell, you can do this by specifying it in DataFormatString, for example DataFormatString=”More Info”)
- The DataNavigateUrlField contains the column name that is to be passed into the URL field.
- The DataNavigateUrlFormatString contains the URL link.
- The HeaderText refers to the Column name to be displayed
ii. Using TemplateColumn
You can achieve the same by adding a template column and putting in <a> attribute. Consider this example:
<asp:TemplateColumn HeaderText="Row #">
<ItemTemplate>
<a target=_blank href='<%# DataBinder.Eval(Container.DataItem, "ID", "Form2.aspx?MyID={0}") %>'><%# Container.ItemIndex+1 %></a>
</ItemTemplate>
</asp:TemplateColumn>
DataBinder.Eval takes in 3 parameters:
- Container.DataItem is the current DataGridItem the grid is referring to.
- ID is the column you want to choose from the DataGridItem row
- The third parameter is the format that you want to display the column value. By saying Form2.aspx?MyID={0}, I am actually sending the value of ID into the format string and using that as my link URL.
Notice how neatly we have tricked it to display the row number and the hyperlink being the format string we want to use, which is a URL to another page
You can use the same method to display images in a grid. For example:
<itemtemplate>
<img width=60 align="top" src='<%# DataBinder.Eval ( Container.DataItem, "Book_Cover", "/etc/images/cover-{0}.gif" ) %>' >
</itemtemplate>
Do note that DataBinder.Eval performs late bound evaluation and may cause slow performance compared to standard ASP.NET pages. Refer to this link here for more details:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwebuidatabinderclassevaltopic2.asp
Hope you had fun reading through this and found this article helpful.
Happy Programming!!
Satish Vemula
Clarity Consulting
svemula@claritycon.com