David David - 4 months ago 7
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

DateRenew
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")

Try
sql = "SELECT * FROM dbo.tblContracts WHERE DateRenew BETWEEN #" & sd & "# AND #" & td & "#;"
conAd = New OleDb.OleDbDataAdapter(sql, con)
conset = New DataSet
conAd.Fill(conset)
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

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.

Comments