Help with partial string search in Grid's columns

Help with partial string search in Grid's columns

Posted by: Victor.m.charles.civ on 26 April 2020, 7:40 am EST

• 

  Victor.m.charles.civ

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 26 April 2020, 7:40 am EST

  Hello,

  I’m using the code below to search all columns for text entered. How do I modify it for a partial string search?

  ‘Search all field by TextEntry - Full String search
  
  fsLinkTest = New System.IO.FileStream(Application.StartupPath + “\AOP40.xml”, IO.FileMode.Open)
  
  dtsetLinkTest.Clear()
  
  dtsetLinkTest.ReadXml(fsLinkTest)
  
  fsLinkTest.Close()
  
  Dim FilteredDTB As DataTable
  
  FilteredDTB = dtsetLinkTest.Tables(0)
  
  Dim view5 As DataView
  
  Dim query5 As String = “”
  
  query5 = FilteredDTB.Columns(0).ColumnName & “= '” & TextBox2.Text & "’"
  
  For x = 1 To FilteredDTB.Columns.Count - 1
  
  query5 &= " OR " & FilteredDTB.Columns(x).ColumnName & “= '” & TextBox2.Text & “'”
  
  Next
  
  view5 = New DataView(FilteredDTB)
  
  view5.RowFilter = query5
  
  If view5.Count = 0 Then
  
  MsgBox(“No Records Found”)
  
  Label2.Visible = False
  
  Exit Sub
  
  End If
  
  If view5.Count <> 0 Then
  
  TextBox1.Text = view5.Count
  
  C1Screen1.DataSource = view5
  
  End If

  Thanks,

  Victor

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 26 April 2020, 6:50 pm EST

  Hi Victor,

  To implement the partial string search in Grid’s columns use the code snippet given below:

  Dim query5 As String = ""
  query5 = "'["FilteredDTB.Columns(0).ColumnName & "] LIKE '%" & TextBox2.Text & "%'"
  For x = 1 To FilteredDTB.Columns.Count - 1
  query5 &= " OR " & "'["FilteredDTB.Columns(x).ColumnName & "] LIKE '%" & TextBox2.Text & "%'"
  Next
  

  Regards,
  
  Prabhat Sharma.

