Gridview Sorting Alphanumerically

Hi
I have a gridview that shows a table from a sql 2008 database
Is there a way to make it sort alphanumeric values
for example, for the following values: M-1, M-2, M-100
they show up as:
M-1
M-100
M-2

How can I make it sort them as:
M-1
M-2
M-100

thanks in advance for all the support

 

Sure, modify the 'ORDER BY' clause (or add one) on your T-SQL select statement so the data is ordered in the desired way before the data bind to the GridView is performed: http://msdn.microsoft.com/en-us/library/ms188385.aspx

 

...Joe'da man
(oops, should use "mate", right?)

OK, just trying to spread some smiles but there is a point to my post...

Wanted to offer a tip...Experiment with the ListView also, you can do magic with it.
http://blogs.msdn.com/mikeormond/archive/2008/01/23/some-asp-net-3-5-listview-control-examples.aspx

All the best,
Mark

 

Hey guys.. thanks for the help

It's already getting sorted by the column that I wanted.. which is the stock number..
but the sorting isn't the way I want it.. so I can specify "ORDER BY Stock" but it's sorted as above..
I wanted to sort alphanumerically.. as I showed in the example in my first post.. what would be the order by statement for that. if any?

 

Heh heh nice one Mark, although if you wanted to be completely accurate for a scouser you'd probably say "spot on kidda".

10inja: For the little bit of sample data you posted this works:

SELECT * from YourTable ORDER BY CAST(SUBSTRING(Stock, 3, LEN(Stock)-2) AS INT)

..but it only works if the first two characters of the Stock column can always be ignored because it assumes a number from position 3 to the end of the string and then performs a conversion.

You also need to be careful when doing this sort of thing because if the data table contains a lot of data there will be a performance hit. If this is a concern then you might think about changing the schema so it's easier for you to get the results you need to achieve.

 

very cool.. that's what I was looking for, I tried that cast and substring before but had the second parameter wrong.. but now it works great
some of the values have three letters such as OC-1 and so on, but I'm willing to get rid of that make sure it's only two letters

"BUT"

when the page loads, it works great with this ORDER BY addition.
the columns of my gridview are sortable, so someone can click on the column header and it will sort it, in which case, it goes back to sorting the way it wants it and not the way using the ORDER BY.

is there a solution to that?

 

Sure there's almost always a solution. It sounds as though you have simply switched on the built in sorting functionality for the GridView and if that's the case, this won't work correctly with your custom ORDER BY clause in the SQL because when the 'Stock' column header is clicked, the SortExpression that is getting auto-assigned will be a regular ORDRER BY Stock clause.

There are a couple of ways to override the default sorting behaviour; you can modify the SortExpression for an individual column directly in the designer or you can implement your own 'Sorting' event handler for the GridView in which you will either:

• Programmatically modify the sort expression in the handler so that it is the one you need and then pass it to a data source control OR
• Completely control the sorting algorithm yourself if you need complete control over the sorting method and then rebind your GridView.

There's some more detailed info over here:
http://msdn.microsoft.com/en-us/lib...controls.datacontrolfield.sortexpression.aspx
http://msdn.microsoft.com/en-us/library/hwf94875.aspx

 

thanks for the info.. I'm still stuck..

it seems that all you can specify in the sortexpression is the column

so, I'm not sure how to programatically specify
CAST(SUBSTRING(Stock, 3, LEN(Stock) - 2) AS INT)";

what is the syntax

I added
AllowSorting="True" OnSorting="GridView1_Sorting" >

then in code behind I have the function:
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (e.SortExpression == "Stock")
{
}
}

just don't know how to programatically specify the ORDER BY
any help is appreciated.

 

Ok perhaps the example I've knocked up below will help you out. In this scenario the default sort order is by your [Stock] column as specified on the SqlDataSource. This is the sort order rendered on first load. The user can sort by other columns but when the sort is re-selected for the Stock column, the code ensures that the default sort order is used on the SqlDataSource by clearing the SortExpression.

I've added a couple of comments in the code example below to maybe cope with other types of scenarios.

