ASP update Database

[Mike Mueller]

Please show me the errors in my ways. I am trying to take
dates from a form (Exp1,2,3) and update date/time fields in
an MDB. (I started a new thread as I am using a different
process now).

Here is the SQL string:

UPDATE AdvSkillsBag SET
Expire1=#::dtExp1::#,Expire2=#::dtExp2::#,Expire3=#::dtExp3:
:# WHERE ID='::ID::'


I get the following errors
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
date in query expression '#::dtExp1::#'.
/amb_inv/update.asp, line 32



Here is the code

<%
'Setup & read in the information from the form
Dim dtExp1, dtExp2, dtExp3
dtExp1 = request.form("Expire1")
dtExp2 = request.form("Expire2")
dtExp3 = request.form("Expire3")

'Check for null dates
if len(dtExp1) <8 then
dtExp1 = "12/31/09"
End If

if len(dtExp2) <8 then
dtExp2 = "12/31/09"
End If

If len(dtExp3) <8 then
dtExp3 = "12/31/09"
End If


'Setup and create the SQL statements
Dim strSQL1
Dim objConn

strSQL1 = "UPDATE AdvSkillsBag SET
Expire1=#::dtExp1::#,Expire2=#::dtExp2::#,Expire3=#::dtExp3:
:# WHERE ID='::ID::'"
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open application("AmboInv_ConnectionString")

response.write strSQL1

objConn.Execute (strSQL1)
%>

 

[jon spivey]

Hi Mike,
are you sure you're putting valid dates in? Depending on which is easiest
for you you'd want
2003-12-02 or
2-december-2003 or
12/2/2003
I prefer the first one as it does away with any date format confusion. The
sql you have looks sound

 

[grw]

UPDATE AdvSkillsBag SET
Expire1=#&"dtExp1&"#,Expire2=#&"dtExp2&"#,Expire3=#&"dtExp3&"> :# WHERE
ID=&"ID

You are using a DRW format not within a DRW, use asp

 

[grw]

<edit>

UPDATE AdvSkillsBag SET
Expire1=#"&dtExp1&"#,Expire2=#"&dtExp2&"#,Expire3=#"&dtExp3&"# WHERE ID=&"ID

Also assumes ID is a valid variable

UPDATE AdvSkillsBag SET
Expire1=#&"dtExp1&"#,Expire2=#&"dtExp2&"#,Expire3=#&"dtExp3&"> :# WHERE
ID=&"ID

You are using a DRW format not within a DRW, use asp

Please show me the errors in my ways. I am trying to take
dates from a form (Exp1,2,3) and update date/time fields in
an MDB. (I started a new thread as I am using a different
process now).

Here is the SQL string:

UPDATE AdvSkillsBag SET
Expire1=#::dtExp1::#,Expire2=#::dtExp2::#,Expire3=#::dtExp3:
:# WHERE ID='::ID::'


I get the following errors
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in
date in query expression '#::dtExp1::#'.
/amb_inv/update.asp, line 32



Here is the code

<%
'Setup & read in the information from the form
Dim dtExp1, dtExp2, dtExp3
dtExp1 = request.form("Expire1")
dtExp2 = request.form("Expire2")
dtExp3 = request.form("Expire3")

'Check for null dates
if len(dtExp1) <8 then
dtExp1 = "12/31/09"
End If

if len(dtExp2) <8 then
dtExp2 = "12/31/09"
End If

If len(dtExp3) <8 then
dtExp3 = "12/31/09"
End If


'Setup and create the SQL statements
Dim strSQL1
Dim objConn

strSQL1 = "UPDATE AdvSkillsBag SET
Expire1=#::dtExp1::#,Expire2=#::dtExp2::#,Expire3=#::dtExp3:
:# WHERE ID='::ID::'"
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open application("AmboInv_ConnectionString")

response.write strSQL1

objConn.Execute (strSQL1)
%>