• 

  Victor.m.charles.civ

  • Post Options:
  • Link
    Copy

  Posted 28 April 2020, 2:12 am EST

  Hello Prabhat,

  I getting error " End of statement Expected" for FilteredDTB on lines:

  query5 = “‘["FilteredDTB.Columns(0).ColumnName & “] LIKE '%” & TextBox2.Text & "%’”
  
  query5 &= " OR " & “‘["FilteredDTB.Columns(x).ColumnName & “] LIKE '%” & TextBox2.Text & "%’”

  Thanks,

  Victor

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 28 April 2020, 10:55 pm EST

  Hi Victor,

  Sorry but we did not face the issue at our end and partial filtering is working fine.
  
  I have attached a sample project which is working fine at our end, please have a look.

  Regards,
  
  Prabhat Sharma.
  
  PartialSearchingTDBDemo.zip

• 

  Victor.m.charles.civ

  • Post Options:
  • Link
    Copy

  Posted 29 April 2020, 3:47 am EST

  Hello Prabhat,

  I think you sent me the wrong project. It includes a Form with a Form Load event loading a table an search for a particular column, which in not similar to my case.

  Thanks,

  Victor

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 29 April 2020, 3:53 pm EST

  Hi Victor,

  The code snippet that you used:

  Dim FilteredDTB As DataTable
  FilteredDTB = dtsetLinkTest.Tables(0)
  Dim view5 As DataView
  Dim query5 As String = ""
  query5 = FilteredDTB.Columns(0).ColumnName & "= '" & TextBox2.Text & "'"
  For x = 1 To FilteredDTB.Columns.Count - 1
  query5 &= " OR " & FilteredDTB.Columns(x).ColumnName & "= '" & TextBox2.Text & "'"
  Next
  view5 = New DataView(FilteredDTB)
  view5.RowFilter = query5
  C1Screen1.DataSource = view5
  
  

  is also filtering the DataSource using the DataView’s RowFilter property and assigning the DataView object to the DataSource property of the C1TrueDBGrid and we are doing similar in our attached sample, it is just that we are doing it in the Form load event on the basis of some static value and on a single column.

  If you are facing issues in using this approach on your sample then please provide your stripped-down sample so that we can help you accordingly.

  Regards,
  
  Prabhat Sharma.

• 

  Victor.m.charles.civ

  • Post Options:
  • Link
    Copy

  Posted 4 May 2020, 8:59 am EST

  Hello Prabhat,

  Can you please provide me a sample without hard coding the field? (i.e. query = “Company LIKE ‘%H%’”). In the actual project the code should search the value from the textbox in all fields from a table.

  Below is the sample you sent me, that searches record in the company field.:

  Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

      Dim dt As DataTable = New DataTable()
      Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "ComponentOne Samples\Common\C1NWind.mdb") & ";"
      Dim conn As OleDbConnection = New OleDbConnection(connectionString)
      Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("Select * from Data", conn)
      adapter.Fill(dt)
      C1TrueDBGrid1.DataSource = dt
      Dim query As String = ""
      query = "Company LIKE '%H%'"
      Dim view As New DataView(dt)
      view.RowFilter = query
      C1TrueDBGrid1.DataSource = view
  End Sub
  

  Thanks,

  Victor

• 

  Victor.m.charles.civ

  • Post Options:
  • Link
    Copy

  Posted 4 May 2020, 9:56 am EST

  Hello Prabhat,

  The partial string search works with the following code:

  Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

      Label2.Visible = True
      Application.DoEvents()
      'Search all field by TextEntry
      fsLinkTest = New System.IO.FileStream(Application.StartupPath + "\AOP40.xml", IO.FileMode.Open)
      dtsetLinkTest.Clear()
      dtsetLinkTest.ReadXml(fsLinkTest)
      fsLinkTest.Close()
      Dim FilteredDTB As DataTable
      FilteredDTB = dtsetLinkTest.Tables(0)
      Dim view5 As DataView
      Dim Y As String
      Y = TextBox2.Text
      Dim query5 As String = ""
      query5 = FilteredDTB.Columns(0).ColumnName & " Like '%" & Y & "%'"
      For X = 1 To FilteredDTB.Columns.Count - 1
          query5 &= " OR " & FilteredDTB.Columns(X).ColumnName & " Like '%" & Y & "%'"
  
      Next
      view5 = New DataView(FilteredDTB)
      view5.RowFilter = query5
      C1Screen1.DataSource = view5
      C1Screen1.Visible = True
  End Sub
  

  Thanks,

  Victor

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 4 May 2020, 3:35 pm EST

  Hi Victor,

  This partial string search will work on the string type columns only and I have modified the last attached project as per your comments to use a TextBox and a button to search value from all the columns.
  
  Please have a look and let us know if this helps or not.

  Regards,
  
  Prabhat Sharma.
  
  PartialSearchingTDBDemo_modified.zip

• 

  Victor.m.charles.civ

  • Post Options:
  • Link
    Copy

  Posted 16 May 2020, 2:51 pm EST

  Hello Prabhat,

  Thank you for the solution but unfortunately it’s not working when I enter a decimal point. For example a column with 1.4S, is found when I search for 4S but returns no records found when I search for 1.4S. How do I fix this issue?

  Thanks,

  Victor

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 17 May 2020, 11:25 pm EST - Updated 3 October 2022, 2:55 pm EST

  Hi Victor,

  Sorry, but we can not replicate the issue at our end as shown in the attached GIF.
  
  If you are doing something else as shown in the GIF then please let us know and modify the last attached sample accordingly,

  Regards,
  
  Prabhat Sharma.