/ AND . in DATE, Please Help :(

[magic2melody]

i am trying add date value to my access database, it is succes on my local host, but isnt in my hosting

Line 159:

dbconn.Execute("INSERT INTO customers (cName,cUserName,cLastName,cMail,cTelephone,cMobile,cBorn,cPassword,cAddress) VALUES ('"&BadWordClean(Request.Form("formName"))&"','"&BadWordClean(Request.Form("formUserName"))&"','"&BadWordClean(Request.Form("formLastName"))&"','"&BadWordClean(Request.Form("formMail"))&"','"&BadWordClean(Request.Form("formTelephone"))&"','"&BadWordClean(Request.Form("formMobile"))&"','"&Replace(sDob,".","/")&"','"&BadWordClean(Request.Form("formPassword"))&"','"&BadWordClean(Request.Form("formAddress"))&"')")

it returns:

Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
customers.asp, line 159


if i use '"&Replace(sDob,".","/")&"' it is working but i must use XX.XX.XXXX standart, not XX/XX/XXXX

please help me thank you very much

FULL OF CODE:
Session.LCID = 1055
If Request.Form("formName") <> "" Then
sDob = Request.Form("formBornDay")&"."&Request.Form("formBornMonth")&"."&Request.Form("formBornYear")
dbconn.Execute("INSERT INTO customers (cName,cUserName,cLastName,cMail,cTelephone,cMobile,cBorn,cPassword,cAddress) VALUES ('"&BadWordClean(Request.Form("formName"))&"','"&BadWordClean(Request.Form("formUserName"))&"','"&BadWordClean(Request.Form("formLastName"))&"','"&BadWordClean(Request.Form("formMail"))&"','"&BadWordClean(Request.Form("formTelephone"))&"','"&BadWordClean(Request.Form("formMobile"))&"','"&Replace(sDob,".","/")&"','"&BadWordClean(Request.Form("formPassword"))&"','"&BadWordClean(Request.Form("formAddress"))&"')")
End If

 

[antic]

Could you post the resulting SQL string? Not knowing what WordClean etc. results in, it's hard to judge.

But rule of thumb for me, is ALWAYS use the date format for SQL as YYYY-MM-DD hh:mm

That is, year FIRST, then month, then day. That format is constant, whereas dd-mm-yy can be interpreted as month-day or day-month depending on the locale of the server.

You can't go wrong with yyyy-mm-dd. This is a formatting routine you can use:

  Function SQLDateString(dt, bIncludeTime)

    Dim s
    s = "NULL" ' default return value

    If IsDate(dt) Then
      s = Year(dt) & "-" & Month(dt) & "-" & Day(dt)
      If bIncludeTime Then s = s & " " & Hour(dt) & ":" & Minute(dt) & ":" & Second(dt)
    End If

    SQLDateString = s

  End Function

Hope that helps.