vb.net SQL command to sort SQL table

Question

I have the following code in vb.net to sort a SQL Server table alphanumerically. It works in SQL Server Management Studio, but when I run the code in vb.net nothing happens.

Do I need to update the SQL Server table and how? Can I even do this directly from vb.net?

  Dim opdragsorteeraplhanumeries As New SqlCommand
    konneksie.ConnectionString = "Data Source=GIDEON-E-LAPTOP\SQLEXPRESS2014;Initial Catalog=SkeduleringDatabasis;Integrated Security=True"
    konneksie.Open()
    opdragsorteeraplhanumeries.Connection = konneksie
    opdragsorteeraplhanumeries.CommandText = "SELECT * FROM Oesskattings " & _
    "ORDER BY " & _
        "CASE WHEN ISNUMERIC(blokno) = 1 THEN right(Replicate('0',21) + blokno, 21) " & _
           "WHEN ISNUMERIC(blokno) = 0 then Left(blokno + Replicate('',21), 21) " & _
          " ELSE blokno " & _
        " End "

    opdragsorteeraplhanumeries.ExecuteNonQuery()
    konneksie.Close()
    MsgBox("Alphanumeries gesorteer")

Regards

You're just not reading the results. Try opdragsorteeraplhanumeries.ExecuteQuery() and read the result set. — Wagner DosAnjos
– Wagner DosAnjos, Commented Nov 3, 2016 at 4:34
You can't change the order of records, you can only select them in a certain order. — artm
– artm, Commented Nov 3, 2016 at 4:35
What do you mean nothing happens? Did you mean there is no data? If you want to see the return value of your select statement, you should use ExecuteScalar, not ExecuteNonQuery. ex. opdragsorteeraplhanumeries.ExecuteScalar(). Then, put it in a dataset or datatable. — jelliaes
– jelliaes, Commented Nov 3, 2016 at 5:29
You cannot "sort" a table in SQL Server - tables are inherently unordered, you can get data back in an ordered fashion when you select from the table and explicitly specify an ORDER BY clause — marc_s
– marc_s, Commented Nov 3, 2016 at 7:01

jmcilhinney · Accepted Answer · 2016-11-03 09:35:58Z

2

There's no sorting of a table going on there. Whether that code is executed in SSMS or not, what it is doing is getting the data from the table and sorting it. The table remains exactly as it was. If it is your intention to actually change the order of the data in the table then you'd have to move the data from original table into a temp table and then back again, doing the ordering on one of the two steps.

Would there really be any point to that though? Why exactly do you think you need the table sorted a particular way and, if that's really what you need, why isn't it like that to begin with? More likely you should simply sort the data when you retrieve it or perhaps create a view that does the sorting and get your data from there.

edited Nov 3, 2016 at 9:35

answered Nov 3, 2016 at 4:46

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

vb.net SQL command to sort SQL table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related