Text based search

Question

I'm trying to create a program that searches a string and then compares the word with words in a database.. but I cant get is to search a colum a the database for the words in the string... this Is my code its in vb

cmd = New SqlCeCommand("SELECT Actiewoord FROM[Actiewoorden] where Actiewoord LIKE @Actiewoord ;", con)
        If con.State = ConnectionState.Closed Then con.Open()
        myDA = New SqlCeDataAdapter(cmd)
        myDataSet = New DataSet()
        myDA.Fill(myDataSet, "0")
        Dim srdr As SqlCeDataReader = cmd.ExecuteReader()
        While srdr.Read = True
            actiewoord = srdr.Item("Actiewoord")
        End While
        srdr.Close()
        con.Close()

       

       

       

       

       

       

Answer 1

Add the line below

       If con.State = ConnectionState.Closed Then con.Open()
       'new line
       cmd.CommandText = True

       myDA = New SqlCeDataAdapter(cmd)

---

jdweng

Answer 2

Thank you bet stil i get the same error at, myDA.Fill(myDataSet,"0")

This is the error: Invalid stored procedure name. [ Stored Proc Name = True ]

Answer 3

Hi,

You cannot call stored Procedure with SqlCe. so try with the below code

cmd = New SqlCeCommand("SELECT Actiewoord FROM[Actiewoorden] where Actiewoord LIKE 'Actiewoord' ", con);

---

PS.Shakeer Hussain

Answer 4

I am trying to reads the text from a ritchtextbox and then comparing words with the database.. so that was my code but i used you`re code and i still get the same error... thanks for the help anyway!

Answer 5

Hi,

Try with the below code

cmd = New SqlCeCommand("SELECT Actiewoord FROM[Actiewoorden] where Actiewoord LIKE 'Actiewoord'", con)
        If con.State = ConnectionState.Closed Then con.Open()
        Dim srdr As SqlCeDataReader = cmd.ExecuteReader()
        While srdr.Read = True
            actiewoord = srdr.Item("Actiewoord")
        End While
        srdr.Close()
        con.Close()

---

PS.Shakeer Hussain

Answer 6

Awesome! the error is gone but it brings me back to a problem a had before with a half working version.... it selects the last word in the database and it only works if the last word of the database is in the sentence. Thanks for the help!

Answer 7

Hi ,

To search any matching word add '%' with LIKE in your SQL Query.

cmd = New SqlCeCommand("SELECT Actiewoord FROM[Actiewoorden] where Actiewoord LIKE '%Actiewoord%'", con)

PS.Shakeer Hussain

Answer 8

If you're searching a lot of text and you can use the not-CE version of SQL Server, you might find Full-Text Search useful.

--
Andrew

Answer 9

but now i get a error in the if statement.. that the value cant be null...

This is my code

If RichTextBox1.Text.Contains(actiewoord) Then            
   MsgBox(RichTextBox1.Text)
   RichTextBox1.Clear()
End If

Answer 10

Hi Try with the below code
If String.IsNullOrEmpty(actiewoord)=False Then
If RichTextBox1.Text.Contains(actiewoord) Then            
   MsgBox(RichTextBox1.Text)
   RichTextBox1.Clear()
End If
END IF

---

PS.Shakeer Hussain

Answer 11

it still doesn't word. i guest im stil to new at this... This is my last code

cmd = New SqlCeCommand("SELECT Actiewoord FROM[Actiewoorden] where Actiewoord LIKE '%Actiewoord%'", con)
      If con.State = ConnectionState.Closed Then con.Open()
        Dim srdr As SqlCeDataReader = cmd.ExecuteReader()
        While srdr.Read = True
            actiewoord = srdr.Item("Actiewoord")
        End While
        srdr.Close()
        con.Close()

        If String.IsNullOrEmpty(actiewoord) = False Then
            If RichTextBox1.Text.Contains(actiewoord) Then
                MsgBox(RichTextBox1.Text)
                RichTextBox1.Clear()
            Else
                MsgBox("Helaas... een fout!")
                RichTextBox1.Clear()
            End If
        End If

Answer 12

Have you checked that the string literal "Actiewoord" appears in the column Actiewoord?

--
Andrew

Answer 13

Ehm I don't know what you mean... sorry i'm still a newb

Answer 14

use the % character with your search keyword..like as :

SELECT Actiewoord FROM[Actiewoorden] where Actiewoord LIKE '%Actiewoord%' ;

you can use various pattern matching with LIKE..go to MSDN:

http://msdn.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx

Thanks

---

Hirendra Sisodiya from authorcode.com

Answer 15

.... I stil cant get it to work....

Answer 16

It is not clear to me what role RichTextBox1 has in your search, but this code may help you see what is happening:

' There could be more than one match, so create a List(Of String) to hold them.
Dim found As New List(Of String)

' As a test, we will search for all the rows containing the string "a"
Dim actiewoord As String = "a"

' We want to search for the string in variable actiewoord appearing anywhere in a row in the colummn [Actiewoord],
' so surround it with "%" characters
Dim search As String = "%" & actiewoord & "%"

Dim connString As String = "your connection string goes here"
Dim conn = New SqlCeConnection(connString)

' We will use a parameter for the value to allow apostrophes in the search (and prevent SQL injection attacks)
Dim cmd = New SqlCeCommand("SELECT Actiewoord FROM [Actiewoorden] where Actiewoord LIKE @P1", conn)
cmd.Parameters.Add(New SqlCeParameter("@P1", search))

conn.Open()
Dim srdr As SqlCeDataReader = cmd.ExecuteReader()

While srdr.Read
	found.Add(CStr(srdr.Item("Actiewoord")))
End While

conn.Close()

If found.Count = 0 Then	' no matches
	MsgBox("Helaas... een fout!")
Else
	MsgBox(RichTextBox1.Text)
End If

RichTextBox1.Clear()

HTH,

Andrew