Syntax error in date in query expression '#12122008#'.

[shawnmiller77]

Need Help ASAP!

History: Installed new SBS 2003 server on Monday. Migrated IIS, website and current Access database over to new server. I did not develop the website or Access database. Former IT guy is no where to be found. Everything is working on the new server except for one .asp page in the website that ties to the Access database. When I try and input data on the page and save it I receive the follwoing error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '#12122008#'.

/it/edit/itnew.asp, line 91


I by no means know anything about Access, but it seems like there is a problem with the "EntryDate" format. I have tried everything I can think of. I have been looking at this for three days with no success. This page works fine on the old server.

Here is my SQL code:
<%
' *** Insert Record: set variables

If (CStr(Request(" MM_insert")) <> "") Then

MM_editConnecti on = MM_CFSLOG_STRIN G
MM_editTable = "TBL_InbondCove rSheet"
MM_editRedirect Url = "default.as p"
MM_fieldsStr = "IT|value|Statu s|value|EntryDa te|value|BLMAWB |value|ETA|valu e|ArrivalDate|v alue|SSRelease| value|OrgCheck| value|Importer| value|FaxCheck| value|SignedPOD |value|Filed|va lue|Broker|valu e|ImportService Fee|value|Consi gnee|value|Hand ingCharge|value |Qty|value|CFSC ODCOLLECT|value |Weight|value|S torBegins|value |CFSDelivery|va lue|DaysInStore |value|CODPayTo |value|StorEnds |value|StorageS etToEnd|value|C odAmount|value| StoreRate|value |StorageFee|val ue|TotalDue|val ue|podpro|value |stgjob|value|s sajob|value|nvj ob|value|STGLOA D|value|misc|va lue|WebComments |value|Internal Comments|value"
MM_columnsStr = "ITNumber|',non e,''|Status|',n one,''|EntryDat e|#,none,NULL|B LMAWBNumber|',n one,''|ETA|',no ne,''|ArrivalDa te|',none,''|SS Rel|none,1,0|Or gChk|none,1,0|I mporter|',none, ''|FaxChk|none, 1,0|SignedPOD|n one,1,0|Filed|n one,1,0|Broker| ',none,''|ImpSe rFee|none,none, NULL|Consignee| ',none,''|HandC hg|none,none,NU LL|Pieces|none, none,NULL|CFSCo llectCOD|',none ,''|Weight|',no ne,''|StorBegin s|',none,''|CFS Delivery|none,1 ,0|DaysInStor|' ,none,''|CODPay To|',none,''|St orEnds|',none,' '|SetStorEnds|n one,1,0|CODAmnt |none,none,NULL |StoreRate|none ,none,NULL|Tota lStorFee|none,n one,NULL|TotalD ue|none,none,NU LL|POD_PRO|',no ne,''|STGJOB|', none,''|SSALOAD |',none,''|NVJo b|',none,''|STG Load|',none,''| Load_MISC|',non e,''|WebComment s|',none,''|Int ernalComments|' ,none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fields Str, "|")
MM_columns = Split(MM_column sStr, "|")

' set the form values
For i = LBound(MM_field s) To UBound(MM_field s) Step 2
MM_fields(i+1) = CStr(Request.Fo rm(MM_fields(i) ))
Next

' append the query string to the redirect URL
If (MM_editRedirec tUrl <> "" And Request.QuerySt ring <> "") Then
If (InStr(1, MM_editRedirect Url, "?", vbTextCompare) = 0 And Request.QuerySt ring <> "") Then
MM_editRedirect Url = MM_editRedirect Url & "?" & Request.QuerySt ring
Else
MM_editRedirect Url = MM_editRedirect Url & "&" & Request.QuerySt ring
End If
End If

End If
%>

I have also attached a screenshot of the Access Table that is in question.


Can someone please help me...I need to have this fixed by Monday morning.

thanks,

Shawn

[MMcCarthy]

Not sure about the asp code but Access can't read a date in this format
'#12122008#'

the date has to be read as #12/12/2008#.

[MMcCarthy]

I've spoken with one of our ASP experts and I am moving this post to the ASP forum. Hopefully they will be able to help you out.

[JamieHowarth0]

Hi there,

I'm the ASP expert in question. It looks like a case of GIGO (garbage in, garbage out) as your code seems fairly sound (albeit it's the first time I've ever seen it written in such a way!).

Can you please post two things to help me hone in on the problem:
1) Sample data which DEFINITELY breaks this page;
2) The HTML source of the form that posts to this page - basically, what page is before this one when going through the website? Get the source (particularly everything between the <form> and </form> tags, and any Javascript that might be relevant) and attach it to your next post back here.

Hope this helps.

codegecko