I have a table field that only contains numbers. but its data type is varchar. So when I do a sort on this field, 2 would come after 111. I wonder whether it is possible to treat the values as Integer during creation of sort expression. I am using adapter.

Thanks.

I would do client side sorting using a custom comparer that pads the fields with 0 on the left for as much as needed.

customers.Sort((int)CustomerFieldIndex.SomeCharacterNumber, ListSortDirection.Ascending, new PaddedNumberComparer());

class PaddedNumberComparer: IComparer
{
    public int Compare(object x, object y)
    {
        string paddedx = x.ToString().PadLeft(25, '0');
        string paddedy = y.ToString().PadLeft(25, '0');
        return paddedx.CompareTo(paddedy);
    }
}

If you have decimals then you will want to take those into consideration also for the comparer.

I am using paging. So I need to sort before records retrieval.

You may use a database View that converts the char values into a Int values. And then you may deal with the View instead of the table.

Otherwise, I can't see anyway to do it but Brian's way (bclubb).

I happen to be using a view for this query. One more question: If I do the data type conversion in Select list, do I need to do a regen to make it work?

Thanks a bunch!!!

I believe you will since it should no longer be using a string so if you do any filtering you would be comparing ints to strings. It would be best and most likely required to regenerate the projects. Let us know if you have any other issues.