SQL query
-
I am trying to populate a backup Access Table for specific range of dates; however, the whole table is getting backed up. It works fine with other fields (eg. the commented myCmd statement). Any suggestion will be appreciated Dim sDate, eDate, cmd As String 'starting date and end date sDate = DateTimePicker1.Value eDate = DateTimePicker2.Value Dim ssdate As Date = DateTimePicker1.Value '******* Start of Nash-Sutcliffe Efficiency Algorithm ******** '------- Populating dailySummary Table ----- myCmd = "SELECT * INTO dailySummary FROM " & inpTable & " WHERE " & fdate & " > " & ssdate 'myCmd = "SELECT * INTO dailySummary FROM " & inpTable & " WHERE " & obsDaily & " BETWEEN 4.0 AND 5.0" cmmd = New OleDbCommand(myCmd, myConnection) MsgBox(myCmd) cmmd.ExecuteScalar()
-
I am trying to populate a backup Access Table for specific range of dates; however, the whole table is getting backed up. It works fine with other fields (eg. the commented myCmd statement). Any suggestion will be appreciated Dim sDate, eDate, cmd As String 'starting date and end date sDate = DateTimePicker1.Value eDate = DateTimePicker2.Value Dim ssdate As Date = DateTimePicker1.Value '******* Start of Nash-Sutcliffe Efficiency Algorithm ******** '------- Populating dailySummary Table ----- myCmd = "SELECT * INTO dailySummary FROM " & inpTable & " WHERE " & fdate & " > " & ssdate 'myCmd = "SELECT * INTO dailySummary FROM " & inpTable & " WHERE " & obsDaily & " BETWEEN 4.0 AND 5.0" cmmd = New OleDbCommand(myCmd, myConnection) MsgBox(myCmd) cmmd.ExecuteScalar()
You have multiple problems with this. The first is that your SQL statement is wrong. The WHERE clause is comparing your first date with the second, and alway socmming up with True, so that's why you're getting every record in the table instead of a range of them. You should be comparing the field in the table you want to those two dates, not the two dates to each other. Second, dates in Access must be wrapped in # characters. You don't have any of those in your SQL string, so the dates are not even valid, no matter what they look like. Lastly, you're using string concantenation to build the SQL query. This has numerous problems with it as dates in strings can be in many different formats and that might not match the date format Access is using. Use parameterized queries instead. Read this[^] for an explanation of why concantenating strings together to build your SQL statements is a bad thing, and how to use parameterized queries to eliminate some of the problems you're having passing dates into the SQL.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007