Sorting databound textboxes in Gridview numerically

Question

I currently have a gridview that is populated by a stored procedure. This stored procedure can not be altered. On my page, I have a drop down box. This dropdown contains all of the column names for the gridview. When a user clicks on a value, the gridview is sorted by that column.

All of that is no problem. However, the problem occurs when I try to sort one of the columns chronologically. I have this:

<asp:TemplateField HeaderText="RollNo" SortExpression="RollNo" >
        <ItemTemplate>
             <asp:TextBox ID="RollNoBox" runat="server" MaxLength="2" AutoCompleteType="None"  Width="35px"
               Text='<%# Bind("RollNo") %>'>
             </asp:TextBox>
        </ItemTemplate>
 </asp:TemplateField>

When I click on this column to sort, it behaves like this:

    Original:               Actual Sorted:  
    1                             null
    2                             null
    3                             null
    4                             null
    5                             1
    6                             2
    7                             3
    8                             4
    9                             5
    10                            6 
    11                            7 
    null                          8
    null                          9
    null                          10
    null                          11

However, I want the sort to be the SAME as the original (So I can add additional filters).

How can I do this?

Here is my sort function:

Protected Sub SortGrid()
    Dim sortexpression As String
    If DropDownList1.SelectedValue <> "Select" Then
        sortexpression = DropDownList1.SelectedValue
    End If

        M1SchedView.Sort(sortexpression, SortDirection.Ascending)

End Sub

This is a bit altered from my actual function as I have multiple sorts in my page.

EDIT: I was able to get the SP edited so that the column is now an integer. However, now it places the null values before the numbers. If this could be switched, I would be set.

Your "RollNo" field is a string instead of a number, that why it sorts like that. If you cannot change the SP so the value is numeric, then you must change it in your code. If you are using some type of EF, this should be easy. If your using a sqldatasource and letting it fill the data, this isn't as easy. — Steve, Aug 14 '14 at 14:30
I'm using a sqldatasource which calls the stored procedure.. — Jack, Aug 14 '14 at 14:34

score 2 · Accepted Answer · answered Aug 14 '14 at 14:31

2

RollNo seems to be a string

add another property int RollNoNumeric and define it so

public int RollNoNumeric
{
    get { return string.IsNullOrEmpty(this.RollNo) 
              ? int.MaxValue 
              : Convert.ToInt32(RollNo); }
}

then change your SortExpression to

SortExpression="RollNoNumeric"

answered Aug 14 '14 at 14:31

esskar

10,638
3
36
57

I'm using VB.Net, is this language specific? Also, I don't know how to implement this, I've never done that before. – Jack Aug 14 '14 at 14:33
This is the solution if you need custom sorting around NULL values, however I would recommend a name of `RollNoForSort` or the like, as you are giving an (arbitrary) value to NULL values, so `RollNoNumeric` may be a bit misleading to another dev later on. A `RollNoNumeric` of type `Nullable(Of Int32)` would be more appropriate for that name, I'd think. – Mike Guthrie Aug 14 '14 at 15:21

score 2 · Answer 2 · answered Aug 14 '14 at 14:38

2

RollNo is a string, convert it to a number

<%# Bind("RollNo", "{0:N2}") %>

http://msdn.microsoft.com/en-us/library/2d76z3ck(v=vs.110).aspx

answered Aug 14 '14 at 14:38

JLane

504
2
11

Are you using a datatable as a datasource? – JLane Aug 14 '14 at 14:43
No, I am using a sqldatasource which calls a stored procedure that returns the data. – Jack Aug 14 '14 at 14:44
Just a question to possibly make this a little easier, but are you using the text-box to allow user input? Would you be able to make a datatable as the datasource? The issue is the column RollNo in the datasource is being considered a string. Edit:What data-type is RollNo in the database? – JLane Aug 14 '14 at 14:53
The textbox is used to allow user input, and not I can't use a datatable. The stored procedure is a necessary evil due to the other functions it performs. EDIT: However, I was able to get the SP edited so that the column is now an integer. However, now it places the null values before the numbers. If this could be switched, I would be set. – Jack Aug 14 '14 at 14:55
1

You need to change your stored procedure to pull it as an int then – JLane Aug 14 '14 at 14:57
I did, however it still places null values before the integers. Could I somehow have them put after? – Jack Aug 14 '14 at 14:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/59364/discussion-between-jlane-and-wyatttroutman). – JLane Aug 14 '14 at 14:59
1

The only way I know of to do this gets to be way more code than worth doing when you can just call the procedure into a datatable to change before databinding it to the gridview. Here is how one person did it: http://forums.asp.net/t/1415170.aspx?Gridview+sort+by+column+but+put+NULL+s+at+bottom+ – JLane Aug 14 '14 at 15:10

Sorting databound textboxes in Gridview numerically

2 Answers2