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_editConnection = MM_CFSLOG_STRING
MM_editTable = "TBL_InbondCoverSheet"
MM_editRedirectUrl = "default.asp"
MM_fieldsStr = "IT|value|Status|value|EntryDate|value|BLMAWB|valu e|ETA|value|ArrivalDate|value|SSRelease|value|OrgC heck|value|Importer|value|FaxCheck|value|SignedPOD |value|Filed|value|Broker|value|ImportServiceFee|v alue|Consignee|value|HandingCharge|value|Qty|value |CFSCODCOLLECT|value|Weight|value|StorBegins|value |CFSDelivery|value|DaysInStore|value|CODPayTo|valu e|StorEnds|value|StorageSetToEnd|value|CodAmount|v alue|StoreRate|value|StorageFee|value|TotalDue|val ue|podpro|value|stgjob|value|ssajob|value|nvjob|va lue|STGLOAD|value|misc|value|WebComments|value|Int ernalComments|value"
MM_columnsStr = "ITNumber|',none,''|Status|',none,''|EntryDate|#,n one,NULL|BLMAWBNumber|',none,''|ETA|',none,''|Arri valDate|',none,''|SSRel|none,1,0|OrgChk|none,1,0|I mporter|',none,''|FaxChk|none,1,0|SignedPOD|none,1 ,0|Filed|none,1,0|Broker|',none,''|ImpSerFee|none, none,NULL|Consignee|',none,''|HandChg|none,none,NU LL|Pieces|none,none,NULL|CFSCollectCOD|',none,''|W eight|',none,''|StorBegins|',none,''|CFSDelivery|n one,1,0|DaysInStor|',none,''|CODPayTo|',none,''|St orEnds|',none,''|SetStorEnds|none,1,0|CODAmnt|none ,none,NULL|StoreRate|none,none,NULL|TotalStorFee|n one,none,NULL|TotalDue|none,none,NULL|POD_PRO|',no ne,''|STGJOB|',none,''|SSALOAD|',none,''|NVJob|',n one,''|STGLoad|',none,''|Load_MISC|',none,''|WebCo mments|',none,''|InternalComments|',none,''"

' create the MM_fields and MM_columns arrays
MM_fields = Split(MM_fieldsStr, "|")
MM_columns = Split(MM_columnsStr, "|")

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

' append the query string to the redirect URL
If (MM_editRedirectUrl <> "" And Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.QueryString <> "") Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
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