Results 1 to 7 of 7

Thread: Checkbox as Columns pick from database

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    4

    Question Checkbox as Columns pick from database

    Hey guys, i want make some datagridview where the columns inside on that data grid view is picked from some checkbox .
    ex : i have 5 check box age,name,gender,address,status then i have datagridview, if i checked age and name checkbox so in datagridview will request data from database with columns name same with checkbox that i checked
    Thanks

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Checkbox as Columns pick from database

    A DataGridView doesn't request data from a database. It displays data that you already have. If you want to change your database query based on these CheckBoxes then that is one thing. If you want to get all the data and then change what you display in the grid based on those columns then that is something else and the database is irrelevant. Please work out what it is that you actually want and then ask that question with all the information relevant to that question. One point to consider is whether you want to be able to hide and show columns after you've already retrieved the data without querying the database again, or whether the choice of columns will be made only once, before the query is executed.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    4

    Re: Checkbox as Columns pick from database

    Quote Originally Posted by jmcilhinney View Post
    A DataGridView doesn't request data from a database. It displays data that you already have. If you want to change your database query based on these CheckBoxes then that is one thing. If you want to get all the data and then change what you display in the grid based on those columns then that is something else and the database is irrelevant. Please work out what it is that you actually want and then ask that question with all the information relevant to that question. One point to consider is whether you want to be able to hide and show columns after you've already retrieved the data without querying the database again, or whether the choice of columns will be made only once, before the query is executed.
    yeah i mean , i have some data in my database then i want to display it in datagridview but not all columns, i just want to display columns that same with checkbox that i checked
    btw sorry for my bad english

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    4

    Re: Checkbox as Columns pick from database

    Quote Originally Posted by jmcilhinney View Post
    A DataGridView doesn't request data from a database. It displays data that you already have. If you want to change your database query based on these CheckBoxes then that is one thing. If you want to get all the data and then change what you display in the grid based on those columns then that is something else and the database is irrelevant. Please work out what it is that you actually want and then ask that question with all the information relevant to that question. One point to consider is whether you want to be able to hide and show columns after you've already retrieved the data without querying the database again, or whether the choice of columns will be made only once, before the query is executed.
    my default program is like this, its so basic i think

    SQL = "Select customer, id_box, id_product, type_product, status_wash, status_service_1, input_damage_1, status_paint, status_service_2, input_damage_2, status_finish from product_status"

    that is program to pick data on columns that available on daatabase, in this case, i want change value the columns like "id_box", "id_product", etc with checked or not checked checkbox

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Checkbox as Columns pick from database

    I would suggest that you set the Text property of each CheckBox to the name of the column it corresponds to or, if you want something else in the Text, set the Tag. Make sure that these CheckBoxes are the only ones in their direct container. They can be be directly on the form or, if there are other CheckBoxes on the form, put these ones in a Panel. You can then build your SQL very easily like this:
    vb.net Code:
    1. Dim checkedColumnNames = From chkbx In Controls.OfType(Of CheckBox)()
    2.                          Where chkbx.Checked
    3.                          Select chkbx.Text
    4. Dim query = $"SELECT {String.Join(", ", checkedColumnNames)} FROM MyTable"
    The first line is a LINQ query. It gets all the controls of type CheckBox where the Checked property is True and selects their Text property, producing an IEnumerable(Of String). The second line joins those Strings using a comma as the delimiter and puts that list into the text for a SQL SELECT statement.

    Note that the column names will follow the z-order of the CheckBoxes, so make sure that they are in the order you want. You can change that using the Document Outline window if required.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2021
    Posts
    4

    Re: Checkbox as Columns pick from database

    Quote Originally Posted by jmcilhinney View Post
    I would suggest that you set the Text property of each CheckBox to the name of the column it corresponds to or, if you want something else in the Text, set the Tag. Make sure that these CheckBoxes are the only ones in their direct container. They can be be directly on the form or, if there are other CheckBoxes on the form, put these ones in a Panel. You can then build your SQL very easily like this:
    vb.net Code:
    1. Dim checkedColumnNames = From chkbx In Controls.OfType(Of CheckBox)()
    2.                          Where chkbx.Checked
    3.                          Select chkbx.Text
    4. Dim query = $"SELECT {String.Join(", ", checkedColumnNames)} FROM MyTable"
    The first line is a LINQ query. It gets all the controls of type CheckBox where the Checked property is True and selects their Text property, producing an IEnumerable(Of String). The second line joins those Strings using a comma as the delimiter and puts that list into the text for a SQL SELECT statement.

    Note that the column names will follow the z-order of the CheckBoxes, so make sure that they are in the order you want. You can change that using the Document Outline window if required.
    Thanks, i got what you mean. but i have trouble about the syntax in
    Code:
    Dim query = $"SELECT {String.Join(", ", checkedColumnNames)} FROM MyTable"
    Here is my code

    Code:
    Private Sub ButtonSave_Click(sender As Object, e As EventArgs) Handles ButtonSave.Click
            Dim checkedColumnNames = From chkbx In Controls.OfType(Of CheckBox)()
                                     Where chkbx.Checked
                                     Select chkbx.Text
            Try
                myconnection.open()
    
                SQL = $"SELECT {String.Join(", ", checkedColumnNames)} FROM process"
    
                COMMAND.Connection = Connection
                COMMAND.CommandText = SQL
    
                SDA.SelectCommand = COMMAND
                SDA.Fill(DT)
    
                Guna2DataGridView1.DataSource = DT
    
                myconnection.close()
            Catch myerror As MySqlException
                MessageBox.Show("Error: " & myerror.Message)
            Finally
                myconnection.close()
            End Try
    
        End Sub

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,296

    Re: Checkbox as Columns pick from database

    Perhaps you could narrow down exactly what "i have trouble" that could mean that you don't understand it or that the compiler doesn't like it or various other things, and they are all very different problems. Be specific and provide ALL relevant information.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width