none
I have a problem with the database RRS feed

  • Question

  • I'm trying to search for a word in a database of type (Access)

    Any word containing (') 

    This message appears

    I want a solution please
    • Edited by jamilaid Sunday, September 17, 2017 11:44 PM
    Sunday, September 17, 2017 11:42 PM

Answers

  • You should always post your code which is having issues. Here is what you need to search using a LIKE condition where the like condition below is a contains.

    Public Sub FindExample(ByVal pValue As String)
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
                cmd.Parameters.AddWithValue("@FirstName", pValue)
                cn.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                If reader.HasRows Then
                    While reader.Read
                        Console.WriteLine(reader.GetString(0))
                    End While
                End If
            End Using
        End Using
    End Sub

    Usage where input comes from a TextBox

    If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
        FindExample($"%{TextBox1.Text}%")
    End If

    Table and output from above code

    TextBox value, an apostrophe 


    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


    Monday, September 18, 2017 2:21 AM
    Moderator
  • Hi jamilaid,

    If you want to find a word contains('), you can use String.Contains Method (String) to find, like this:

    Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb;Persist Security Info=False;"
            Dim dt As New DataTable
            Dim con As New OleDbConnection(str)
            Dim query As String = "select * from Test2"
            Dim cmd As New OleDbCommand(query, con)
            con.Open()
            Dim reader As OleDbDataReader = cmd.ExecuteReader
            If reader.HasRows Then
                While reader.Read
                    If reader("Column1").contains("'") Then
                        Console.WriteLine(reader("Column1"))
                    End If
                End While
            End If

    Best Regards,

    Cherry



    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.


    Monday, September 18, 2017 2:54 AM
    Moderator
  • You should always post your code which is having issues. Here is what you need to search using a LIKE condition where the like condition below is a contains.

    Public Sub FindExample(ByVal pValue As String)
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
                cmd.Parameters.AddWithValue("@FirstName", pValue)
                cn.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                If reader.HasRows Then
                    While reader.Read
                        Console.WriteLine(reader.GetString(0))
                    End While
                End If
            End Using
        End Using
    End Sub

    Usage where input comes from a TextBox

    If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
        FindExample($"%{TextBox1.Text}%")
    End If

    Table and output from above code

    TextBox value, an apostrophe 


    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


    Thanks everyone

    This code is great but I want to search exactly for the word if the letters are big or small
    This code shows any word that contains part of the search text

    I want to search exactly

    • Edited by jamilaid Monday, September 18, 2017 1:08 PM
    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:40 PM
    Monday, September 18, 2017 12:36 PM
  • this should do it...

    change
    FindExample($"%{TextBox1.Text}%")
    
    to
    FindExample($"'{TextBox1.Text}'")
    
    and 
    Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
    
    to
    Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName = @FirstName;", cn)


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Monday, September 18, 2017 1:25 PM
  • I just tinkered around with a dataview, maybe this will work for you.

            With dt
                .Columns.Add("Col1", GetType(System.String))
                .Columns.Add("IsVisible", GetType(System.Boolean))
                .Rows.Add("That's")
                .Rows.Add("Thats")
            End With
            dv = dt.DefaultView
            DataGridView1.DataSource = dv
    
    
        Private Sub TextBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyUp
           
            For Each DTRow As DataRow In dt.Rows
                Dim SearchString As String = TextBox1.Text
                If DTRow("Col1").ToString.Contains(SearchString) Then
                    DTRow("IsVisible") = True
                Else
                    DTRow("IsVisible") = False
                End If
            Next
            dv.RowFilter = "IsVisible = True"
        End Sub


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Monday, September 18, 2017 4:49 PM
    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Monday, September 18, 2017 3:49 PM
  • This code does not search exactly

    Look

    and

    I want to write in the Textbox1 looking for him exactly

    Try this

                Dim SearchString As String = TextBox1.Text
                If DTRow("Col1").ToString.Contains(SearchString) And DTRow("Col1").ToString = SearchString Then
                    DTRow("IsVisible") = True
                Else
                    DTRow("IsVisible") = False
                End If
            Next
            dv.RowFilter = "IsVisible = True"


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Tuesday, September 19, 2017 12:06 PM
  • Convert all the string to upper should work?

            Dim SearchString As String = TextBox1.Text.ToUpper
            For Each dtrow As DataRow In dt.Rows
                If dtrow("Col1").ToString.ToUpper.Contains(SearchString) And dtrow("Col1").ToString.ToUpper = SearchString Then
                    dtrow("IsVisible") = True
                Else
                    dtrow("IsVisible") = False
                End If
            Next


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Wednesday, September 20, 2017 1:41 AM

All replies

  • Hi

    Guess what is missing from your post!

    Yes, the code is missing..........


    Regards Les, Livingston, Scotland

    Monday, September 18, 2017 2:14 AM
  • You should always post your code which is having issues. Here is what you need to search using a LIKE condition where the like condition below is a contains.

    Public Sub FindExample(ByVal pValue As String)
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
                cmd.Parameters.AddWithValue("@FirstName", pValue)
                cn.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                If reader.HasRows Then
                    While reader.Read
                        Console.WriteLine(reader.GetString(0))
                    End While
                End If
            End Using
        End Using
    End Sub

    Usage where input comes from a TextBox

    If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
        FindExample($"%{TextBox1.Text}%")
    End If

    Table and output from above code

    TextBox value, an apostrophe 


    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


    Monday, September 18, 2017 2:21 AM
    Moderator
  • Hi jamilaid,

    If you want to find a word contains('), you can use String.Contains Method (String) to find, like this:

    Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb;Persist Security Info=False;"
            Dim dt As New DataTable
            Dim con As New OleDbConnection(str)
            Dim query As String = "select * from Test2"
            Dim cmd As New OleDbCommand(query, con)
            con.Open()
            Dim reader As OleDbDataReader = cmd.ExecuteReader
            If reader.HasRows Then
                While reader.Read
                    If reader("Column1").contains("'") Then
                        Console.WriteLine(reader("Column1"))
                    End If
                End While
            End If

    Best Regards,

    Cherry



    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.


    Monday, September 18, 2017 2:54 AM
    Moderator
  • You should always post your code which is having issues. Here is what you need to search using a LIKE condition where the like condition below is a contains.

    Public Sub FindExample(ByVal pValue As String)
        Using cn As New OleDbConnection(Builder.ConnectionString)
            Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
                cmd.Parameters.AddWithValue("@FirstName", pValue)
                cn.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                If reader.HasRows Then
                    While reader.Read
                        Console.WriteLine(reader.GetString(0))
                    End While
                End If
            End Using
        End Using
    End Sub

    Usage where input comes from a TextBox

    If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
        FindExample($"%{TextBox1.Text}%")
    End If

    Table and output from above code

    TextBox value, an apostrophe 


    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


    Thanks everyone

    This code is great but I want to search exactly for the word if the letters are big or small
    This code shows any word that contains part of the search text

    I want to search exactly

    • Edited by jamilaid Monday, September 18, 2017 1:08 PM
    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:40 PM
    Monday, September 18, 2017 12:36 PM
  • this should do it...

    change
    FindExample($"%{TextBox1.Text}%")
    
    to
    FindExample($"'{TextBox1.Text}'")
    
    and 
    Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
    
    to
    Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName = @FirstName;", cn)


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Monday, September 18, 2017 1:25 PM
  • You never indicated this in your initial post. SQL-Server provides this functionality natively but not MS-Access. You would need to rely on looping through all rows for this or query via the LIKE operator for case insensitive results and loop through those matches. How you do this is dependent on how many rows might be a match for the search criteria e.g. several hundred thousand rows verses several hundred plus if there are indexes on the field(s) in question.


    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

    Monday, September 18, 2017 1:26 PM
    Moderator
  • this should do it...

    change
    FindExample($"%{TextBox1.Text}%")
    
    to
    FindExample($"'{TextBox1.Text}'")
    
    and 
    Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName Like @FirstName;", cn)
    
    to
    Using cmd As New OleDbCommand("SELECT LastName FROM users WHERE FirstName = @FirstName;", cn)


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    This code does not work
    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    • Unmarked as answer by jamilaid Wednesday, September 20, 2017 3:41 PM
    Monday, September 18, 2017 2:04 PM
  • youre right. i wasnt paying attention that youre looking for ' 

    I know better than to answer questions on less than 1 cup of coffee


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Monday, September 18, 2017 2:10 PM
  • You never indicated this in your initial post. SQL-Server provides this functionality natively but not MS-Access. You would need to rely on looping through all rows for this or query via the LIKE operator for case insensitive results and loop through those matches. How you do this is dependent on how many rows might be a match for the search criteria e.g. several hundred thousand rows verses several hundred plus if there are indexes on the field(s) in question.


    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

    I am sorry because I do not speak English so I have made the right mistake ....

    But now I work with Access

    Monday, September 18, 2017 2:13 PM
  • I want a code that searches for any word you type exactly if it contains (') or not

    But if the word contains (') I want to appear naturally

    Without the appearance of the error message

    Monday, September 18, 2017 2:36 PM
  • I just tinkered around with a dataview, maybe this will work for you.

            With dt
                .Columns.Add("Col1", GetType(System.String))
                .Columns.Add("IsVisible", GetType(System.Boolean))
                .Rows.Add("That's")
                .Rows.Add("Thats")
            End With
            dv = dt.DefaultView
            DataGridView1.DataSource = dv
    
    
        Private Sub TextBox1_KeyUp(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyUp
           
            For Each DTRow As DataRow In dt.Rows
                Dim SearchString As String = TextBox1.Text
                If DTRow("Col1").ToString.Contains(SearchString) Then
                    DTRow("IsVisible") = True
                Else
                    DTRow("IsVisible") = False
                End If
            Next
            dv.RowFilter = "IsVisible = True"
        End Sub


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Monday, September 18, 2017 4:49 PM
    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Monday, September 18, 2017 3:49 PM
  • This code does not search exactly

    Look

    and

    I want to write in the Textbox1 looking for him exactly
    • Edited by jamilaid Monday, September 18, 2017 9:28 PM
    Monday, September 18, 2017 9:14 PM
  • I want a code that searches for any word you type exactly if it contains (') or not

    But if the word contains (') I want to appear naturally

    Without the appearance of the error message

    Hi jamilaid,

    According to your description, you want to find out some data in access database that contains a entire word in the column, for example, one column 1 is "I am Cherry", column 2 is "Helloam", then the find string is "am", so column1 will meet your requirement, am I right?

    Best Regards,

    Cherry


    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.

    Tuesday, September 19, 2017 9:48 AM
    Moderator
  • This code does not search exactly

    Look

    and

    I want to write in the Textbox1 looking for him exactly

    Try this

                Dim SearchString As String = TextBox1.Text
                If DTRow("Col1").ToString.Contains(SearchString) And DTRow("Col1").ToString = SearchString Then
                    DTRow("IsVisible") = True
                Else
                    DTRow("IsVisible") = False
                End If
            Next
            dv.RowFilter = "IsVisible = True"


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Tuesday, September 19, 2017 12:06 PM
  • This is perfect
    Could it come to the word if it was  small or Capital

     



    • Edited by jamilaid Wednesday, September 20, 2017 12:21 AM
    Tuesday, September 19, 2017 11:40 PM
  • Convert all the string to upper should work?

            Dim SearchString As String = TextBox1.Text.ToUpper
            For Each dtrow As DataRow In dt.Rows
                If dtrow("Col1").ToString.ToUpper.Contains(SearchString) And dtrow("Col1").ToString.ToUpper = SearchString Then
                    dtrow("IsVisible") = True
                Else
                    dtrow("IsVisible") = False
                End If
            Next


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:39 PM
    Wednesday, September 20, 2017 1:41 AM
  • Thank you
    • Marked as answer by jamilaid Wednesday, September 20, 2017 3:41 PM
    • Unmarked as answer by jamilaid Wednesday, September 20, 2017 3:41 PM
    Wednesday, September 20, 2017 3:38 PM