David David - 1 year ago 51
Vb.net Question

Access to SQL Server syntax changeover

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

is within 30 days... How do I convert this to SQL? At the moment I get the following error message

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
errorLog(ex.Message, ex.StackTrace)
MsgBox("Failed to retrieve contract information from database, refer to error log")
End Try

End Sub

Answer Source

Easier and less error-prone is to directly use the date functions of SQL Server.

sql = "SELECT * FROM dbo.tblContracts WHERE DateRenew BETWEEN 

(line break for readability)

If it doesn't matter that GETDATE() includes the current time, you can leave out the CONVERT(DATE, ...) parts.