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