The usual disclaimers apply - this is sample code only that gives no consideration to anything other than your specific issue!

Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title></title>
</head>
<body>
    
    <script type="text/C#" runat="server">
        protected void GridView_Sorting(object sender, GridViewSortEventArgs e)
        {
            if (e.SortExpression == "Stock")
                e.SortExpression = string.Empty; //use the default sort clause assigned to the SqlDataSource

            /*
             * You can also completely rewrite the SqlDataSource SelectCommand if necessary to cope with the sort
             * direction and rebind
             * If using a stored proc just pass parameters to it based on the column and sort direction to execute
             * and then rebind
             */
        }
    </script>

    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView" runat="server" AllowSorting="True" 
            AutoGenerateColumns="False" DataKeyNames="pk" DataSourceID="SqlDataSource" 
            onsorting="GridView_Sorting">
            <Columns>
                <asp:BoundField DataField="pk" HeaderText="pk" InsertVisible="False" 
                    ReadOnly="True" SortExpression="pk" />
                <asp:BoundField DataField="Stock" HeaderText="Stock" SortExpression="Stock" />
            </Columns>
        </asp:GridView>
    </div>
    <asp:SqlDataSource ID="SqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:test %>" 
        SelectCommand="SELECT * FROM [Test] ORDER BY CAST(SUBSTRING([Stock], 3, LEN([Stock]) - 2) AS INT)"></asp:SqlDataSource>
    </form>
</body>
</html>

 

Wow.. thanks a bunch Joe.. I owe you lunch

that e.sortexpression = string.empty did it. that was the key

so now it's solved, after I almost gave up

in the code behind my function is:
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (e.SortExpression == "Stock")
{
e.SortExpression = string.Empty;
}
}

sweet dude, thanks to you and Mark and the rest for all the help, much appreciated..

now I can work on my fulltextsearch which is also giving me hell.. might have to post something for that too

 

Cool, I'm glad it worked out. I wasn't sure if you were using a SqlDataSource or some other way of populating the GridViews' data source.

If you haven't realised by now, clearing the SortExpression in the OnSort event for the [Stock] column effectively instructs the GridView to pass an empty sort expression to the SqlDataSource for this column only; In the sample code I posted, the SqlDataSource has an ORDER BY clause built into its' SelectCommand so the postback / rebind process intiated by the user clicking the header LinkButton for the [Stock] column results in the default ORDER BY clause being used as the query to the DB.

If you've not already done so, you could go a little further with this by enabling bi-directional sorting in the GridView for the Stock column. If the other columns can support bi-directional sorting, why not support it in your custom sorted column also? It's only a little bit of an extension to what we already have and if you wanted to get really flashy, you could even add sort arrow indicator images into the GridView header columns (but I'll leave that one to you to investigate if you fancy it ;-))

Code:

<script type="text/C#" runat="server">
        const string DEFAULT_SELECT_STATEMENT = "SELECT * FROM [Test] ORDER BY CAST(SUBSTRING([Stock], 3, LEN([Stock]) - 2) AS INT)";

        bool StockSortAsc
        {
            get { return (bool)ViewState["StockSortAsc"]; }
            set { ViewState["StockSortAsc"] = value; }
        }

        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            if (!IsPostBack)
                StockSortAsc = false;
        }
        
        protected void GridView_Sorting(object sender, GridViewSortEventArgs e)
        {
            if (e.SortExpression == "Stock")
            {
                StockSortAsc = !StockSortAsc;
                e.SortExpression = string.Empty;
                SqlDataSource.SelectCommand = StockSortAsc ?
                    DEFAULT_SELECT_STATEMENT :
                    DEFAULT_SELECT_STATEMENT + " DESC";
            }
        }
</script>

 

Hey thanks for the info. I finally got more time to play around with it.
And, I got it to bidirectional sort.

I am using a dataset, so I have to use the objectdatasource, I created a new query in the table adapter to sort "DESC" since I don't think I can programatically change the select statement of the table adapter query.
So, it looks like this now:

