Question SQL command

[tresehulyo]

How to Put space before and after Search_Textbox.Text

for example the Search_Textbox.Text is "Inside"

i want my sql command to WHERE Title Like " Inside" OR Title Like "Inside "

with space

Dim cmd As New OleDb.OleDbCommand("select * from books where Title LIKE ' " & Search_Textbox.Text & "' or Title LIKE '" & Search_Textbox.Text & " & ' ", con)

for example the title of book is "The Brown Coward"

if i search "Cow" it will not return that because it's only a part of word
am i right if search in Cow with space before after the word " Cow" "Cow "

 

[Tom]

Look in the help file(s) on how to use wild cards. If you still need further help let me know. Also as a suggestion, try using parameters instead of a concatenated statement such as the above.

 

[tresehulyo]

ok thanks i firgure out now

 

[Tom]

Where Field Like 'P%' should only return fields that start with P, not second words starting with P. Show an example of your coding, I want to see if you may possibly not clearing your results out from previous queries

 

[tresehulyo]

this is the code
as of now i can search for ISBN and Title

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search_Button.Click
        'ISBN Radio Button
        If ISBN_RadioButton.Checked = True Then
            'Search Text Length is 10
            If Search_Textbox.Text.Length = 10 Then
                Panel.Show()
                Call ISBN1()
            Else
                Panel.Show()
                Call ISBN2()
            End If
        End If
        'Title Radio Button
        If Title_RadioButton.Checked = True Then
            'Search Text Length is 1
            If Search_Textbox.Text.Length = 1 Then
                Panel.Show()
                Call Title1()
            End If
            If Search_Textbox.Text.Contains(" ") Then
                Panel.Show()
                Call Title2()
            Else
                Panel.Show()
                Call Title3()
            End If
        End If
    End Sub
    'Exact if string length 10
    Sub ISBN1()
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("select * from books where ISBN='" & Search_Textbox.Text & "' ", con)
        dr = cmd.ExecuteReader
        While dr.Read()
            Call adddatatolistview(ListView, CStr(dr(0)), CStr(dr(1)), CStr(dr(2)), CStr(dr(3)), CStr(dr(4)), CStr(dr(5)))
        End While
        con.Close()
    End Sub
    'search in the beginning only
    Sub ISBN2()
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("select * from books where ISBN LIKE '" & Search_Textbox.Text & "%'", con)
        dr = cmd.ExecuteReader
        While dr.Read()
            Call adddatatolistview(ListView, CStr(dr(0)), CStr(dr(1)), CStr(dr(2)), CStr(dr(3)), CStr(dr(4)), CStr(dr(5)))
        End While
        con.Close()
    End Sub
    'single letter search
    Sub Title1()
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("select * from books where Title LIKE '" & Search_Textbox.Text & "%' ", con)
        dr = cmd.ExecuteReader
        While dr.Read()
            Call adddatatolistview(ListView, CStr(dr(0)), CStr(dr(1)), CStr(dr(2)), CStr(dr(3)), CStr(dr(4)), CStr(dr(5)))
        End While
        con.Close()
    End Sub
    'phrase search ex. "inside c#"
    Sub Title2()
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("select * from books where Title LIKE '%" & Search_Textbox.Text & "%' ", con)
        dr = cmd.ExecuteReader
        While dr.Read()
            Call adddatatolistview(ListView, CStr(dr(0)), CStr(dr(1)), CStr(dr(2)), CStr(dr(3)), CStr(dr(4)), CStr(dr(5)))
        End While
        con.Close()
    End Sub

    Sub Title3()
        con.Open()
        Dim cmd As New OleDb.OleDbCommand("select * from books where Title LIKE '%" & Search_Textbox.Text & "' OR Title LIKE '" & Search_Textbox.Text & "%'", con)
        dr = cmd.ExecuteReader
        While dr.Read()
            Call adddatatolistview(ListView, CStr(dr(0)), CStr(dr(1)), CStr(dr(2)), CStr(dr(3)), CStr(dr(4)), CStr(dr(5)))
        End While
        con.Close()
    End Sub