David David - 1 year ago 63
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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download