I recently moved a project that I made from Access to MS SQLServer, but of course this means I need to change some of the code in my SQL statements.
I have this subroutine where I am getting all records where the field
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in MaintenanceControl.exe
Additional information: Incorrect syntax near '#'.
Private Sub btn30Days_Click(sender As Object, e As EventArgs) Handles btn30Days.Click
Dim td As String
Dim sd As String
sd = Format(Today.AddDays(31), "MM/dd/yyyy")
td = Format(Today, "MM/dd/yyyy")
sql = "SELECT * FROM dbo.tblContracts WHERE DateRenew BETWEEN #" & sd & "# AND #" & td & "#;"
conAd = New OleDb.OleDbDataAdapter(sql, con)
conset = New DataSet
ugModules.DataSource = conset
Catch ex As Exception
MsgBox("Failed to retrieve contract information from database, refer to error log")
Easier and less error-prone is to directly use the date functions of SQL Server.
sql = "SELECT * FROM dbo.tblContracts WHERE DateRenew BETWEEN CONVERT(DATE, GETDATE()) AND DATEADD(DAY, 31, CONVERT(DATE, GETDATE()))"
(line break for readability)
If it doesn't matter that
GETDATE() includes the current time, you can leave out the
CONVERT(DATE, ...) parts.