VB Give integer value to column based on sort

[GideonE]

I am building a VB.Net applications using SQL server and cannot figure the following out :

I need to give a sequential integer (starting from 1) to a column in the database table (column called Prioriteit) based on a sort that is as follows :

First column kultivar (i.e. string Ascending), then GroepperingsGroep (i.e. string Ascending), Then LaasteOntledingsdatum (i.e. date oldest to newest) and lastly Laastesuiker (i.e. real largest to smallest). According to this sort integer values should be given to Prioriteit for each GroepperingsGroep. I am attaching a picture to explain the end result needed

.


Regards

[jmcilhinney]

Execute a query against the database using an appropriate ORDER BY clause. Use a data adapter to populate a DataTable. Use a For loop to iterate over the Rows of that DataTable and set that Prioriteit column based on the loop counter. When done, use the same data adapter to save the changes. Note that the only columns you need to retrieve are the primary key and the one you want to modify.

[GideonE]

Thank you jmcilhinney. Came up with the following :

SQL :

;WITH CTE AS 
(
    SELECT ROW_NUMBER() OVER (PARTITION BY kultivar,GroepperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) NewPrioriteit,
           *
      FROM myTable
)
UPDATE CTE
   SET CTE.Prioriteit = CTE.NewPrioriteit

VB.Net :

Dim konneksie As New SqlConnection
        Dim opdragprioriteit As New SqlCommand
        konneksie.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;Integrated Security=True"
        konneksie.Open()
        opdragprioriteit.Connection = konneksie
        opdragprioriteit.CommandText = "WITH CTE AS " &
        "  (Select Case ROW_NUMBER() OVER (PARTITION BY kultivar,GroeperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) NewPrioriteit, * " &
        " From skedulering2)" &
        " Update CTE" &
        " SET CTE.Prioriteit = CTE.NewPrioriteit "
 
 
        opdragprioriteit.ExecuteNonQuery()
        konneksie.Close()

Working in SQL, however I get a error message (Incorrect syntax near 'NewPrioriteit'). What syntax am I missing?

Regards and thank you.

[GideonE]

Thank you jmcilhinney. Came up with the following :

SQL :

;WITH CTE AS 
(
    SELECT ROW_NUMBER() OVER (PARTITION BY kultivar,GroepperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) NewPrioriteit,
           *
      FROM skedulering2
)
UPDATE CTE
   SET CTE.Prioriteit = CTE.NewPrioriteit

VB.Net :

Dim konneksie As New SqlConnection
        Dim opdragprioriteit As New SqlCommand
        konneksie.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Administrator\Desktop\SKEDULERING\Skedulering6\Skedulering6\SkeduleringsDatabasis6.mdf;Integrated Security=True"
        konneksie.Open()
        opdragprioriteit.Connection = konneksie
        opdragprioriteit.CommandText = ";WITH CTE AS " &
        "  (Select Case ROW_NUMBER() OVER (PARTITION BY kultivar,GroeperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) NewPrioriteit, * " &
        " From skedulering2)" &
        " Update CTE" &
        " SET CTE.Prioriteit = CTE.NewPrioriteit "
 
 
        opdragprioriteit.ExecuteNonQuery()
        konneksie.Close()

Working in SQL, however I get a error message in VB.Net (Incorrect syntax near 'NewPrioriteit'). What syntax am I missing?

Regards and thank you.

[techgnome]

What you're doing wrong is that you can't create a CTE and update... because you can't update a CTE... I'm not even sure why you created the CTE in the first place...

Select Case ROW_NUMBER() OVER (PARTITION BY kultivar,GroeperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) NewPrioriteit, * " &
        " From skedulering2

And as I paste that, I see that you used a CASE statement... ????? why? No...

Select 
    kultivar,
    LaasteOntledingsdatum,
    Laastesuiker,
    GroepperingsGroep,
    ROW_NUMBER() OVER (PARTITION BY kultivar, GroeperingsGroep ORDER BY LaasteOntledingsdatum Asc, Laastesuiker Desc) Prioriteit
From skedulering2

There... nothing complicated about it...

-tg

[GideonE]

Thank you technome. Your code works well to select, but how do then do I update skedulering2?