Sorting using Order By in Vb.net

Question

Trying to sort records using ORDER BY in vb.net but I am not getting the required result. I am fetching records from two different tables but sorting use a column from a single table.

CODE

'query data
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT biodata_info.student_id, sname, mname, fname, passport, " & _
                            " result_score FROM biodata_info LEFT JOIN [" & connResultLink & "].result_summary ON biodata_info.student_id = result_summary.student_id " & _
                            "WHERE result_term = @resultTerm  AND result_session = @resultSession AND result_class = @resultClass AND result_arm = @resultArm " & _
                            " ORDER BY result_summary.result_score ASC", connStudent)

    cmd.Parameters.AddWithValue("@resultTerm", term1)
    cmd.Parameters.AddWithValue("@resultSession", session1)
    cmd.Parameters.AddWithValue("@resultClass", className1)
    cmd.Parameters.AddWithValue("@resultArm", armName1)

RESULT using ASC

<1> 1011
<2> 863
<3> 911
<4> 985

RESULT using DESC

<1> 985
<2> 911
<3> 863
<4> 1011

Possible duplicate of https://stackoverflow.com/questions/489874/sql-order-chars-numerically — Saragis, Jul 21 '15 at 08:29
Seems to me like your `result_summary.result_score` column is not a numeric data type, but a string data type. try using `CInt(result_summary.result_score)` in the order by clause. — Zohar Peled, Jul 21 '15 at 08:31
@BlackPearl: but it would be much better to use the correct datatype in the first place. — Tim Schmelter, Jul 21 '15 at 08:35
@TimSchmelter [`cInt` in ms-access...](https://support.office.com/en-sg/article/Type-Conversion-Functions-8ebb0e94-2d43-4975-bb13-87ac8d1a2202) — Zohar Peled, Jul 21 '15 at 08:35
@TimSchmelter yea, it seem to work even I was suspicious at first but with VB.NET, you never know. — BlackPearl, Jul 21 '15 at 08:35

score 2 · Accepted Answer · answered Jul 21 '15 at 08:44

Seems to me like your result_summary.result_score column is not a numeric data type, but a string data type. try using CInt(result_summary.result_score) in the order by clause.

Read this link about type conversions in ms-access (and other office products)

score 1 · Answer 2 · answered Jul 21 '15 at 09:00

1

if 'result_score' is a string field use cast(result_score as Unsigned) . But if 'result_score' is a numeric field and still you are not getting result in ascending order, try abs(result_score).

Hope this will help.

answered Jul 21 '15 at 09:00

PARITOSH THAPLIYAL

51
6

Sorting using Order By in Vb.net

2 Answers2