0

I have converted my database from Access to Sql as Sql didn't accept format() so is displaying an error.

This is my code:

DefaultStr = "" &
"SELECT StudentAccount.Dated, StudentAccountS.StAdmNo, StudentAccountS.StClass, " &
"StudentAccountS.StName, StudentAccount.Perticular, StudentAccount.Amount,StudentAccount.Remark,StudentAccount.PayMode,TransactionID " &
"FROM (StudentAccount LEFT OUTER JOIN StudentAccountS ON StudentAccount.SSID = StudentAccountS.SSID) " &
"WHERE (StudentAccount.Debit) and (StudentAccount.Dated Between " &
"#" & Format(DateFrom, "MM/dd/yyyy") & "# AND #" & Format(DateTo, "MM/dd/yyyy") & "#)"


Select Case SIndex
    Case 0
        SelStr = " AND (StudentAccount.PayMode = '" & OptionStr & "') Order By StudentAccount.Dated"
    Case 1
        SelStr = " AND (StudentAccount.Perticular = '" & OptionStr & "') Order By StudentAccount.Dated"
    Case 2, 3
        SelStr = " AND (StudentAccount.TransType = '" & filterStr & "') Order By StudentAccount.Dated"
    Case Else
        SelStr = Nothing
End Select

Da = New SqlDataAdapter(DefaultStr & SelStr, Conn)
Ds = New DataSet
Da.Fill(Ds)
stef
  • 14,172
  • 2
  • 48
  • 70
Ankit
  • 1
  • 2
  • 1
    In `mssql`, when You store `date` (or `datetime`) value don't use `#` (like in `Access`) but single quote. What is `DateTo` and `DateFrom`, variables? If they are then use something like this : `'" & String.Format("{0:MM/dd/yyyy}", DateTo) & "'` – nelek Sep 06 '15 at 08:11
  • 1
    I would also add that you should not be using string concatenation for building sql commands. use parameterized sql. see this http://stackoverflow.com/questions/306668/are-parameters-really-enough-to-prevent-sql-injections – chrisl08 Sep 06 '15 at 10:07
  • This also throws the same error – Ankit Sep 06 '15 at 10:32
  • The error is in this part of the code: `WHERE (StudentAccount.Debit) and (StudentAccount.Dated` StudentAccount.Debit does not appear to be a boolean value so it cannot be used with `and`. – Chris Dunaway Sep 08 '15 at 14:37

1 Answers1

0

You have to use something like this:

Dim CMD As SqlClient.SqlCommand = Conn.CreateCommand
Dim dtStart As DateTime = New DateTime(2015, 9, 6, 10, 1, 0)
Dim dtEnd As DateTime = New DateTime(2015, 9, 6, 11, 0, 0)

CMD.CommandText = "SELECT * FROM Table1 WHERE Date BETWEEN '" & dtStart.ToString("yyyy-MM-dd HH:mm:ss") & "' AND '" & _
                dtEnd.ToString("yyyy-MM-dd HH:mm:ss") & "'"
Dim DA As New SqlClient.SqlDataAdapter
DA.SelectCommand = CMD
Dim DT As New DataTable
DA.Fill(DT)

Howewer i suggest you to start learing SqlParameter for queries (that are much more reliable, as for example in dealing with sql injection). Simply use something like that:

CMD.Parameters.Add("@DateStart", SqlDbType.DateTime2, 20).Value = dtStart
CMD.Parameters.Add("@DateEnd", SqlDbType.DateTime2, 20).Value = dtEnd
CMD.CommandText = "SELECT * FROM Table1 WHERE Date BETWEEN @DateStart AND @DateEnd"
Raffaello
  • 112
  • 4