Last post Jul 03, 2008 10:57 PM by hkbeer
Jul 02, 2008 08:10 PM|hkbeer|LINK
I have a gridview in ASP web form that connects and updates and inserts records in mdb file and one field is a date.
I want ASP to treat it as DateTime format. What should I set for the type and any formatting is needed in mdb file when I create it in Access ?
I first set type in Access the field to Date/Time, then when I use code to get DateTime.Now and save to that field, I get this error, or no date/time is saved to that field (However, if I set field type in Access mdb to Text, the time and date of now is
saved to the filed, but then ASP treat it as text instead of date and this causes trouble in my future use of this date).
Did I do something wrong ? Thanks
[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
Jul 02, 2008 09:10 PM|DotNetAdvisor|LINK
I believe there's only one kind of datetime type in access. All the formatting will need to be done when you bind it to the page.
Jul 02, 2008 09:47 PM|hkbeer|LINK
Thanks. I asked coz I first set type in Access the field to Date/Time, then when I use code to get DateTime.Now and save to that field, I get this error.
What should I do ? Thanks
Jul 03, 2008 12:05 AM|hkbeer|LINK
Hello all, I ended up having a solution to set field to text in mdb structure, and then in ASP store dates as text, and then in to display it in a query connected GridView using
Select DateValue([ApplyTime]) as [AppliedTime] from TableA
It works. I hope this also provides some help in case other runs into same trouble. [:D]
Jul 03, 2008 02:34 AM|Mikesdotnetting|LINK
Actually, saving the DateTime as a TEXT item is not a good solution. It means it will be treated as text in the database, which means that if you need to sort, it will be done alphabetically instead of chronologically. Also, any date comparisons will not
work as you expect. You should use DateTime, then solve your error properly by either using the correct delimiters or better still, parameters:
http://www.mikesdotnetting.com/Article.aspx?ArticleID=76. In fact, in the case you cited, where you want the current date and time entered, you could have simply used the Access built-in
Insert into table (mydatefield) Values (Now())
Jul 03, 2008 04:16 AM|hkbeer|LINK
Thanks Mike, you are right.
But I do not know where to put that insert SQL statement. Should I put it inside <Script> and make it on insert record event or somewhere else ?
Jul 03, 2008 04:23 PM|Mikesdotnetting|LINK
Amend the InsertCommand or insert statement that you have already. If you are using a SqlDataSource or AccessDataSource, it will be the InsertCommand.
Jul 03, 2008 08:09 PM|hkbeer|LINK
Sorry but I really dunno where to put this statement. In Accessdatasource1 (ie below the line of select SQL ?) or elsewhere ?
Jul 03, 2008 10:57 PM|hkbeer|LINK
I tried in the SQL Statement and it works !! Thanks Mike. You are great !