SQL: Windowed functions can only appear in the SELECT or ORDER BY clauses

Use a CTE. Problem could occur in

WITH myCTE AS
(
SELECT WO, QuoteNo, EntryNo = ROW_NUMBER() OVER (PARTITION BY QuoteNo ORDER BY WO)
FROM dbo.Orders2006
WHERE QuoteNo IS NOT NULL
)

UPDATE dbo.Orders2006
SET EntryNo = myCTE.EntryNo
FROM dbo.Orders2006
INNER JOIN myCTE ON myCTE.WO = dbo.Orders2006.WO

 

With thanks to:

http://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause

Advertisements

DataTable detect column changed using ColumnChanged event

Background

Because of disconnected nature of VB.Net databinding I have users who have come from MS Access instant save who in .Net are concerned that they fogot to press save. Hence I added CheckForChangesAndPromptForSave. However this is firing too much. I have too much code and something must be changing a column but need to detect which and probably stop changing it.

Solution

Open Dataset and select table with changes. View Code. This generates new code window if not present. On table event use the ColumnChanged event.

Partial Class dsBusiness
    Partial Class QuoteDataTable

        Private Sub QuoteDataTable_ColumnChanged(sender As Object, e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanged
            MsgBox(e.Column.ColumnName)
        End Sub

    End Class
End Class