none
Looping through multiple records in SQL database RRS feed

  • Question

  • Hi everyone,

    I have this code to check if one record is true or false  in SQL database ... it is working fine as expected

     Private Sub Availability()
            Using conn As New SqlConnection(cs)
                conn.Open()
                Using cmd As New SqlCommand()
                    cmd.Connection = conn
                    cmd.CommandText = "select F1
                                       from UnitA 
                                       where  F1 = 1"
                    
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                       If rdr.Read Then
                            MessageBox.Show("Checkbox1 is in use", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            CheckBox1.Enabled = False
    
                            If Not rdr Is Nothing Then
                                rdr.Close()
                            End If
    
                        Else
                            MessageBox.Show("You can use Checkbox1", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            CheckBox1.Enabled = True
                        End If
                    End Using
                End Using
            End Using
    End Sub

    But in fact i have 21 record to check them also (F1,F2,F3,.............To F21),,, the question is How can i check them by this code 

    I tried to do something like this to only 6 records and it works with me   

    cmd.CommandText = "select F1,F2,F3,F4,F5,F6
                                       from UnitA 
                                       where F1=1 or F2=1 or F3=1 or F4=1 or F5=1 or F6=1"
    But i think there is a simple way to deal with 21 (Bit records in my SQL server query) than this way... 

    I want a way to loop through them .... I tried to search but i didn't understand so i hope anyone to explain me how to do this ..

    Thanks  

     

    Regards From Egypt



    • Edited by Amr_Aly Thursday, November 28, 2019 11:13 PM
    Thursday, November 28, 2019 1:27 PM

All replies

  • Hello,

    With SQL-Server you need to use WHERE F1 = 1 OR F2 = 1 etc. If this is variable e.g. not always all columns and the user decided use a CheckedListBox.

    In this example the CheckedListBox items are hard coded but you could always populate via

    SELECT COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Your table name' AND TABLE_SCHEMA='dbo'

    To dynamically create SQL from the CheckedListBox

    Add this class to your project

    Public Class CheckedItem
        Public Property Text() As String
        Public Property Index() As Integer
    End Class

    In a button click event

    Dim checked As CheckedItem() = CheckedListBox1.Items.
            Cast(Of String).
            Select(Function(Text, Position) New CheckedItem With {.Text = Text, .Index = Position}).
            Where(Function(item) CheckedListBox1.GetItemCheckState(item.Index) = CheckState.Checked).
            ToArray()
    
    If checked.Count() = 0 Then
        Exit Sub
    End If
    
    ' value to find in one or more columns
    Dim value = 1
    Dim columns = String.Join(",", checked.Select(Function(item) item.Text).ToArray())
    Dim conditions = String.Join(" OR ", checked.Select(Function(item) $"{item.Text} = {value}").ToArray())
    Dim sql = $"SELECT {columns} FROM UnitA WHERE {conditions}"
    Console.WriteLine(sql)

    Results for above

    SELECT F1,F2,F4 FROM UnitA WHERE F1 = 1 OR F2 = 1 OR F4 = 1


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, November 28, 2019 2:22 PM
    Moderator
  • Thanks Karen for your fast reply 

    I edited my question to explain that i already use ("WHERE" and "OR") in my query ,During your reply ..

    when i began to build my project i searched for a CheckBoxList solution but i decided to use individual CHECKBOX...

    So i don't need to use CheckBoxList ... 

    I see your answer tell me that no way for looping .. either to use CheckBoxList or make what i said above and what you said in the footer of your answer ... 

    cmd.CommandText = "select F1,F2,F3,F4,F5,F6
                                       from UnitA 
                                       where F1=1 or F2=1 or F3=1 or F4=1 or F5=1 or F6=1"


    Regards From Egypt


    • Edited by Amr_Aly Thursday, November 28, 2019 2:48 PM
    Thursday, November 28, 2019 2:47 PM
  • Do you want to check if the table contains at least one row where any F is “1”, or to check if the given Fx column contains “1” in any row, and then adjust the checkboxes accordingly?

    • Edited by Viorel_MVP Thursday, November 28, 2019 6:02 PM
    Thursday, November 28, 2019 6:00 PM
  • Do you want to check if the table contains at least one row where any F is “1”, or to check if the given Fx column contains “1” in any row, and then adjust the checkboxes accordingly?

    Thanks Viorel

    This app for renting Flats for one month or one year ,, Every CheckBox represent one Flat 

    what i need to make sure that when the Flat number F1 is rented or booked we can't chosse it again to another one .. except in case of the rent date is finished (i.e a client(X) booked  Flat F1,F2 and F4) from (1/1/2020) to (1/4/2020) these flats must be disabled to any other clients in this period and enabled to any clients after this period..

    So i want to check about if it is True Or False because the data type of every (F) is bit in my SQL database  ,,, And i have two units every unit has 21 Flats so i have Unit A with 21 flats and Unit B with 21 flats


    Regards From Egypt


    • Edited by Amr_Aly Thursday, November 28, 2019 8:10 PM
    Thursday, November 28, 2019 8:07 PM
  • Okay, 

    Now i get a good scenario , but i have a lot of (Subs "Methods")  to check if the Flat is rented or not...

     Sub AvailabilityA1()
            Using conn As New SqlConnection(cs)
                conn.Open()
                Using cmd As New SqlCommand()
                    cmd.Connection = conn
                    cmd.CommandText = ("select F1
                                        from UnitA 
                                        where F1=1")
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        If rdr.Read Then
                            MessageBox.Show("Flat F1 is in use", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                            If Form6.CheckBox1.Checked = True Then
                                Form6.CheckBox1.Enabled = True
                            Else
                                Form6.CheckBox1.Enabled = False
                            End If
    
                            If Not rdr Is Nothing Then
                                rdr.Close()
                            End If
    
                        Else
                            Form6.CheckBox1.Enabled = True
                            MessageBox.Show("Flat F1 is free", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                        End If
    
                    End Using
                End Using
            End Using
    
        End Sub

    Now i have 21 Subs for UnitA (AvailabiltyA1() ... to AvailabilityA21()) and 21 Subs for UnitB(AvailabiltyB1() ... to AvailabilityB21()) it works fine till now in a Button_Click event,, My inquiry is no way for looping to decrease the efforts or this is the optimal way to do such case 

    Thanks to Karen , Viorel and anyone can help 

        

    Regards From Egypt

    Saturday, November 30, 2019 7:32 PM