vb.net database query and checkbox , proper way to do?

Question

I am new to Vb.net and Sql Server(don't be harsh). Can anybody check my code, i feel it is wrong way to do. The purpose is to show query only if check-box is clicked and shows that query. Thank you very much.

 Private Sub btnFilter_Click(sender As Object, e As EventArgs) Handles btnFilter.Click

    CheckValue = 0 'set to value 0 
    'check if any checkbox is checked.
    For Each C As Control In Me.Controls
        If C.GetType Is GetType(CheckBox) Then
            Dim cb As CheckBox = DirectCast(C, CheckBox)
            If cb.Checked Then
                CheckValue = CheckValue + 1 'only works if value is above 1
            End If
        End If
    Next


    If CheckValue <> 0 Then   'only if check box is checked then rest will run

        Try
            If chkboxId.Checked = True Then 'assign value to checkbox
                chkboxIdstr = "userid"
            Else
                chkboxIdstr = ""
            End If

            If chkboxName.Checked = True Then
                chkboxNamestr = "username"
            Else
                chkboxNamestr = ""
            End If

            'to insert comma, only if both are true.
            If chkboxId.Checked = True And chkboxName.Checked = True Then
                SQLcomma = " , "
            Else
                SQLcomma = ""
            End If

            'so if both are checked we get a comma
            SQL2 = chkboxIdstr + " " + SQLcomma + " " + chkboxNamestr
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Try 'sql connect and display
            sqlCon.Open()
            SQL = "SELECT " + SQL2 + " FROM user_info"

            Dim dadapter As New SqlDataAdapter(SQL, sqlCon)
            Dim dset As New DataSet
            sqlCon.Close()
            dadapter.Fill(dset)
            'fill datagrid
            dgv.DataSource = dset.Tables(0)

        Catch ex As Exception
            MsgBox(ex.Message)
            sqlCon.Close()
        End Try
    Else
        MsgBox("Plesase do check any box to filter ")
        Exit Sub

    End If
End Sub

-- I learn by doing small application(trail and error), mostly google search for solutions.

Answer 1

Try this:

Private Sub btnFilter_Click(sender As Object, e As EventArgs) Handles btnFilter.Click

    If chkboxId.Checked OrElse chkboxName.Checked Then
        SQL2 = String.Empty
        If chkboxId.Checked Then
            SQL2 = SQL2 + "userid"
        EndIf

        If chkboxId.Checked AndAlso chkboxName.Checked Then
            SQL2 = SQL2 + " , "
        End If

        If chkboxName.Checked Then
            SQL2 = SQL2 + "username"
        End If

        Try 'sql connect and display
            sqlCon.Open()
            SQL = "SELECT " + SQL2 + " FROM user_info"

            Dim dset As New DataSet
            Using dadapter As New SqlDataAdapter(SQL, sqlCon)
                dadapter.Fill(dset)
            End Using

            'fill datagrid
            dgv.DataSource = dset.Tables(0)

          Catch ex As Exception
             MsgBox(ex.Message)
          Finally
             sqlCon.Close()
          End Try
    Else
        MsgBox("Please do check any box to filter ")
    End If

End Sub

Answer 2

I don't think you need all that code. Just test your checkbox one by one and build incrementally the list of columns to add to the SELECT query....

Dim selectColumns As String
Try

    If chkboxId.Checked = True Then 
        selectColumns = "userid,"
    End If

    If chkboxName.Checked = True Then
        selectColumns &= "username,"
    End If

    ' Other columns???? follow the same pattern

    ' If we have something in selectColumns then we could execute the query
    ' but before we trim away the last comma
    If selectColumns.Length > 0 Then 
       selectColumns = selectColumn.TrimEnd(",")

       sqlCon.Open()
       SQL = "SELECT " & selectColumns & " FROM user_info"
       .....
    Else
       ' no data in selectColumns? Message for you user
       MsgBox("Plesase do check any box to filter ")
       Exit Sub
    End If    
Catch ex As Exception
    MsgBox(ex.Message)
    sqlCon.Close()
End Try