CODEWITHSUNDEEP
vb.netoracle-database
LuckyLuke82
LuckyLuke82

Reputation: 606

Dynamic SQL - retrieve both checkbox values from DB

I'm retrieving data from DB when User selects checkboxes. Values in DB are "YES" or "NO". They both work fine separately, but If I click both checkboxes, I want to view all records that have both values. Here is my code:

 Using cmd As New OracleCommand()

                Dim SQL As String = "Select * FROM MyTable "
                Dim Bind_SQL As String = " Where "

                Dim Activity As String

                If ChkActiveYES.Checked = True Then
                    Activity = "YES"
                    SQL = String.Concat(SQL, Bind_SQL, " ACTIVE_PLAYER = :activity")
                    cmd.Parameters.Add(New OracleParameter("activity", Activity))
                    Bind_SQL = " and "

                ElseIf ChkActiveNO.Checked = True Then
                    Activity = "NO"
                    SQL = String.Concat(SQL, Bind_SQL, " ACTIVE_PLAYER = :activity")
                    cmd.Parameters.Add(New OracleParameter("activity", Activity))
                    Bind_SQL = " and "

                ElseIf ChkActiveYES.Checked = True And ChkActiveNO.Checked = True Then
                    Activity = "NO"
                    SQL = String.Concat(SQL, Bind_SQL, " ACTIVE_PLAYER = :activity")
                    cmd.Parameters.Add(New OracleParameter("activity", Activity))
                    Bind_SQL = " and "

                    Activity = "YES"
                    SQL = String.Concat(SQL, Bind_SQL, " ACTIVE_PLAYER = :activity")
                    cmd.Parameters.Add(New OracleParameter("activity", Activity))
                    Bind_SQL = " and "

                End If
End using

What am I missing here ?

Upvotes: 1

Views: 419

Answers (2)

jmcilhinney
jmcilhinney

Reputation: 54457

How can any record have an ACTIVE_PLAYER value of both YES and NO? It can't, so you'll get no matches. You have to use OR to combine those two criteria.

Also, you're adding the same parameter name twice. You'd need to add two parameters with two different names if you want two different values.

I would suggest a different approach, which will address the issues both Crush Sundae and I have raised. Just use all values all the time and then the SQL does need to be dynamic, e.g.

cmd.CommandText = "SELECT * FROM MyTable WHERE (:p1 IS NOT NULL AND MyColumn = :p1) OR (:p2 IS NOT NULL AND MyColumn = :p2)"
cmd.Parameters.Add("p1", OracleDbType.VarChar).Value = If(ChkActiveYES.Checked, "YES", CObj(DBNull.Value))
cmd.Parameters.Add("p2", OracleDbType.VarChar).Value = If(ChkActiveNO.Checked, "NO", CObj(DBNull.Value))

Upvotes: 2

Aethan
Aethan

Reputation: 1985

It's not going to this part:

...
ElseIf ChkActiveYES.Checked = True And ChkActiveNO.Checked = True Then

because it already enters here:

If ChkActiveYES.Checked = True Then

Put the checking of if both are checked in the first part of your if statement.

Upvotes: 2

Related Questions