如何在vb.net内的SQL查询中使用可变日期而不会出现语法11错误

debugcn 发表于 Dev

Shmewnix

我有一个基本的vb.net程序，可以从SQL数据库中提取查询。如果我对日期进行硬编码，则我的程序可以正常工作，但是当我将代码从以下位置更改时：

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()

到：

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()

我收到“ 11附近的语法错误”。dtstartdate和dtenddate在做什么？

新石器时代

您需要改为使用SQL参数。否则，从长远来看，您将需要进行大量调试，并且您的代码容易受到SQL注入的攻击。

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 )

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。