CODEWITHSUNDEEP
sqlvb.net
Gideon
Gideon

Reputation: 313

sql update giving wrong results

I have the following sql statement in VB.NET

   Dim READER As SqlDataReader

    Try
        konneksie.Open()
        Dim Query As String

        Query = "Update blokkeklaar " & _
        " set Klaarvb = 'JA' " & _
        " where KlaarEZY = 'YES'" & _
        " Update blokkeklaar " & _
        " set Klaarvb = 'NEE' " & _
        "  where KlaarVB IS NULL"

        COMMAND = New SqlCommand(Query, konneksie)
        READER = COMMAND.ExecuteReader
        MessageBox.Show("KlaarVB verander ")
        konneksie.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
    End Try
    konneksie.Close()

I cannot understand why klaarvb is populated with "NEE" where klaarvb is not null?

Upvotes: 1

Views: 57

Answers (2)

JohnHC
JohnHC

Reputation: 11195

You're trying to do 2 separate updates with one statement. If you want a single statement solution, try:

update blokkeklaar 
set Klaarvb = case
                when KlaarEZY = 'YES' then 'JA' 
                when KlaarVB is null then 'NEE' 
                else Klaarvb -- Thanks hurcane
              end;

This evaluates in order, and exits when condition is met, so if the first condition is true, the second will not be evaluated or applied.

Upvotes: 2

apomene
apomene

Reputation: 14389

you have to put semicolon between the statements or execute them separately:

 Query = "Update blokkeklaar " & _
        " set Klaarvb = 'JA' " & _
        " where KlaarEZY = 'YES';" & _ //<--put semiColon here
        " Update blokkeklaar " & _
        " set Klaarvb = 'NEE' " & _
        "  where KlaarVB IS NULL"

Also as a side note, you better use ExecuteNonQuery instead of ExecuteReader

Upvotes: 2

Related Questions