Like statement not working [resolved]

[NeonBurner]

i've done some searching and i know my SQL is right, but i'm getting strange results, has something changed with the LIKE keyword when using OLE? heres what i got

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        LB.Items.Clear()
        Dim strConn As String = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source ="
        strConn += Request.PhysicalApplicationPath & "CalTime.mdb"
        Dim strSQL As String = "Select * from CalTime where( '7am' Like " & "'" & "%" & txtCriteria.Text & "%" & "'" & ");"
        Dim conn As New OleDbConnection(strConn)
        Dim Cmd As New OleDbCommand(strSQL, conn)
        Dim objDR As OleDbDataReader
 
        If DDEmp.SelectedItem.Text <> " - All - " Then
            strSQL += " and empName = '" & DDEmp.SelectedItem.Text & "'"
        End If
       ' msgErr.Text = strSQL
 
        Dim sName As String
        Try
            conn.Open()
            objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
            Do While objDR.Read()
                sName = objDR("empName") & " ,  " & objDR("calDate") & ""
                LB.Items.Add(New ListItem(sName))
            Loop
            conn.Close()
        Catch ex As OleDb.OleDbException
            msgErr.Text += (ex.Message)
        End Try
 
    End Sub

i dont catch any exceptions, if i have txtCriteria blank then i get the right results, when i add an A i should get 3 results but i get none, i added a intCounter in there to see if i was getting the results but not loading them right into ListBox, but its just not grabbing anything, any ideas?

[jmeckley]

I thinks its the () and ; in the string when you first declare the variable. if you were to select an option from the list box, you would get an error.

Try this instead

Dim strSQL As String = "Select * from CalTime where [7am] Like '%" & txtCriteria.Text & "%'"

[NeonBurner]

that worked, i had took out the () so it had to be the brackets, is it because my field started with a number? because without the ticks around 7am it would give me syntax error, anyways thanks

[jmeckley]

by putting single quotes around text the query interprets that as text, not a column name so '7am' would literally be 7am, not the column 7am.

It's also good practice to refrain from using numbers, spaces and special charaters in field names. If you do you will probally need brackets

[NeonBurner]

while i got ya >=), now that is working but when i change by dropdown box to list a certain employee, its not filtering out, i tried the [empName] and still not working its grabbing all results

[jmeckley]

this would be an issue with the coding not the sql statement. If it's grabbing all results then your if statement is always interpreted as false and never appending the addtional criteria.

If the " - All - " option is always the first option in your list then use this logic

If Not DDEmp.SelectedIndex = 0 Then strSQL += " and empName = '" & DDEmp.SelectedItem.Text & "'"

If it's always the last one then the code would look like this

If Not DDEmp.SelectedIndex = DDEmp.Items.Count - 1 Then strSQL += " and empName = '" & DDEmp.SelectedItem.Text & "'"

[NeonBurner]

its default is " - All - " and i'm getting the right SQL statement, but still not getting results
heres my SQL

Select * from CalTime where [7am] Like '%%' and [empName] = 'Kris test'

but its grabbing all the empNames not just Kris test? i've tried (), [], everything i can think of I've never had so much trouble on a simple SQL statement

[NeonBurner]

never mind i'm dimmed my OLECommand to soon, thanx for all your help