[analys]

Hello,
I'm stuck with coding to filter my search data in datagrid.
I'm using datagrid to view three different table in my database.
I have three button to view the tables. When I click on one of the table, show payment (it will show payment table) and from there I will filter the data base on name or id number.
But I don't know how to proceed from show button into filter data in my database.

'Show Data in DataGrid
    Private Sub showpdbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showpdbtn.Click
        Dim schomasys As New DataTable()
        dataAdapter = New OleDbDataAdapter("SELECT * FROM PersonalDetails", conn)
        dataAdapter.Fill(schomasys)
        dgrid.DataSource = schomasys

        End If
    End Sub

    Private Sub showEdubtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showEdubtn.Click
        Dim schomasys As New DataTable()
        dataAdapter = New OleDbDataAdapter("SELECT * FROM EducationRecords", conn)
        dataAdapter.Fill(schomasys)
        dgrid.DataSource = schomasys
    End Sub


    Private Sub showpbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles showpbtn.Click
        Dim schomasys As New DataTable()
        dataAdapter = New OleDbDataAdapter("SELECT * FROM payment", conn)
        dataAdapter.Fill(schomasys)
        dgrid.DataSource = schomasys
    End Sub

    Private Sub searchpbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchbtn.Click
        Dim showpdbtn As Boolean = False
        Dim showEdubtn As Boolean = False
        Dim showpbtn As Boolean = False

        connection.Open()
        'command.Connection = connection
        'command.CommandType = CommandType.Text
        If showpdbtnClick = True Then
            ' SQL Statement to search for either Name or ID Number
            Dim sqlsearch As String
            sqlsearch = "SELECT * FROM PersonalDetails"

            Try
                If Me.combosearch.Text = "Name" Then
                    sqlsearch = sqlsearch & " where sname like '%" & Me.txtsearch.Text & "%'"
                Else
                    sqlsearch = sqlsearch & " where idnumber =" & Me.txtsearch.Text
                End If
                ' Once again we execute the SQL statements against our DataBase
                Dim adapter As New OleDbDataAdapter(sqlsearch, connection)

                ' Shows the records and updates the DataGridView
                Dim schomasys As New DataTable("PersonalDetails")
                dataAdapter.Fill(schomasys)
                dgrid.DataSource = schomasys

            Catch ex As Exception
                MsgBox(ErrorToString)
            Finally
                connection.Close()
            End Try

            If showEdubtn_Click() = True Then
                ' SQL Statement to search for either Name or ID Number
                Dim sqlsearch As String
                sqlsearch = "SELECT *FROM EducationRecords"

                Try
                    If Me.combosearch.Text = "Name" Then
                        sqlsearch = sqlsearch & " where sname like '%" & Me.txtsearch.Text & "%'"
                    Else
                        sqlsearch = sqlsearch & " where idnumber =" & Me.txtsearch.Text
                    End If
                    ' Once again we execute the SQL statements against our DataBase
                    Dim adapter As New OleDbDataAdapter(sqlsearch, connection)

                    ' Shows the records and updates the DataGridView
                    Dim schomasys As New DataTable("PersonalDetails")
                    dataAdapter.Fill(schomasys)
                    dgrid.DataSource = schomasys

                Catch ex As Exception
                    MsgBox(ErrorToString)
                Finally
                    connection.Close()
                End Try

                If showpbtn_Click() = True Then
                    ' SQL Statement to search for either Name or ID Number
                    Dim sqlsearch As String
                    sqlsearch = "SELECT * FROM payment"

                    Try
                        If Me.combosearch.Text = "Name" Then
                            sqlsearch = sqlsearch & " where sname like '%" & Me.txtsearch.Text & "%'"
                        Else
                            sqlsearch = sqlsearch & " where idnumber =" & Me.txtsearch.Text
                        End If
                        ' Once again we execute the SQL statements against our DataBase
                        Dim adapter As New OleDbDataAdapter(sqlsearch, connection)

                        ' Shows the records and updates the DataGridView
                        Dim schomasys As New DataTable("PersonalDetails")
                        dataAdapter.Fill(schomasys)
                        dgrid.DataSource = schomasys

                    Catch ex As Exception
                        MsgBox(ErrorToString)
                    Finally
                        connection.Close()
                    End Try

                Else
                    MsgBox("Record not found!")
                End If
            Else
                MsgBox("Record not found!")
            End If

        Else
            MsgBox("Record not found!")
        End If

    End Sub

[modi123_1]

Are you going to reload all the data, or just play around with what you have already loaded? If it's the latter then you should have the dataset to to a datagrid view then the view to data grid.. you can then set filters (think of them as 'where clauses') on the dataset's data..

If it's the former then you need to be prepared to clear the dataset (and any grid formatting) for the new columns/data.

[_HAWK_]

Quote

If showEdubtn_Click() = True Then

