how to sort the data in data set i use vb.net and sql database

Question

how to sort the data in data set i use vb.net and sql database my code

 Dim SQLSTATMENT As String = "SELECT * FROM   CODES WHERE LEVEL = " & i & ""
        Dim ds As New DataSet
        Dim dataadapter As New OleDbDataAdapter(SQLSTATMENT, CONn)
        dataadapter.Fill(ds, "codes")


        ds.Tables(0).DefaultView.Sort = "mcode DESC"


        With DataGridView1
            .DataSource = ds
            .DataMember = "CODES"
        End With

you could add it to your SQL query like ORDER BY column1, column2, ... ASC|DESC; - if you just want to use the DataGridView for sorting then you should check out the DataGridView.SortOrder Property — Marco Sadowski
– Marco Sadowski, Commented Feb 22, 2018 at 12:41
Your code seems to be ok. What's the problem? It just don't show it in order? Try setting the order (ds.Tables(0).DefaultView.Sort = "mcode DESC") after setting the Datagridview data source (after the With block) — Pikoh
– Pikoh, Commented Feb 22, 2018 at 12:43

Pikoh · Accepted Answer · 2018-02-22 12:55:38Z

1

You have different possibilities:

Sort on DataView:

'use the table name to avoid using another table on index 0.
Dim dvTable As DataView = ds.Tables("CODES").DefaultView
dvTable.Sort = "mcode DESC"

With DataGridView1
    .DataSource = dvTable
End With

Sort on SELECT:

Dim SQLSTATMENT As String = "SELECT * FROM CODES WHERE LEVEL = " & i & " ORDER BY mcode DESC"

Sort on DataGridView:

With DataGridView1
    .DataSource = ds
    .DataMember = "CODES"
    .Sort(DataGridView1.Columns(DataGridView1.Columns("mcode").Index), ListSortDirection.Descending)
End With

edited Feb 22, 2018 at 12:55

Pikoh

7,72331 silver badges56 bronze badges

answered Feb 22, 2018 at 12:51

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

3 Comments

Pikoh Over a year ago

What is the difference between your first possibility and OPs code? PD. I edited your answer just to set the code syntax to vb, as the color highlighting was wrong

Sebastian Brosch Over a year ago

Yeah, I see. The first one is a more secure way not using the table index (numeric).

Pikoh Over a year ago

Well yes, but as OP is filling the dataset just in that moment, index 0 is going to be the codes datatable. I don't think that has anything to do with the sort problem :)

Collectives™ on Stack Overflow

how to sort the data in data set i use vb.net and sql database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related