0

I'm trying to know what is the correct syntax of sql wildcard expression in vb.net

what I want is to select my record when my dayVariable match the Day record, sample if my dayVariable = "T" then my sql expression need to display all record in Day that has a letter of "T" even "TF" or "FT", as long as there is a "T"

All I try only display record which Day has a record match exactly to "T"

SELECT * FROM tblSomething WHERE Day LIKE '%" + dayVariable + "%'

SELECT * FROM tblSomething WHERE Day LIKE '%" & dayVariable & "%'

Note: I try to build query in sql express like this:

 SELECT * FROM tblSomething WHERE Day LIKE '%T%'

and I achieve to display all records that has "T" letter in Day column.

This is my real Vb.net

    Private Function checkSecConflict(ByVal sTime As String, ByVal eTime As String, ByVal secID As String, ByVal day As String) As Boolean
    If Connect.State = ConnectionState.Closed Then
        Connect.Open()
    End If
    Dim cmd2 As New SqlCommand("Select * from tblSubjectOffer Where  eTime > " & sTime & " AND sTime < " & eTime & " AND Day LIKE '%' + day + '%' AND SectionID = '" & secID & "'", Connect)
    Dim dr As SqlDataReader = cmd2.ExecuteReader()
    dr.Read()
    If dr.HasRows Then

        Return True
    Else

        Return False

    End If
    dr.Close()
    cmd2.Dispose()
End Function

just ignore my where statement about the time and secID, because I figured out that my wildcard about day is the real problem, I think so, :)

Thanks in advance to those who want to help me solve my problem,

Arvin
  • 31
  • 1
  • 4
  • Other than missing start and end quotes in the vb I don't see a problem. are you getting any errors? also doing the concatenation in sql would allow you to parameterise the query. – T I Apr 04 '15 at 19:37
  • What is your underlying database? – Gordon Linoff Apr 04 '15 at 19:38
  • @T I , I'm not getting any error but my code doesn't meet what I want. is it correct on what I understand about wildcard that, it display all the record as long as it meet the where statement or a specific letter or string – Arvin Apr 04 '15 at 19:53
  • I used sql express as my database, @GordonLinoff – Arvin Apr 04 '15 at 19:55
  • Could you show the real vb.net code that you use when you want to issue this query ? – Steve Apr 04 '15 at 19:56
  • @Steve i just edited my question, – Arvin Apr 04 '15 at 20:06
  • You should try to avoid inline sql. However, if you absolutely have to, make sure to parameterize it. – Yatrix Apr 04 '15 at 20:17

3 Answers3

0
" AND Day LIKE '%' + day + '%' AND SectionID = '"

This should be: " AND Day LIKE '%" + day + "%' AND SectionID = '"

You weren't appending the day parameter to your string properly.

Yatrix
  • 13,361
  • 16
  • 48
  • 78
  • Aaaaand right as I finish my answer, I see your edit. =\ – Yatrix Apr 04 '15 at 20:10
  • sorry, i forgot to change my real vb.net syntax when i pasted it into the question , but if you see my first part of my question I've already tried what you wanted me to do. – Arvin Apr 04 '15 at 20:15
0

Your code has some problems that need to be fixed.
First, do not use a global Sql Connection. There is a connection pooling mechanism that allows rapid creation and open of connection.

Second, use a parameterized query to pass the values to your database engine specifying the correct datatype for your database columns. Allowing automatic interpretation of your string values will lead to error and will render your code easily exploitable using Sql Injection

Finally, every disposable object (like connection, command and readers) should be enclosed in a Using Statement that provides the correct functionality to close and dispose these objects (also in case of exceptions)

Private Function checkSecConflict(ByVal sTime As String, ByVal eTime As String, ByVal secID As String, ByVal day As String) As Boolean

    Dim sqlText = "Select * from tblSubjectOffer " & _ 
                  "Where  eTime > @starttime AND sTime < @endtime AND " & _
                  "Day LIKE @day AND SectionID = @secid"
    Using con = new SqlConnection(....connection string here ....)
    Using cmd2 As New SqlCommand(sqlText, con)
        con.Open()
        cmd.Parameters.Add("@starttime", SqlDbType.DateTime).Value = Convert.ToDateTime(eTime)
        cmd.Parameters.Add("@endttime", SqlDbType.DateTime).Value = Convert.ToDateTime(sTime)
        cmd.Parameters.Add("@day", SqlDbType.NVarChar).Value = "%" & day & "%"
        cmd.Parameters.Add("@secid", SqlDbType.Int).Value = Convert.ToInt32(secID)
        Using dr = cmd2.ExecuteReader()
           ' the value True/False of HasRows could be used as return here
           return dr.HasRows   
        End Using
    End Using
    End Using
End Function

NOTE: This code assumes that your fields eTime and sTime are smalldatetime (or similar), the field Day is a nvarchar and the SectionID is a integer

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • thanks for correcting my codes and I've already change my code similar to your suggestion, but it doesn't solve my real problem which is I want to know how to make my syntax select all records that likely similar to what I input to the "day" variable, sample if I input "T" value then all records that has a "T" on it will display, – Arvin Apr 05 '15 at 03:46
  • The code above selects all records that in the column named "Day" you have a string that contains the letter "T" in any position of the column and with any cases. Of course there are other conditions in that where all linked with AND so every condition should be respected. If you remove the other conditions do you get the correct set of records? If yes then the problem is in the other conditions – Steve Apr 05 '15 at 08:06
0

I always use ... Day LIKE '*" + day + "*' ... and it seems to work OK.

OSKM
  • 728
  • 14
  • 25