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 stackoverflow.com/questions/489874/sql-order-chars-numerically — Saragis
– Saragis, Commented Jul 21, 2015 at 8: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
– Zohar Peled, Commented Jul 21, 2015 at 8:31
@BlackPearl: but it would be much better to use the correct datatype in the first place. — Tim Schmelter
– Tim Schmelter, Commented Jul 21, 2015 at 8:35

Zohar Peled · Accepted Answer · 2015-07-21 08:44:21Z

2

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)

answered Jul 21, 2015 at 8:44

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PARITOSH THAPLIYAL · Accepted Answer · 2015-07-21 09:00:44Z

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, 2015 at 9:00

PARITOSH THAPLIYAL

516 bronze badges

Collectives™ on Stack Overflow

Sorting using Order By in Vb.net

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related