How to use variable dates in an SQL query inside vb.net without syntax 11 error

Question

I have a basic vb.net program that pulls a query from an SQL database. My program works correctly if I hard code the date, however when I change the code from:

Dim dtstartdate As String = DateTime.Today
Dim dttomorrow As DateTime = DateTime.Today.AddDays(1)
Dim dtenddate As DateTime = dttomorrow.AddSeconds(-1)
Try
    For icounter = 1 To 2
        Call GetLocationInfo()



        connectionString = "Data Source=" & LocationDB & ";Initial Catalog=database;Persist Security Info=True;User ID=login;Password=password"


        sql = "select count(sTicket_number) as tickets from tickets where dtcreated between 2/8/2014 AND 2/9/2014 "
        sqlCnn = New SqlConnection(connectionString)

        sqlCnn.Open()

TO:

Dim dtstartdate As String = DateTime.Today
Dim dttomorrow As DateTime = DateTime.Today.AddDays(1)
Dim dtenddate As DateTime = dttomorrow.AddSeconds(-1)

Try
    For icounter = 1 To 2
        Call GetLocationInfo()



        connectionString = "Data Source=" & LocationDB & ";Initial Catalog=database;Persist Security Info=True;User ID=login;Password=password"


        sql = "select count(sTicket_number) as tickets from tickets where dtcreated between " & dtstartdate & " AND " & dtenddate & ""
        sqlCnn = New SqlConnection(connectionString)

        sqlCnn.Open()

I get a "Syntax Error near '11'" What am I doing incorrectly with the dtstartdate and dtenddate?

Did you try putting single quotes around the dates? select count(sTicket_number) as tickets from tickets where dtcreated between '" & dtstartdate & "' AND '" & dtenddate & "'"? — Tim
– Tim, Commented Feb 9, 2014 at 22:55
I didn't, and that seemed to have worked. Why do I need to add the single quotes? — Shmewnix
– Shmewnix, Commented Feb 9, 2014 at 22:56
Dates are enclosed in single quotes in SQL. Not sure why it worked for you when they were hard-coded without single quotes. — Tim
– Tim, Commented Feb 9, 2014 at 22:57
The hardcoded version worked because it is only a date. DateTime contains also a time part that is separated by a whitespace from the date — Flat Eric
– Flat Eric, Commented Feb 9, 2014 at 23:03

Victor Zakharov · Accepted Answer · 2014-02-09 23:01:06Z

3

You need to be using SQL parameters instead. Otherwise you are looking for a lot of debugging in the long run + your code is vulnerable to SQL injection.

sql = "select count(sTicket_number) as tickets from tickets where dtcreated between @START_DATE AND @END_DATE"

Dim cmd As New SqlCommand(sql, sqlCnn)

cmd.Parameters.AddWithValue("@START_DATE", dtstartdate)
cmd.Parameters.AddWithValue("@END_DATE", dtenddate )

answered Feb 9, 2014 at 23:01

Victor Zakharov

26.6k18 gold badges95 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim · Accepted Answer · 2014-02-09 23:03:13Z

You need to enclose the dates in single quotes ('). Additionally, I'd recommend getting into the practice of using parameterized queries to prevent SQL Injection Attacks. Something like this:

Using sqlCnn As SqlConnection = New SqlConnection(connectionString)

    sql = "select count(sTicket_number) as tickets from tickets where dtcreated between @StartDate AND @EndDate"
    SqlCommand cmd = new SqlCommand(sql);
    cmd.Parameters.AddWithValue("@StartDate", dtstartdate)
    cmd.Parameters.AddWithValue("@EndDate", dtenddate)

    sqlCnn.Open()

    ' Do the rest of your data access here

End Using

Using a parameterized query will both prevent SQL Injection Attacks and enable you to supply the parameter values without worrying about whether they need to be quoted or not.

Collectives™ on Stack Overflow

How to use variable dates in an SQL query inside vb.net without syntax 11 error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related