none
vb.net help: mutiple keyword search from one textbox in database

    Question

  • how do i search the database with multiple keyword enterned in one textbox seperated by comma. please can any one assist me withe the code.
    Wednesday, May 3, 2017 11:07 AM

All replies

  • Hello,

    Are you searching against one table without joins? Will any of the values have apostrophes? These are important to know up front before I provide a solution.


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, May 3, 2017 11:46 AM
    Moderator
  • Hi,

    I'm afraid you would get lazy about your task.

    It will require some steps, such as extracting keywords from the textbox, and search the database with them. You don't provide the number of keywords, e.g. fixed to 5, varies but max is 10, etc.

    Please narrow down your question, and provide restrictions to make code.
    __________
    Ashidacchi
    Thursday, May 4, 2017 5:30 AM
  • Hi ruman_haque,

    According to your description, can you please tell me that you want to search in the specific field or fuzzy query in the all fields.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 4, 2017 8:33 AM
    Moderator
  • Try This

          Dim connetionString As String
            Dim connection As SqlConnection
            Dim myAdapter As SqlDataAdapter
            Dim ds As New DataSet
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
            connection = New SqlConnection(connetionString)
            Try
                connection.Open()
                myAdapter = New SqlDataAdapter("with tmp(Field1, Field2," & TextBox3.Text & ", Field4) as (select Field1, Field2, LEFT(" & TextBox3.Text & ", CHARINDEX(','," & TextBox3.Text & " +',')-1),STUFF(" & TextBox3.Text & ", 1, CHARINDEX(','," & TextBox3.Text & "+','), '') from Testdata union all select Field1, Field2, LEFT(" & TextBox3.Text & ", CHARINDEX(','," & TextBox3.Text & "+',')-1), STUFF(" & TextBox3.Text & ", 1, CHARINDEX(','," & TextBox3.Text & "+','), '') from tmp where " & TextBox3.Text & " > '' ) select Field1, Field2, Field4 from tmp order by Field1", connection)
                myAdapter.Fill(ds)
                ListBox1.DataSource = ds.Tables(0)
    
                connection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
    
    
    

    just make the separation of the text from the SQL statement

    Saturday, May 6, 2017 6:34 AM
  • Okay, I had time to put the following together.

    I created a custom TextBox where you can set several properties either through the property window or by code e.g.

    Custom TextBox

    Imports System.ComponentModel
    Public Class TextBoxWhere
        Inherits TextBox
    
        Private mInClause As String
        <Browsable(False)>
        Public ReadOnly Property InClause As String
            Get
                Return mInClause
            End Get
        End Property
        Private mWhere As String
        Public ReadOnly Property Where As String
            Get
                Return mWhere
            End Get
        End Property
        Private mIsValid As Boolean
        <Browsable(False)>
        Public ReadOnly Property IsValid As Boolean
            Get
                Return mIsValid
            End Get
        End Property
    
        <Category("Data"), Description("Column name in table")>
        Public Property ColumnName As String
        <Category("Data"), Description("Char separator in Text property to split on")>
        Public Property Separator As Char
        <Category("Data"), Description("Valid SQL SELECT with no WHERE clause")>
        Public Property SelectStatement As String
    
        Public Sub CreateInClause()
            If Not String.IsNullOrWhiteSpace(Text) AndAlso Not String.IsNullOrWhiteSpace(SelectStatement) Then
                If Not String.IsNullOrWhiteSpace(ColumnName) AndAlso Not Separator = vbNullChar Then
    
                    Dim splitTokensArray As String() = Text.Split(Separator)
    
                    Dim sb As New Text.StringBuilder
                    For Each item In splitTokensArray
                        sb.Append($"'{item.Replace("'", "''")}',")
                    Next
    
                    Dim joinedTokens As String = sb.ToString
                    If joinedTokens.Last = "," Then
                        joinedTokens = joinedTokens.Substring(0, joinedTokens.Length - 1)
                    End If
    
                    Dim whereTokens = "(" & joinedTokens & ")"
                    mWhere = whereTokens
                    mInClause = $"{SelectStatement} WHERE {ColumnName} IN " & whereTokens
                    mIsValid = True
                Else
                    mIsValid = False
                End If
            Else
                mIsValid = False
            End If
        End Sub
    
    End Class

    Using the following table

    Created a windows forms project

    Code for form

    Public Class Form1
        Private SelectStatement As String =
            "SELECT supplier_id,supplier_name,city,[state] FROM WhereInSimple"
    
        ''' <summary>
        ''' Simple, comma delimited, no apostrophe, column name specified
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdValid_Click(sender As Object, e As EventArgs) Handles cmdValid.Click
            TextBoxWhere1.Text = "Google,Kimberly-Clarks,Tyson Foods"
            TextBoxWhere1.SelectStatement = SelectStatement
            TextBoxWhere1.Separator = ","c
            TextBoxWhere1.ColumnName = "supplier_name"
            TextBoxWhere1.CreateInClause()
            If TextBoxWhere1.IsValid Then
                PopulateDataDataGridView(TextBoxWhere1.InClause)
            Else
                DataGridView1.DataSource = Nothing
            End If
        End Sub
        Private Sub cmdValid1_Click_1(sender As Object, e As EventArgs) Handles cmdValid1.Click
            TextBoxWhere1.Text = "Google's,Kimberly-Clarks,Tyson Foods"
            TextBoxWhere1.SelectStatement = SelectStatement
            TextBoxWhere1.Separator = ","c
            TextBoxWhere1.ColumnName = "supplier_name"
    
            TextBoxWhere1.CreateInClause()
            If TextBoxWhere1.IsValid Then
                PopulateDataDataGridView(TextBoxWhere1.InClause)
            Else
                DataGridView1.DataSource = Nothing
            End If
        End Sub
    
        Private Sub cmdNoSeparator_Click(sender As Object, e As EventArgs) Handles cmdNoSeparator.Click
            TextBoxWhere1.Text = "Google's,Kimberly-Clarks,Tyson Foods"
            TextBoxWhere1.SelectStatement = SelectStatement
            TextBoxWhere1.Separator = Nothing
            TextBoxWhere1.ColumnName = "supplier_name"
    
            TextBoxWhere1.CreateInClause()
            If TextBoxWhere1.IsValid Then
                PopulateDataDataGridView(TextBoxWhere1.InClause)
            Else
                DataGridView1.DataSource = Nothing
                MessageBox.Show("Not configured correctly")
            End If
        End Sub
    
        Private Sub cmdNoSelectStatement_Click(sender As Object, e As EventArgs) Handles cmdNoSelectStatement.Click
            TextBoxWhere1.Text = ""
            TextBoxWhere1.SelectStatement = SelectStatement
            TextBoxWhere1.Separator = Nothing
            TextBoxWhere1.ColumnName = "supplier_name"
    
            TextBoxWhere1.CreateInClause()
            If TextBoxWhere1.IsValid Then
                PopulateDataDataGridView(TextBoxWhere1.InClause)
            Else
                DataGridView1.DataSource = Nothing
                MessageBox.Show("Not configured correctly")
            End If
        End Sub
        Private Sub cmdBadSelectStatement_Click(sender As Object, e As EventArgs) Handles cmdBadSelectStatement.Click
            TextBoxWhere1.Text = "SELECT id,supplier_name,city,[state] FROM WhereInSimple"
            TextBoxWhere1.SelectStatement = TextBoxWhere1.Text
            TextBoxWhere1.Separator = ","c
            TextBoxWhere1.ColumnName = "supplier_name"
    
            TextBoxWhere1.CreateInClause()
            If TextBoxWhere1.IsValid Then
                PopulateDataDataGridView(TextBoxWhere1.InClause)
    
            Else
                DataGridView1.DataSource = Nothing
                MessageBox.Show("Not configured correctly")
            End If
        End Sub
        Private Sub cmdValidNoMatches_Click(sender As Object, e As EventArgs) Handles cmdValidNoMatches.Click
            TextBoxWhere1.Text = "Starbucks,Clarks,Bad Foods"
            TextBoxWhere1.SelectStatement = SelectStatement
            TextBoxWhere1.Separator = ","c
            TextBoxWhere1.ColumnName = "supplier_name"
            TextBoxWhere1.CreateInClause()
            If TextBoxWhere1.IsValid Then
                PopulateDataDataGridView(TextBoxWhere1.InClause)
                If DataGridView1.Rows.Count = 1 AndAlso CType(DataGridView1.DataSource, DataTable).Rows.Count = 0 Then
                    MessageBox.Show($"No matches for column {TextBoxWhere1.ColumnName} using{Environment.NewLine}{TextBoxWhere1.Where}")
                End If
            Else
                DataGridView1.DataSource = Nothing
            End If
        End Sub
        Public Sub PopulateDataDataGridView(ByVal SelectStatement As String)
            Dim dt As New DataTable
            Dim databaseServer As String = "KARENS-PC"
            Dim defaultCatalog As String = "ForumExamples"
            Using cn As New SqlClient.SqlConnection
                cn.ConnectionString = $"Data Source={databaseServer};Initial Catalog={defaultCatalog};Integrated Security=True"
                Using cmd As New SqlClient.SqlCommand With {.Connection = cn}
                    cmd.CommandText = SelectStatement
                    cn.Open()
                    Try
                        dt.Load(cmd.ExecuteReader)
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End Using
            End Using
            DataGridView1.DataSource = dt
        End Sub
    End Class

    Usage

    1. Set the SelectStatement with valid SQL e.g. SELECT * FROM SomeTable
    2. Set Separator property to a char e.g. comma, semi-colon
    3. Set ColumnName to the name for the WHERE condition
    4. Execute CreateInClause method
    5. Check for success via IsValid
    6. If valid use InClause for your SELECT statement in your command object.

    For the above I ran this against SQL-Server database, no testing for MS-Access (see docs) but should work fine. Now all the above is for fields of type string, you would need to alter the control say if you want to do dates or numbers by adding a property to the control to specify type of field then in turn act on that when creating the IN tokens.

    EDIT I decided to write a MSDN code sample, check it out

    https://code.msdn.microsoft.com/Custom-TextBox-for-1461a153


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Saturday, May 6, 2017 2:25 PM
    Moderator