superthang
New Member
I have an ASP.NET page that needs to push a little data to an MS Access 2003 database. The process requires a Select on one record, Inserting one new record and Updating one record. I am connecting to the Access database via an \[code\]OleDbConnection\[/code\] connection. So far, the Select and Insert functions are working exactly as expected (so I know my connection is good). The Update, however, fails to update any rows. The function to update the record looks like this:\[code\]public static int UpdateDeviceDates(int deviceId, DateTime nextTestDate){ var conn = DbConnect.AccessConnection(); var sqlString = WebConfigurationManager.AppSettings["UpdateDeviceDates"]; using (var cmd = new OleDbCommand(sqlString, conn)) { cmd.Parameters.AddWithValue("@DeviceID", deviceId); cmd.Parameters.AddWithValue("@NextTestDate", nextTestDate); cmd.CommandType = CommandType.Text; conn.Open(); var result = cmd.ExecuteNonQuery(); return result; }}\[/code\]The sqlString pulled back from the web.config file looks like this:\[code\]UPDATE tblDevice SET tblDevice.NextTestDate = @nextTestDate, tblDevice.FirstNoticeDate = Null, tblDevice.SecondNoticeDate = Null WHERE DeviceID=@deviceId;\[/code\]This query works fine if you paste it into a new Access query window and hit run, so I know the syntax is correct. I have done quite a bit of testing and figured out that it is the \[code\]@nextTestDate\[/code\] field that is causing it to fail. When I took that out of the SQL string, it updated the record as expected. This is disconcerting, because the date I pass through to the Insert function works just fine. I have looked around quite a bit and the closest I found to an answer was "Can't update date in aspx to a MS-ACCESS table". The main answer there was to change the parameter to a \[code\]ShortDateString\[/code\]. I tried that to no effect. It was also suggested to bracket the date in \[code\]#\[/code\], since that is what Access does in its own queries. Unfortunately, that didn't work either. I don't know why either of these should have been necessary, because the date comes through in exactly the same format as in the Insert statement and that works fine. I'm at my wits end here because the only thing I've found to make that query work is to remove the date parameter (which would defeat the main purpose of the query).