CODEWITHSUNDEEP
sqlvb.netstringif-statement
Cheddar
Cheddar

Reputation: 530

Build string for SQL statement with multiple checkboxes in vb.net

I am currently working with a SQL back end and vb.net Windows Forms front end. I am trying to pull a report from SQL based on a list of checkboxes the user will select.

To do this I am going to use an IN clause in SQL. The only problem is if I use an if statement in vb.net to build the string its going to be a HUGE amount of code to set up the string.

I was hoping someone knew a better way to do this. The code example below shows only selecting line 1 and selecting both line 1 and 2. I will need for the code to be able to select any assortment of the lines. The string will have to be the line number with a comma following the number. This way when I include the code in my SQL query it will not bug.

Here is the code:

Dim LineString As String
    'String set up for line pull
    If CBLine1.Checked = False And CBLine2.Checked = False And CBLine3.Checked = False And CBLine4.Checked = False And _
         CBLine7.Checked = False And CBLine8.Checked = False And CBLine10.Checked = False And CBLine11.Checked = False And CBLine12.Checked = False Then
        MsgBox("No lines selected for download, please select lines for report.")
        Exit Sub
    End If

    If CBLine1.Checked = True And CBLine2.Checked = False And CBLine3.Checked = False And CBLine4.Checked = False And _
        CBLine7.Checked = False And CBLine8.Checked = False And CBLine10.Checked = False And CBLine11.Checked = False And CBLine12.Checked = False Then
        MsgBox("This will save the string as only line 1")

    ElseIf CBLine1.Checked = True And CBLine2.Checked = True And CBLine3.Checked = False And CBLine4.Checked = False And _
    CBLine7.Checked = False And CBLine8.Checked = False And CBLine10.Checked = False And CBLine11.Checked = False And CBLine12.Checked = False Then
        MsgBox("This will save the string as only line 1 and 2")
    End If

The final string will have to be inserted into a SQL statement that looks like this:

SELECT * 
FROM tabl1 
WHERE LineNumber IN (-vb.netString-)

The above code will need commas added in for the string.

Upvotes: 0

Views: 977

Answers (3)

Steve
Steve

Reputation: 216273

First you need to set up all your checkboxes with the Tag property set to the line number to which they refers. So, for example, the CBLine1 checkbox will have its property Tag set to the value 1 (and so on for all other checkboxes).

This could be done easily using the WinForm designer or, if you prefer, at runtime in the Form load event.

Next step is to retrieve all the checked checkboxes and extract the Tag property to build a list of lines required. This could be done using some Linq

Dim linesSelected = new List(Of String)()
For Each chk in Me.Controls.OfType(Of CheckBox)().
                   Where(Function(c) c.Checked)
   linesSelected.Add(chk.Tag.ToString())
Next

Now you could start your verification of the input

if linesSelected.Count = 0 Then
   MessageBox.Show("No lines selected for download, please select lines for report.")
Else If linesSelected.Count = 1 Then
   MessageBox.Show("This will save the string only for line " & linesSelected(0))
Else
   Dim allLines = string.Join(",", linesSelected)
   MessageBox.Show("This will save the string for lins " & allLines)
End If

Of course, the List(Of String) and the string.Join method are very good to build also your IN clause for your query

Upvotes: 2

BlueDan
BlueDan

Reputation: 54

If checkboxes 3...10 = false then
    If checkbox 1 = true then
        If checkbox 2 is true then
            A
        Else if checkbox 2 = false then
            B
        End if
Else
    C
End if

Upvotes: -1

BlueDan
BlueDan

Reputation: 54

I feel that I'm only half getting your question but I hope this helps. In VB.net we display a list of forms that have a status of enabled or disabled.

There is a checkbox that when checked displays only the enabled forms.

SELECT * FROM forms WHERE (Status = 'Enabled' or @checkbox = 0)

The query actually has a longer where clause that handles drop down options in the same way. This should help you pass the backend VB to a simple SQL statement.

Upvotes: 0

Related Questions