SQL Date field help - Data type mismatch in criteria expression.

SQL Date field help - Data type mismatch in criteria expression. (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

travismp -> SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 15:21:10)
I have an "Edit" Form. My user can click their record to bring up a form with all the pre-existing data, they change the text, hit submit then new data is saved. Works well. There are 50 fields total, if they will out EVERY single record and hit submit it works great, however there are about 10 date based fields in the backend DB, if they leave ANY of the dat fields empty then I get this error: Microsoft JET Database Engine error '80040e07' Data type mismatch in criteria expression. My code is almost 900 lines long so I am only going to post a sample of where I think the issue is. <%if oktogo=1 and request.form("submit")="Update Record" then%> <%'***** UPDATE RECORD IN DATABASE%> <% idtofind=replace(request.form("id"),"/","") sql = "update tbl_Main set " _ & " Name='"&Name&"', " _ & " HomeAddress='"&HomeAddress&"', " _ & " RequestDate='"&RequestDate&"', " _ & " Sex='"&Sex&"', " _ Can I make any changes to this line to tell it is a date field so it goes on past this if it is left blank? & " RequestDate='"&RequestDate&"', " _ Thank you all.

BeTheBall -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 15:42:56)
What type of database are you using?

travismp -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 15:45:17)
Access 2000

BeTheBall -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 15:50:36)
Give this a try. Paste this function at the top of your page: <% Function verifyDate(dateField) If isDate(dateField) then myDate = "#" & dateField & "#" Else myDate = "null" End If verifyDate = myDate End Function %> Then, in your SQL, do this: & " RequestDate="&verifyDate(RequestDate)&", " _ The function should allow the insertion of a date if a valid date is entered or insert null if no date or an invalid date.

travismp -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 15:53:13)
OK I will... I already have <%function cleanme(strvalue)%> <%cleanme=replace(strvalue,"'","''")%> <%end function%> at the top of my page. How should I add your function without effecting current function?

travismp -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 15:57:03)

I just added the functions side by side like this:

<%function cleanme(strvalue)%>
	<%cleanme=replace(strvalue,"'","''")%>
<%end function%>

<% 
Function verifyDate(dateField) 
If isDate(dateField) then 
myDate = "#" & dateField & "#" 
Else 
myDate = "null" 
End If 
verifyDate = myDate 
End Function 
%>

It looks like that might work. Is there anything wrong with doing it this way?

travismp -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 16:08:32)
It looks like everything works great the way you had me set it up. I think this is a closed case. Thank you Duane as always you come through for me! Way to go.

BeTheBall -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 16:30:52)
Great. You don't need so many delimiters on the first function. You can write it like this: <% function cleanme(strvalue) cleanme=replace(strvalue,"'","''") end function %>

travismp -> RE: SQL Date field help - Data type mismatch in criteria expression. (6/16/2008 16:45:39)
OK I will make that change too. I am still working on that page so it will just take a second. THANK YOU AGAIN. I tested it twice and all seems well. Travis

Page: [1]

0.0625