You can't use a button click event like a function - they don't return a boolean. It sounds like you need some radio buttons in the for options as to which table they want to view. 1 button for updating the DGV on the results. Rethink the logic and make the controls follow it.

[analys]

Hi modi,
I have three tabs for inserting data into three different tables.
Another tab for searching whereby I can view all of the tables (that's why I use datagrid) and also filter some data by name and id number

_HAWK_, on 15 March 2013 - 07:32 AM, said:

Quote

If showEdubtn_Click() = True Then

You can't use a button click event like a function - they don't return a boolean. It sounds like you need some radio buttons in the for options as to which table they want to view. 1 button for updating the DGV on the results. Rethink the logic and make the controls follow it.

Yeah, couldn't think of the way to connect it. should I make a function?

[_HAWK_]

You don't need a function. I suggested the radio buttons since only one can be selected they operate as choices. When the user changes which radio button is selected then re-query your data. If you load all the data into the dataset once you can make a LINQ query by a filter - pretty easy since the DGV's datasource can accept an array or datarows that the query would return. If I remember correctly you need to set the DGV's datasource to Nothing before setting it's new data.

Dim query = myDatatable.Rows.Where(Function(dr) dr("column name").ToString = {some filter}).ToArray
dgv.DataSource = query

[analys]

I have try using radio button but I couldn't get the data to be filter. when i try to search with name, it will just close the program. One more thing, in my name data, i have insert three string of names : John Lee Mayer. Is that effect on the search?

Private Sub searchpbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchbtn.Click

        connection.Open()

        If radpdbtn.Checked = True Then
            ' SQL Statement to search for either Name or ID Number
            Dim sqlsearch As String
            sqlsearch = "SELECT * FROM PersonalDetails"

            Try
                If Me.combosearch.Text = "Name" Then
                    sqlsearch = sqlsearch & " where sname like '%" & Me.txtsearch.Text
                Else
                    sqlsearch = sqlsearch & " where idnumber =" & Me.txtsearch.Text
                End If
                ' Once again we execute the SQL statements against our DataBase
                Dim adapter As New OleDbDataAdapter(sqlsearch, connection)

                ' Shows the records and updates the DataGridView
                Dim schomasys As New DataTable("PersonalDetails")
                dataAdapter.Fill(schomasys)
                dgrid.DataSource = schomasys

            Catch ex As Exception
                MsgBox(ErrorToString)
            Finally
                connection.Close()
            End Try

        ElseIf radedubtn.Checked = True Then
            ' SQL Statement to search for either Name or ID Number
            Dim sqlsearch As String
            sqlsearch = "SELECT * FROM EducationRecords"

            Try
                If Me.combosearch.Text = "Name" Then
                    sqlsearch = sqlsearch & " where sname like '%" & Me.txtsearch.Text & "%'"
                Else
                    sqlsearch = sqlsearch & " where idnumber =" & Me.txtsearch.Text
                End If
                ' Once again we execute the SQL statements against our DataBase
                Dim adapter As New OleDbDataAdapter(sqlsearch, connection)

                ' Shows the records and updates the DataGridView
                Dim schomasys As New DataTable("EducationRecords")
                dataAdapter.Fill(schomasys)
                dgrid.DataSource = schomasys

            Catch ex As Exception
                MsgBox(ErrorToString)
            Finally
                connection.Close()
            End Try

        ElseIf radpbtn.Checked = True Then
            ' SQL Statement to search for either Name or ID Number
            Dim sqlsearch As String
            sqlsearch = "SELECT * FROM payment"

            Try
                If Me.combosearch.Text = "Name" Then
                    sqlsearch = sqlsearch & " where sname like '%" & Me.txtsearch.Text & "%'"
                Else
                    sqlsearch = sqlsearch & " where idnumber =" & Me.txtsearch.Text
                End If
                ' Once again we execute the SQL statements against our DataBase
                Dim adapter As New OleDbDataAdapter(sqlsearch, connection)

                ' Shows the records and updates the DataGridView
                Dim schomasys As New DataTable("Payment")
                dataAdapter.Fill(schomasys)
                dgrid.DataSource = schomasys

            Catch ex As Exception
                MsgBox(ErrorToString)
            Finally
                connection.Close()
            End Try

        Else
            MsgBox("Record not found!")
        End If
    End Sub

[_HAWK_]

Could you show the table(s) structure? Your saying John Lee Mayer has many records and you want to search for all records with that name or id, are you just typing in like John and hoping to get all records for John Lee Mayer? Are there any related tables in your design?

[analys]

I put the table as one to one relation (personal details with education records and education records with payment)
If let say I type john, then I want to get all the records for john lee mayer

[_HAWK_]

PersonnelDetails table should be the only table with the name (one time), the other related tables should have the PersonnelDetailsId that relates back to this table. Each time this person adds an education or payment those tables add the id and any other info specific to them. So your sql query will require a Join statement to get all records that belong to that id or name.