locked
How to display only the result during searching in groupbox RRS feed

  • Question

  • Good day,

    I would like to know on how to only display the result that it is search 

    Example,

    The system will check the person's Employee Card number and the Invitation Card number.

    If match, it will only show the person that is related instead of everyone.

    Wednesday, February 19, 2020 4:17 AM

Answers

  • If you want the search from the database and not the DataGridView the following shows how.

    Imports System.Data.SqlClient
    
    
    Public Class DataOperations
        Private ConnectionString As String =
                    "Data Source=.\SQLEXPRESS;" &
                    "Initial Catalog=PaginationExample;" &
                    "Integrated Security=True"
    
        Public Function BySequenceAndCountry(sequence As String, country As String) As DataTable
    
            Dim resultsDataTable As New DataTable
    
            Dim selectStatement =
                    "SELECT Id, FirstName, SequenceNumber, LastName, Street, City, State, Country, Balance, LastPaid " &
                    "FROM LotsOfData_1 WHERE (SequenceNumber = @SequenceNumber) AND (Country = @Country)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    cmd.Parameters.AddWithValue("@SequenceNumber", sequence)
                    cmd.Parameters.AddWithValue("@Country", country)
                    cn.Open()
                    resultsDataTable.Load(cmd.ExecuteReader())
                End Using
            End Using
    
            Return resultsDataTable
    
        End Function
    End Class
    

    Demo

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.DataSource = BindingSource
        End Sub
        Private BindingSource As New BindingSource
    
        Private Sub FindButton_Click(sender As Object, e As EventArgs) Handles FindButton.Click
            If Not String.IsNullOrWhiteSpace(SequenceNumberTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(CountryTextBox.Text) Then
                Dim ops As New DataOperations
                BindingSource.DataSource = ops.BySequenceAndCountry(SequenceNumberTextBox.Text, CountryTextBox.Text)
            End If
        End Sub
    End Class
    

    Comment: Whenever possible write your code in a class for data operations rather than in a form.

    Here I tweaked the above so the method call is static

    Imports System.Data.SqlClient
    
    
    Public Class DataOperations
        Private Shared ConnectionString As String =
                    "Data Source=.\SQLEXPRESS;" &
                    "Initial Catalog=PaginationExample;" &
                    "Integrated Security=True"
    
        Public Shared Function BySequenceAndCountry(sequence As String, country As String) As DataTable
    
            Dim resultsDataTable As New DataTable
    
            Dim selectStatement =
                    "SELECT Id, FirstName, SequenceNumber, LastName, Street, City, State, Country, Balance, LastPaid " &
                    "FROM LotsOfData_1 WHERE (SequenceNumber = @SequenceNumber) AND (Country = @Country)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    cmd.Parameters.AddWithValue("@SequenceNumber", sequence)
                    cmd.Parameters.AddWithValue("@Country", country)
                    cn.Open()
                    resultsDataTable.Load(cmd.ExecuteReader())
                End Using
            End Using
    
            Return resultsDataTable
    
        End Function
    End Class
    

    Demo

    Private Sub FindButton_Click(sender As Object, e As EventArgs) Handles FindButton.Click
        If Not String.IsNullOrWhiteSpace(SequenceNumberTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(CountryTextBox.Text) Then
            BindingSource.DataSource = DataOperations.BySequenceAndCountry(SequenceNumberTextBox.Text, CountryTextBox.Text)
        End If
    End Sub


    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

    • Marked as answer by Zetsubo69 Thursday, February 20, 2020 9:48 AM
    Wednesday, February 19, 2020 2:41 PM

All replies

  • Hi,
    the easiest way is to set filter on BindingSource like in follwing demo:

    Public Class Form1
      Private WithEvents tb As New TextBox With {.Dock = DockStyle.Top}
      Private dt As DataTable
      Private bs As New BindingSource
      Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {dgv, tb})
        dt = GetData()
        bs.DataSource = dt
        dgv.DataSource = bs
      End Sub
    
      Private Sub tb_KeyDown(sender As Object, e As KeyEventArgs) Handles tb.KeyDown
        If e.KeyCode = Keys.Enter Then
          bs.Filter = $"Invitation_Card_No = {tb.Text}"
        End If
      End Sub
    
      Private Function GetData() As DataTable
        Dim dt As New DataTable
        With dt
          With .Columns
            With .Add("ID", GetType(Integer))
              .AutoIncrement = True
              .AutoIncrementSeed = -1
              .AutoIncrementStep = -1
            End With
            .Add("Invitation_Card_No", GetType(String))
          End With
          For i = 1 To 100
            Dim row = dt.NewRow
            row("Invitation_Card_No") = i.ToString("000")
            dt.Rows.Add(row)
          Next
        End With
        Return dt
      End Function
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Wednesday, February 19, 2020 4:43 AM
  • the requirement has to be as above stated. 
    Wednesday, February 19, 2020 8:04 AM
  • Hi,
    if you want see only records with 2 searched values use filter like this:

          bs.Filter = $"Invitation_Card_No = {tbInvitationCardNo.Text} AND Enployee_Card_No = {tbEnployeeCardNo.Text}"


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Wednesday, February 19, 2020 8:39 AM
  • Hi Zetsub

    I think that is easy to do what you ask for by your SQL query,,,,,,,,

    When you write the number in the (Employee No TextBox) and (Invitation card TextBox) in text changed event and use in your SQL query  the "WHERE  AND" statement like the following

     Using cn As New SqlConnection(cs)
                cn.Open()
                Using cmd As New SqlCommand
                    cmd.Connection = cn
                    cmd.CommandText = "select Col1,Col2,Col3,......
                                                 from your_table                                                   
                                                 WHERE EmployeeNo=@EmployeeNo AND InvitationCard=@InvitationCard"
                    cmd.Parameters.Add("@EmployeeNo", SqlDbType.Int).Value = Val(txtEmployeeNo.Text)
                    cmd.Parameters.Add("@InvitationCard", SqlDbType.Int).Value = Val(txtInvitationCard.Text)
                    Using ds As New DataSet, da As New SqlDataAdapter(cmd)
                        da.Fill(ds, "your_table")
                        DataGridView1.DataSource = ds.Tables("your_table").DefaultView
                    End Using
                End Using
            End Using

    Will absorb a specific data from your table (you choose the employee that has this Employee No and this Invitation card only from your table ) 

    Hope it helps

      

    Regards From Egypt

    Wednesday, February 19, 2020 9:33 AM
  • If you want the search from the database and not the DataGridView the following shows how.

    Imports System.Data.SqlClient
    
    
    Public Class DataOperations
        Private ConnectionString As String =
                    "Data Source=.\SQLEXPRESS;" &
                    "Initial Catalog=PaginationExample;" &
                    "Integrated Security=True"
    
        Public Function BySequenceAndCountry(sequence As String, country As String) As DataTable
    
            Dim resultsDataTable As New DataTable
    
            Dim selectStatement =
                    "SELECT Id, FirstName, SequenceNumber, LastName, Street, City, State, Country, Balance, LastPaid " &
                    "FROM LotsOfData_1 WHERE (SequenceNumber = @SequenceNumber) AND (Country = @Country)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    cmd.Parameters.AddWithValue("@SequenceNumber", sequence)
                    cmd.Parameters.AddWithValue("@Country", country)
                    cn.Open()
                    resultsDataTable.Load(cmd.ExecuteReader())
                End Using
            End Using
    
            Return resultsDataTable
    
        End Function
    End Class
    

    Demo

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            DataGridView1.DataSource = BindingSource
        End Sub
        Private BindingSource As New BindingSource
    
        Private Sub FindButton_Click(sender As Object, e As EventArgs) Handles FindButton.Click
            If Not String.IsNullOrWhiteSpace(SequenceNumberTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(CountryTextBox.Text) Then
                Dim ops As New DataOperations
                BindingSource.DataSource = ops.BySequenceAndCountry(SequenceNumberTextBox.Text, CountryTextBox.Text)
            End If
        End Sub
    End Class
    

    Comment: Whenever possible write your code in a class for data operations rather than in a form.

    Here I tweaked the above so the method call is static

    Imports System.Data.SqlClient
    
    
    Public Class DataOperations
        Private Shared ConnectionString As String =
                    "Data Source=.\SQLEXPRESS;" &
                    "Initial Catalog=PaginationExample;" &
                    "Integrated Security=True"
    
        Public Shared Function BySequenceAndCountry(sequence As String, country As String) As DataTable
    
            Dim resultsDataTable As New DataTable
    
            Dim selectStatement =
                    "SELECT Id, FirstName, SequenceNumber, LastName, Street, City, State, Country, Balance, LastPaid " &
                    "FROM LotsOfData_1 WHERE (SequenceNumber = @SequenceNumber) AND (Country = @Country)"
    
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
                    cmd.Parameters.AddWithValue("@SequenceNumber", sequence)
                    cmd.Parameters.AddWithValue("@Country", country)
                    cn.Open()
                    resultsDataTable.Load(cmd.ExecuteReader())
                End Using
            End Using
    
            Return resultsDataTable
    
        End Function
    End Class
    

    Demo

    Private Sub FindButton_Click(sender As Object, e As EventArgs) Handles FindButton.Click
        If Not String.IsNullOrWhiteSpace(SequenceNumberTextBox.Text) AndAlso Not String.IsNullOrWhiteSpace(CountryTextBox.Text) Then
            BindingSource.DataSource = DataOperations.BySequenceAndCountry(SequenceNumberTextBox.Text, CountryTextBox.Text)
        End If
    End Sub


    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

    • Marked as answer by Zetsubo69 Thursday, February 20, 2020 9:48 AM
    Wednesday, February 19, 2020 2:41 PM
  • Good day,

    Is it possible for a message box to appear if the 2 check box is value is matched? 

    Thursday, February 20, 2020 3:44 AM