I have a problem with the database

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

Answer 1

Hi

Guess what is missing from your post!

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

---

Regards Les, Livingston, Scotland

Answer 2

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

Answer 3

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 [e-mail].

Answer 4

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

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

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

But now I work with Access

Answer 10

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

Answer 11

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

Answer 12

This code does not search exactly

Look

and

I want to write in the Textbox1 looking for him exactly

Answer 13

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 [e-mail].

Answer 14

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

Answer 15

This is perfect
Could it come to the word if it was  small or Capital

 

Answer 16

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

Answer 17

Thank you