Updating Access Database using ASP

[darthrocco]

Hi all,

I'm about at wits end on this issue so I'm hoping that you can help me out with it. I have a site that I am trying to create an admin section to maintain a particular section. The section is for "tour dates" and since the site lists tour dates in chrono order, I have the data type in Access set to "date/time". Now, when I use the admin tool to update the site, I get the following error message:

----
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/adminProcessTourDates.asp, line 49
----

Now I've seen on other boards that this is sometimes caused by a conflict with the date/time data type and on those sites, the standard answer is "don't use the date/time data type." Pardon my french, but that seems like a shitty solution. Has anyone out there done this and how did you get it to work? Any help would be greatly appreciated. Here is the SQL command that I am using:

----
UPDATE tourdates SET sMonth='September',nYear=2007,dStartDate=#9/3/2007#,dEndDate=#9/9/2007#,sClubName='Various Rooms',sCity='New York City',sState='NY',sPerformWith='',sNotes='this is a test',sLinks='' WHERE tourDateID='117'
----

Thanks in advance!

 

[nicklieb]

the classic issue with dates is the formatting of the dates, and I recollect in the my distant memory that Access was the most 'finikiest'

try changing the format around and try these

yyyy-mm-dd
yyyy/mm/dd
mm/dd/yyyy

etc

good luck!

 

[johnwm]

The error indicates type mismatch in criteria expression which may refer to your WHERE clause. You seem to have delimited the 117 with apostrophes which would be correct if the datatype for tourDateID is a text or string type. If it is an integer omit the delimiters thus.

WHERE tourDateID=117

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[darthrocco]

Guys, this worked like a champ! Thank you so much for your help. You guys rule!