Error that I can't figure out

[stillwaiting]

----------------------------------------------------------------
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred in C:\CFusionMX\wwwroot\Test\update.cfm: line 67

65 : <cfelse>
66 : NULL
67 : </cfif>
68 : )
69 : </cfquery>

SQL INSERT INTO Legal Issues (title, link,"date") VALUES ( 'test' ,
'www.test.com' , #5/20/05# )
DATASOURCE sp2
VENDORERRORCODE -3502
SQLSTATE 42000
---------------------------------------------------------------

The entire code statement that the error occured in is as follows:
---------------------------------------------------------------
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"lgl_issues">
<cfquery datasource="sp2" password="************">
INSERT INTO Legal Issues (title, link,"date") VALUES (
<cfif IsDefined("FORM.title") AND #FORM.title# NEQ "">
'#FORM.title#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.link") AND #FORM.link# NEQ "">
'#FORM.link#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.date") AND #FORM.date# NEQ "">
###FORM.date###
<cfelse>
NULL
</cfif> <----This is line 67
)
</cfquery>
</cfif>
------------------------------------------------------------

I have another query above it that was put together the exact same way
with DW and it operates correctly but this one and the 4 more following
don't work. Any thoughts?

stillwaiting

[The ScareCrow]

The error is related to the way you are passing the date, should be
#CreateODBCDate(form.date)#

Ken

[stillwaiting]

The ScareCrow wrote:

> The error is related to the way you are passing the date, should be
> #CreateODBCDate(form.date)#
>
> Ken

Your going to have to elaborate a little more. Why does my code work for
the first form but not for the other ones? Where am I supposed to put
the above code? I tried putting your code in place of "#FORM.date# but
it returned the same error. I'm new at this and I'm afraid I need a
little more explanation.

<cfif IsDefined("FORM.date") AND #FORM.date# NEQ "">
###FORM.date###
<cfelse>
NULL
</cfif>

Thanks for the help so far,
stillwaiting

[The ScareCrow]

Why does my code work for the first form but not for the other ones?

I don't know as I have not seen all the code, but I would assume there is a
difference somewhere.

The attached code is what you need

Ken

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"lgl_issues">
<cfquery datasource="sp2" password="************">
INSERT INTO Legal Issues (title, link,"date")
VALUES (
<cfif IsDefined("FORM.title") AND
Len(Trim(FORM.title))>'#FORM.title#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.link") AND
Len(Trim(FORM.link))>'#FORM.link#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.date") AND
IsDate(FORM.date)>#CreateODBCDate(FORM.date)#<cfel se>NULL</cfif>
)
</cfquery>
</cfif>

[JMGibson3]

Have you tried [date] rather than "date" for your column name that's a keyword.
You probably also need [Legal Issues] for a table name since it has an
embedded space in it. Better yet, rename everything in both the DB and your
SQL, since for all I know, "link" may also be a keyword:

tblLegalIssues
fldTitle
fldLink
fldDate

[stillwaiting]

I'll give it a try. That sounds like it may do the trick.

ScareCrow, the code you gave me still didn't work.

stillwaiting

JMGibson3 wrote:

> Have you tried [date] rather than "date" for your column name that's a keyword.
> You probably also need [Legal Issues] for a table name since it has an
> embedded space in it. Better yet, rename everything in both the DB and your
> SQL, since for all I know, "link" may also be a keyword:
>
> tblLegalIssues
> fldTitle
> fldLink
> fldDate
>
>

[The ScareCrow]

Damn, can't believe I missed the space in the table name.

JMGibson3 is correct and you should change the db, but the attached will also
work

Ken

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
"lgl_issues">
<cfquery datasource="sp2" password="************">
INSERT INTO [Legal Issues] ([title], [link],[date])
VALUES (
<cfif IsDefined("FORM.title") AND
Len(Trim(FORM.title))>'#FORM.title#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.link") AND
Len(Trim(FORM.link))>'#FORM.link#'<cfelse>NULL</cfif>,
<cfif IsDefined("FORM.date") AND
IsDate(FORM.date)>#CreateODBCDate(FORM.date)#<cfel se>NULL</cfif>
)
</cfquery>
</cfif>

[stillwaiting]

The ScareCrow wrote:

> Damn, can't believe I missed the space in the table name.
>
> JMGibson3 is correct and you should change the db, but the attached will also
> work
>
> Ken
>
> <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ
> "lgl_issues">
> <cfquery datasource="sp2" password="************">
> INSERT INTO [Legal Issues] ([title], [link],[date])
> VALUES (
> <cfif IsDefined("FORM.title") AND
> Len(Trim(FORM.title))>'#FORM.title#'<cfelse>NULL</cfif>,
> <cfif IsDefined("FORM.link") AND
> Len(Trim(FORM.link))>'#FORM.link#'<cfelse>NULL</cfif>,
> <cfif IsDefined("FORM.date") AND
> IsDate(FORM.date)>#CreateODBCDate(FORM.date)#<cfel se>NULL</cfif>
> )
> </cfquery>
> </cfif>
>

How exactly does the #CreateODBCDate(FORM.date)# differ in function? I
would actually prefer to have the date and maybe even time added to the
DB automatically. Could someone point me in the right direction on
figuring out how to do that?

By the way, I renamed all the tables and columns in my DB and it works
flawlessly now. Thanks a ton guys. You guys on these forums are a life
saver for those of us that are learning.

stillwaiting

[The ScareCrow]

createodbcdate passes an odbc date data type to the sql query, where as
#FORM.date# passes a string.

You should always keep dates as dates, the only time you should convert a date
value is to display it to a user.

To have the db automatically insert the current date/time in the column.
Open you ms access db
Click on the date/time column
In the bottom pane you will see the word "default value" in the space next to
this enter Now()
Also under this is also "required"

Now when you insert a record, do not include the date/time column in the
insert sql statement and the current date/time will be entered.

But, this will not enter the current date/time if the record is updated, this
must be done in the sql statement.

Ken