Create an SQL SELECT statement based on checkbox entries - Visual Basic

Question

I am fairly new to programming! and I am using Visual Basic Studio 2017 to make a windows form

I have a form with 2 radio buttons, 6 checkboxes, and 1 combo box. I have the data in an access database. The point is when one of the radio buttons is chosen, only certain data from the database gets filled in the combo box.

My database has 8 columns, the first column is an id column. It has data like this for example. Burger-FR001. Burger-GR001. Burger-GR002. Sandwich-FR001 and so on. So the radio button filters for either burger or sandwich. I used an SQL statement with Like

If optBurger.Checked = True Then

SQLa = "SELECT FROM Menu WHERE MENU_ID Like '%Burger%'"

For example, if one radio button says Burger and the other sandwich, By choosing Burger the combo box only shows me the data related to burgers.

Burger-FR001
Burger-GR001
Burger-GR002

The checkboxes act as a filter, if I choose a checkbox for GR it should only show me the entries with FR, and multiple checkboxes can be chosen. This is done by selecting a "Type" column in the database.

if chkGR.Checked = True Then
SQLa = SQLa & "AND Type= 'GR'"

I know this can be done using if.. else if statements, but with 6 checkboxes and the possibility of adding more, this gets tedious and big! I would need to make 36 if else statement for each checkbox!

I tried looping through the controls and if any box is checked it will remove the first three letters of the name (chk) and add the name to the SQL select statement since that is how they are named in the database

But I cannot seem to make it work! I have spent on this so far. If anyone can help I would appreciate that! I think the loop is correct (or it could be a mess, I am new to this ..)

Code is below:

If optBurger.Checked = True Then
        cboSelect.Items.Clear()

SQLa = "SELECT FROM Menu WHERE MENU_ID Like '%Burger%'"

            Dim strChk As String = ""
            Dim chk As CheckBox
            ' Loop through each control on the form, we are looking for checkboxes
            For Each ctl As Control In grpSubsystem.Controls
                If TypeOf ctl Is CheckBox Then
                    chk = DirectCast(ctl, CheckBox)
                    If chk.Checked = True Then
                        SQLa = SQLa & "AND Type ='" & Microsoft.VisualBasic.Mid(chk.Text, 1) & "'"

                    End If
                End If
            CallAccess()
            RSa.MoveFirst()
            Do Until RSa.EOF()
                cboSelect.Items.Add(RSa.Fields("MENU_ID").Value)
                RSa.MoveNext()
            Loop
            CloseAccess()
end if

I am trying to do this using the checkChanged event

Thanks in advance

unknown.person · Accepted Answer · 2017-10-23 09:12:55Z

1

just insert your checkbox value to some string

        Dim MenuList As String = ""
        Dim Menu_List As List(Of [String]) = New List(Of String)

        If CheckBox1.Checked Then
            Menu_List.Add("colomn1")
        Else
            Menu_List.Remove("colomn1")
        End If
        If CheckBox2.Checked Then
            Menu_List.Add("colomn2")
        Else
            Menu_List.Remove("colomn2")
        End If
        ... 
        MenuList = [String].Join(", ", Menu_List.ToArray())

and then you can run your query with

Select " + MenuList + " from MENU

or

MenuList = [String].Join("%' AND Menu_ID like %' ", Menu_List.ToArray())

and the query is

select * from MENU where Menu_ID like '%" + MenuList +"%'

I hope this will help you, sorry if this code have minor mistake, i didn't write this code on compiler

answered Oct 23, 2017 at 9:12

unknown.person

1332 silver badges11 bronze badges

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

1 Comment

Pneuma Over a year ago

Thank you for your answer. I had to adapt this to visual basic. When selecting two checkboxes it is fine. But when the third one is checked, it just keeps repeating the second checkbox!

Collectives™ on Stack Overflow

Create an SQL SELECT statement based on checkbox entries - Visual Basic

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related