static string SelectMethod;

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (e.SortExpression == "Stock")
{
if (SelectMethod == null) SelectMethod = "GetData";
e.SortExpression = string.Empty;
ObjectDataSource1.SelectMethod = SelectMethod == "GetData" ?
"GetDataBySortStockDesc":
"GetData" ;
SelectMethod = ObjectDataSource1.SelectMethod;
}
}

But this code isn't a hundred percent working. The static variable is remembered from the last time you visit the website. It doesn't work all the time.
If the SelectSort from the last time you visited is set to DESC, then the first time it load, it will not use the on_sorting and will sort normal. Then if you click on sort again, it will see that the SelectMethod is DESC and set it to normal and it will not change the normal to DESC.
Dang... hopefully this makes sense.
Does the viewstate (which I currently have no clue about) help me in this case?

 

It sounds like a shared persistence problem because you have declared the SelectMethod string member as static. As I understand it when you do this in a page in ASP.NET, the value of the member is set and persisted across postbacks but the interesting thing is that the member is shared between different users of the page / website. This means that a web site user can affect the operation of the website for other users, where the last user to set the static member value 'wins'. It's a strange situation, not the behaviour required in most cases and a colleague has described to me a system he worked on where this was the cause of a hard to find bug.

If this sounds like the problem you're having I would recommend following the pattern I highlighted in my last post where the member variable is stored in viewstate. When it's done like this, the value is stored in the page markup for the current user so the value set in the member variable is specific to a single user and that user only.

 

Interesting!!
I copied your code and it solved that little bug, thanks a whole lot.

bool StockSortAsc
{
get { return (bool)ViewState["StockSortAsc"]; }
set { ViewState["StockSortAsc"] = value; }
}

protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
if (!IsPostBack)
StockSortAsc = false;
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (e.SortExpression == "Stock")
{
e.SortExpression = string.Empty;
StockSortAsc = !StockSortAsc;
ObjectDataSource1.SelectMethod = StockSortAsc ?
"GetDataBySortStockDesc":
"GetData" ;

}
}

You can check out that first website of mine if you'd like:
http://www.TheMikesAutoSales.com

 

Super, good work. Your website is looking good and it appears to render equally well in IE8 and FF3.5.2 (just a quick test though). If you're feeling generous, I'll take the 2001 plate SLK230 as payment..as long as you don't mind shipping it to the UK ;-)

I do have one suggestion for you - it would be nice if the 'price' column was formatted as a currency value. It's easy to do with the GridView - just bung something like this string into the price column DataFormatString property to auto-apply currency formatting to the number - {0:C2}. If you have any sort of problem with incorrect formatting when you do this, we might need to tinker with the localization settings for the application but hopefully it should just work fine as is.

 

Hey dude.. that SLK is yours.. it's the least I can do..

Thanks for testing and for the feedback.

