Question SQL ORDER BY with ComboBox

[joefa]

Hi

I've got a TSQL view which looks like this

SELECT TOP 100 PERCENT --Needed for the ORDER BY clause
	N.NHSReasonID
	,N.Reason
	,N.Notes
	,N.TopValue
FROM
	dbo.NHSReason N
ORDER BY
	N.TopValue DESC
	,N.Reason

In my VB.Net application, I have a ComboBox which uses the above as its datasource. The reason I've used the view rather than just bind it directly to the table is because I would like the values displayed in the drop-down in the order returned from this view. It is set up like this

Dim NHSReasonAdapter As New TopManDataSetTableAdapters.NHSReasonsInOrderTableAdapter
Dim NHSReasonTable As New TopManDataSet.NHSReasonsInOrderDataTable
....
NHSReasonAdapter.Fill(NHSReasonTable)
....
cmbNHSReason.DataSource = NHSReasonTable
cmbNHSReason.DisplayMember = "Reason"
cmbNHSReason.ValueMember = "NHSReasonID"

But I can't get this to work, the values are still displayed to the user in the order of their SQL primary key value, rather than the order I specified in the ORDER BY clause. Please can anyone suggest what I could be doing wrong?

Thanks

 

[joefa]

Ahhh, don't worry, I've got it. Classic case of explaining it to others causes you to realise what the problem is yourself.

If anyone is interested, SQL views do not like the ORDER BY clause, because they are intended to return unordered data. Therefore, although I had used a workaround to make it syntactically acceptable, the custom DataSet was just ignoring the ORDER BY.

The solution I found is to order the data in the front end, using ComboBox.DefaultView:

NHSReasonTable.DefaultView.Sort = "TopValue DESC, Reason"
....
cmbNHSReason.DataSource = NHSReasonTable.DefaultView

Hope this proves useful for someone out there.