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.

You dont need to open the connection if u are using SqlDataAdapter, because it does it byitself — C0d1ngJammer
– C0d1ngJammer, Commented Dec 30, 2014 at 11:08
Just a question. What would you do if you have to pass an value to your Select statement? Lets say you have to select a row where the userid is 45. How would you pass the 45? — C0d1ngJammer
– C0d1ngJammer, Commented Dec 30, 2014 at 11:14
Well I will tell you just for the case, because you cant avoid it :). "SELECT userid FROM user_info where userid = @value" user_info"dadapter.SelectCommand.Parameters.AddWithValue("@value",45) — C0d1ngJammer
– C0d1ngJammer, Commented Dec 30, 2014 at 11:26

Steve · Accepted Answer · 2014-12-30 11:06:48Z

1

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

answered Dec 30, 2014 at 11:06

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Asher Malcom Over a year ago

Had an error "Object reference not set to an instance of an object". after giving value for selectColumns its ok .

Asher Malcom Over a year ago

Steve, Thank you so much. Learning programming in my spare time.

bastos.sergio · Accepted Answer · 2014-12-30 11:15:26Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related