0

I am looking to get the text value of the ticked checkboxes and use that text value as a parameter for my SQL statement. so far the code I wrote gets the first checked value but not the rest after that. does anyone have any ideas the best way to do this ? also note I have an indefinite amount of checkboxes that will constantly change so looking for something that doesn't require me to write code for each option.

Below is a sample of the code I have so far which as said above only gets the first checked box.

For Each item As ListItem In chkBox.Items
If item.Selected = True Then
Using con As New SqlConnection(GblSqlCon)
Using cmd As New SqlCommand("SELECT DISTINCT ID, NAME, ADDRESS, COUNTRY, DOB FROM [db].[dbo].[tbl_TEST] WHERE COUNTRY = @COUNTRY AND DOB = @DOB")

                    cmd.Parameters.AddWithValue("@DOB", txtDOB.Text)
                    cmd.Parameters.AddWithValue("@COUNTRY", chkBox.Text)

                    MsgBox(chkBox.Text)


                    Using sda As New SqlDataAdapter()
                        cmd.Connection = con
                        sda.SelectCommand = cmd
                        Using dt As New DataTable()
                            sda.Fill(dt)
                            
                     End Using
                  End Using
              End Using
End If
Next
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user11019667
  • 5
  • 4
  • Hello, what type of project is this and what type of control is chkBox ? – Karen Payne Aug 17 '20 at 12:37
  • Hi Karen, chkBox is a checkbox list. I am looking to essentially extract and display data from SQL database and i want to use a checkbox to allow the user to filter what information is being pulled from the database – user11019667 Aug 17 '20 at 13:19
  • [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – Thom A Aug 17 '20 at 13:24
  • If this is ASP.NET see [the following](https://stackoverflow.com/questions/18924147/how-to-get-values-of-selected-items-in-checkboxlist-with-foreach-in-asp-net-c) – Karen Payne Aug 17 '20 at 14:08

1 Answers1

0

if you are using checkedBoxList and multiple countries are ticked at a time, then change your query as below: -

Note; make the parameter @COUNTRY as csv field and use IN operator.

SELECT DISTINCT ID, NAME, ADDRESS, COUNTRY, DOB FROM [db].[dbo].[tbl_TEST] WHERE COUNTRY IN ( @COUNTRY_CSV_PARAM ) AND DOB = @DOB

GL SOFT INDIA
  • 64
  • 1
  • 10