CODEWITHSUNDEEP
vb.netsortingms-access
BlackPearl
BlackPearl

Reputation: 2795

Sorting using Order By in Vb.net

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

Upvotes: 0

Views: 1406

Answers (2)

PARITOSH THAPLIYAL
PARITOSH THAPLIYAL

Reputation: 51

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.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

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)

Upvotes: 2

Related Questions