Datatable not showing results in same order from SQL Query result

Question

Hi I am working on ASP.NET MVC Project. I am using datatable to display customer details. In controller I am calling one stored procedure which is taking customer details in asc order. Like :

    SELECT id, firstname, lastname, colnum
    FROM TABLE1
    ORDER BY colnum ASC

colnum has results of 1,2,3,4,5. So I get results in order of 1,2,3,4,5. So i get results as like below when i debug :

    <thead>
     <tr>
     <th>
      id
      </th>
      <th>
      firstname
       </th>
       <th>
       lastname
      </th>

      </tr>
      </thead>
      <tr>
      <td> csv1 </td>
      <td> fname1 </td>
      <td> lname1 </td>
      </tr>
      <tr>
      <td> csv2 </td>
       <td> fname2 </td>
      <td> lname2 </td>
      </tr>
      <tr>
      <td> csv3 </td>
      <td> fname3 </td>
      <td> lname3 </td>
      </tr>
      <td> csv22 </td>
      <td> fname4 </td>
      <td> lname4 </td>
      </tr>

This is result I get when i debug. I have this result in Viewdata.

But when i bind this Viewdata to datatable, I get results based on alphabetical order of id.

That is like this :

<tr>
<td> csv1 </td>
<td> fname1 </td>
<td> lname1 </td>
</tr>
<tr>
<td> csv2 </td>
<td> fname2 </td>
<td> lname2 </td>
</tr>
<tr>
<td> csv22 </td>
<td> fname4 </td>
<td> lname4 </td>
</tr>
<td> csv3 </td>
<td> fname3 </td>
<td> lname3 </td>
</tr>

As you can see, in datatable I get results like csv1, csv2, csv3, csv33, csv34, csv4, csv5. But I need to display results like csv1, csv2, csv3, csv4, csv5, csv33, csv44.

I am assuming it is datatable functionality of taking results based on alphabetical order. How can I change this to achieve result what I want?

possible duplicate of [Is there a way to disable initial sorting for jquery DataTables?](http://stackoverflow.com/questions/4964388/is-there-a-way-to-disable-initial-sorting-for-jquery-datatables) — Daniel J.G., Sep 25 '14 at 11:21
@DanielJ.G. I think it is not duplicate question. My requirements was different compare to post which you mentioned. — Ajay, Sep 25 '14 at 15:24
I might be missing something, but you are getting the results already in the order that you wanted from the database, which is based on the `colnum` column. So, don't you just need to avoid datatables sorting again your results? Have you tried using `"aaSorting": []`? — Daniel J.G., Sep 25 '14 at 15:28
@DanielJ.G. You are right I am getting results what i need in colnum. But I dont want this column to be displayed and I want to sort other column based on colnum result. So i used visible approach. — Ajay, Sep 25 '14 at 15:56

score 0 · Answer 1 · answered Sep 25 '14 at 15:24

After searching online I found answer for this. I am posting this thinking may be helpful for others.

As I mentioned in post, my problem was, colnum result. While selecting from stored procedure I am selecting result based on colnum but while displaying results into datatable I was not binding colnum results to datatable.

So it used to sort based on id column where I had results like, csv1, csv2, csv23, csv24, csv4, etc.

So solution for this I used is bind colnum result to datatable but make it hidden. And while sorting in datatable, do sort based on colnum result for id column .

This is the code i used :

  $('#results').dataTable({
        "aoColumnDefs": [


             { visible: false, targets: [0] },
           { targets: [ 1 ], orderData: [ 0 ] }

        ]
    });

so here, 1st column is colnum which is not visible and for column 2 that is id i am using sorting based on column 1, that is colnum.

Hope it will be useful for others.

Datatable not showing results in same order from SQL Query result

1 Answers1