The price column used to display as currency (used formatstring = {0,C}. but I believe I messed that up when I changed the type from int to varchar
I did that to be able to add that column to the fulltextsearch index
that not only messed up the formatting but also the sorting (10995 comes before 11995)

so, gotta think about how to solve this if I want to keep it as varchar and keep it available in the text search.. what do you think?

 

Ah right I understand - it wasn't clear that this was a string value. If you need to keep the price as a varchar in the db but would also like to format it as currency for display purposes you can insert this conversion command into your SQL statement (but substituting Cost for the actual name of your price column: CAST(Cost AS money) Cost

In fact 'CAST(Cost AS int) Cost' would also work if your prices are never fractions of a dollar and once the conversion to a number is performed, you will then be able to successfully use the DataFormatString again. In addition because you are converting to a number, column sorting will continue to work as desired

This is one thing to be aware of when doing this however - the conversion will only work if you can be sure the database varchar column will always contain a value that successfully converts to a number. If for any reason you end up with a string value in your price column that does not convert to a number it will blow up on value conversion attempt. Not to worry though - one way to cope with this nasty is to enforce data validation on the web page where you input your prices.

I hope that's clear, but if I rambled on incoherently just let me know.

 

no rambling there, it's all CrystalCMS.. ohh I mean Crystal Clear. and thanks

I tried that cast yesterday and gave up as I didn't know where to put it in the Select statement but with you inspiration, I tried again and it worked.
I had the select statement as:
SELECT Cars.*
FROM Cars
ORDER BY CAST(SUBSTRING(Stock, 3, LEN(Stock) - 2) AS INT)

and again with inspiration from you, I managed to change that to the following and it worked great:
SELECT [Body Style], Description, Engine, [Exterior Color], Features, [Fuel Type], [Interior Color], Make, Mileage, Model, CAST(Price AS int) AS Price, Stock,
Transmission, Type, VIN, Year
FROM Cars
ORDER BY CAST(SUBSTRING(Stock, 3, LEN(Stock) - 2) AS INT)

thanks to you, it's comming along.. it's my first asp.net "and" website, so it's nothing great but it's functional so far and we're getting traffic and it's helping us sell some cars.

I've got a few more things to work on. My slide show isn't working all the times, it's so wierd.. it sometimes displays images from other cars..
I have a feeling it has to do with all the static variables I've used, so I might have to use similar method to the method you showed me earlier..
here's my code, I will be working on this soon:


public partial class Details : System.Web.UI.Page
{
private static FileInfo[] rgFiles;
private static int picNum;
private static int nextPicIndex;
private static string stock;

protected void Page_Load(object sender, EventArgs e)
{
if ((picNum == 0) | (stock != Request.QueryString["Stock"]))
{
stock = Request.QueryString["Stock"];
DirectoryInfo di = new DirectoryInfo(HttpRuntime.AppDomainAppPath.ToString() + "images\\" + stock);
if (di.Exists == true)
{
rgFiles = di.GetFiles("*.jpg");
picNum = 0;
nextPicIndex = 0;
foreach (FileInfo fi in rgFiles)
{
picNum++;
}
}
this.SlideShowImage1.ImageUrl = GetNextImage();
}

}
protected void Page_Unload(object sender, EventArgs e)
{

}

protected void SlideShowTimer_Tick(object sender, EventArgs e)
{
DateTime later = DateTime.Now.AddSeconds(.5);
while (DateTime.Now < later) { }
this.SlideShowImage1.ImageUrl = GetNextImage();
}
private string GetNextImage()
{
if (rgFiles.GetLength(0) > 1)
{
this.Button1.Visible = true;
this.ButtonNext.Visible = true;
this.ButtonPrevious.Visible = true;
this.SlideShowTimer.Enabled = true;
if (nextPicIndex >= rgFiles.GetLength(0))
nextPicIndex = 0;
return "~/images/" + stock + "/" + rgFiles[nextPicIndex++];
}
else
{
this.ButtonNext.Visible = false;
this.ButtonPrevious.Visible = false;
this.Button1.Visible = false;
this.SlideShowTimer.Enabled = false;
return "~/images/mikes/noimage.jpg"; ;
}
}
}

 

It's good to hear you're getting traffic to your website. It can be difficult to develop a useful online presence that people/customers are willing to visit..I think a lot depends on a successful marketing strategy.

You definitely do have the same bug here that you had earlier on the inventory page related to static member variables, but before you fix this problem I think there could be a problem with the code here. It's not to do with the layout / asthetics of the page but it could be related to the slideshow and the refresh mechanism in use.

Have you used an ASP.NET UpdatePanel / AJAX timer combination on this page to enable slideshow refresh? If you have, you could be limiting the scaleability/usability of your website. It's not entirely clear at the moment because I haven't seen the markup code in the aspx page but the code behind seems to indicate this possible problem. I can explain the pitfalls further but I'll wait for a bit more info.

 

Hey Joe.. thanks for the help again
So, I redid that whole thing and made sure I'm using the ajax update panel and timer
and I fixed the bug of the static variables.
It's almost working.. my problem is now when I click a button (pause, play, next, previous) the entire page is getting reloaded. I messed around but I can't figure out why (it wasn't doing that before I updated the whole thing)

I've attached my markup and codebehind..

 

Perhaps I wasn't explicit enough in my last post but I was trying to warn you about the pitfalls of using an <asp:Timer> on a web page to perform partial (or full for that matter) page updates; this is why I suggested it might be worth you waiting to fix the static var bugs until I had some more info from you so I could advise further.

Since you've now posted the entire code for this page I can see that you have fixed the original static variable bugs but the you are indeed using an <asp:Timer> to perform a partial page refresh on a 3 second interval.

The problem with this code / doing this type of thing on a publicly accessible website is:

1. You never know how many visitors are hitting your website at any given point in time
2. As a result of (1) the number of visitors could be large and if we're honest, this is what most website developers are hoping for.
3. If the number of visitors is large then a proportion of these visitors will be viewing details of vehicles on your details page with the timer. This subset of visitors will be putting an excessive load on the webserver degrading overall performance of the website for all users because they are all firing a timer for a partial page postback on a 3 second interval.

A couple of simple examples to demonstrate the problem worsening as the number of visitors grow:

• 10 visitors on your details pages =~ a page post back to your web server every 300 msecs
• 100 visitors on your details pages =~ a page post back to your webserver every 30 msecs

I hope this explains the potential problem clearly. Although what you currently have definitely does work (at least for JavaScript enabled browsers - AJAX is dependant on JS), you could be limiting the overall usability and performance of your system. Extending the interval from 3 seconds upwards can improve things but it still doesn't solve the problem.

Since you are already relying on JavaScript being enabled in the visitors browser with your current method, a much better solution here would be a completely JavaScript enabled solution (all client side code) with no AJAX / postback at all. If you go this route then it entirely solves the scaleability problem.

If you're not concerned about this potential problem but you still want to fix your pause / play / next button postback problem, just move these buttons into the the UpdatePanel ContentTemplate and that will solve that one.

 

thanks for the info. It seems that I misunderstood your post, it was my fault.
so, I read some on how to use Javascript (haven't used it before). Depends on my code and if I uses FileSystemObject, the user might also have to accept activex script.

Is there another way where I don't expect the user to have javascript enabled? Or should I always expect them to have that enabled?

 

I'm pretty sure that JavaScript is the most widely supported method of implementing image slideshow functionality in a website. Of course there will always be some users who have JavaScript disabled in the browser and in these cases the slideshow JS code should gracefully degrade to display a single image that does not rotate and also hide any play / pause buttons etc.

One thing to keep in mind is that if a user does have JavaScript disabled, your current AJAX timer method also doesn't work. You can easily test this behaviour by disabling JavaScript in your own browser.

There are indeed other ways to implement a slideshow - Flash, Silverlight etc. Some very impressive results can be achieved here but these methods do require a plugin to be installed in the browser and although many users will have these plugins readily installed, some will not. Since browser plugins are downloadable installs they can considered more intrusive and not as easy for web developers to support as JavaScript which is natively supported in all popular browsers. I'm not suggesting that Flash and/or Silverlight content is to be avoided at all costs - in fact I'm actually a Silverlight fan for applications where it is a good fit, but I do think these technologies could be overkill for a simple image slideshow.

Anything that requires ActiveX is to be avoided like the plague in my opinion and I wouldn't recommend using any code that uses or depends on ActiveX technology. It is generally only supported in Internet Explorer and only Microsoft knows for how much longer.

If you're looking for a quick start to enable a pure JavaScript image slideshow in your website, there are many free scripts available for download that are unrestricted for commercial use. Here's one I found which looks quite nice: http://www.gerd-tentler.de/tools/slideshow/

Having had sight of your code, the key for you to get any JS slideshow successfully working will be enabling your details.aspx page code-behind to intelligently render down the image paths into JavaScript into the page markup so it can be picked up and used by the slideshow JavaScript code.

 

Hey joe.. thanks for the help..
so.. use javascript to run the slideshow (to take some load off the server)
use code behind to get the list of images and send them to the javascript

I started working on this yesterday right after your post.. it will take me a couple of days as my time is limited.. but i'll keep you posted..

I'm putting the javascript in a seperate file and the details.aspx will first get the list of files ( on page_prerender), put them as a long string and send this list when it registers the javascript as a startup script. I'll see how that ends up as I get close...

good stuff!!