Need Help with Record Insert Error

[manhattanmediaworks]

Hi Coldfuschions,

I am a novice and am trying to use the Record Insertion Form Wizard in
D8/ColdFusion to add a record to a MS Access database. I had this working fine
yesterday and somehow I am getting errors now, which I can't seem to circumvent
on my own. The most puzzling part is that I am using the built in wizard and
not handwriting any code, so it seems the wizard is producing bad code, which I
would find to be surprising" Here is the code fragment that the wizard is
generating that is causing the error: I bolded the line that the parser is
barking about. Can anyone help?? Thanks!


INSERT INTO [Path to my database and table] (Name, Date, Project,
Activity, BillableHours, NonBillableHours)
VALUES (
<cfif IsDefined("FORM.Name") AND #FORM.Name# NEQ "">
'#FORM.Name#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Date") AND #FORM.Date# NEQ "">
'#FORM.Date#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Project") AND #FORM.Project# NEQ "">
'#FORM.Project#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Activity") AND #FORM.Activity# NEQ "">
'#FORM.Activity#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.BillableHours") AND #FORM.BillableHours# NEQ "">
#FORM.BillableHours#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.NonBillableHours") AND #FORM.NonBillableHours# NEQ "">
#FORM.NonBillableHours#
<cfelse>
NULL
<b></cfif></b>
)
</cfquery>
<cflocation url="thanks.html">
</cfif>

[manhattanmediaworks]

Hi Coldfuschions,

I am a novice and am trying to use the Record Insertion Form Wizard in
D8/ColdFusion to add a record to a MS Access database. I had this working fine
yesterday and somehow I am getting errors now, which I can't seem to circumvent
on my own. The most puzzling part is that I am using the built in wizard and
not handwriting any code, so it seems the wizard is producing bad code, which I
would find to be surprising.

Below is the code segment that the wizard is generating that is causing the
error. I noted the line below that the parser is barking about (towards end of
code segment). The connection to the db is good. Any ideas?? Thanks in
advance for removing me from misery!


INSERT INTO [Path to my database and table] (Name, Date, Project,
Activity, BillableHours, NonBillableHours)
VALUES (
<cfif IsDefined("FORM.Name") AND #FORM.Name# NEQ "">
'#FORM.Name#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Date") AND #FORM.Date# NEQ "">
'#FORM.Date#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Project") AND #FORM.Project# NEQ "">
'#FORM.Project#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Activity") AND #FORM.Activity# NEQ "">
'#FORM.Activity#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.BillableHours") AND #FORM.BillableHours# NEQ "">
#FORM.BillableHours#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.NonBillableHours") AND #FORM.NonBillableHours# NEQ "">
#FORM.NonBillableHours#
<cfelse>
NULL
</cfif> <!--- This is the line that the parser is finding to be problematic
--->
)
</cfquery>
<cflocation url="thanks.html">
</cfif>

[sfbell]

Just checking, but is there start tags (i.e, <cfquery name="..." etc> and <cfif>) for both the </cfquery> and </cfif> at the end?

[Dan Bracuk]

For your specific problem, you have to look above the code you posted. Here
are your last three lines of code.
</cfquery>
<cflocation url="thanks.html">
</cfif>

You need an opening cfif tag and cfquery tag, in that order.

By the way, you should learn about database normalization. No sense having a
database if the data is unuseable.

[manhattanmediaworks]

sfbell/Dan, thanks for the replies...the whole code snippet is below and now
you can see the opening tags.

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="conn_hours">
INSERT INTO [Path to my database/table] (Name, Date, Project, Activity,
BillableHours, NonBillableHours)
VALUES (
<cfif IsDefined("FORM.Name") AND #FORM.Name# NEQ "">
'#FORM.Name#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Date") AND #FORM.Date# NEQ "">
'#FORM.Date#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Project") AND #FORM.Project# NEQ "">
'#FORM.Project#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.Activity") AND #FORM.Activity# NEQ "">
'#FORM.Activity#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.BillableHours") AND #FORM.BillableHours# NEQ "">
#FORM.BillableHours#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.NonBillableHours") AND #FORM.NonBillableHours# NEQ "">
#FORM.NonBillableHours#
<cfelse>
NULL
</cfif>
)
</cfquery>
<cflocation url="thanks.html">
</cfif>

[paross1]

What specific error are you getting? Also, are the BillableHours and NonBillableHours fields actually a numeric datatype in the database? If not, then you will need single quotes.

Phil

[manhattanmediaworks]

Error Occurred While Processing Request
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 [path to my file]...TimeInputter2.cfm: line 40

38 : <cfelse>
39 : NULL
40 : </cfif>
41 : )
42 : </cfquery>




--------------------------------------------------------------------------------

SQL INSERT INTO C:\CFusionMX7\db\raskin3.TimeEntries2 (Name, Date, Project,
Activity, BillableHours, NonBillableHours) VALUES ( 'Phil Raskin' , 'asdf' ,
'UMDNJ001' , 'Programming' , 3 , 3 )
DATASOURCE conn_hours
VENDORERRORCODE -3502
SQLSTATE 42000

Resources:

[sfbell]

the <cfquery at the start needs a name="any_query_name" as well.

[sfbell]

The <cfquery> tag must always have name and datasource as required fields. This
name is in case you ever need to reference the query on the page. The query
name must always begin with a letter as well.

[paross1]

I think that your problem is here --> SQL INSERT INTO <b>C:\CFusionMX7\db\raskin3.TimeEntries2</b> Shouldn't this be a DATASOURCE? I think Dreamweaver is messing you up (what a shock).

Phil

[sfbell]

the datasource has been called in the <cfquery> datasource already, try just putting the table name after the insert into?

[Dan Bracuk]

Copy and paste
INSERT INTO YourTableName
(Name, Date, Project, Activity, BillableHours, NonBillableHours) VALUES
( 'Phil Raskin' , 'asdf' , 'UMDNJ001' , 'Programming' , 3 , 3 )
into access and see if it runs. If not, start deleting fields and values
until it does. The last field you delete will be the one that is causing the
problem.

I'm guessing it's the date.

[manhattanmediaworks]

Before all hell breaks loose and all hair falls out, I decided to create a bare
bones MS Access database with a couple of text formatted fields and with the
wizard I was able to add rows. With each success I added more functionality
and sure enough when I try to add a date I run into problems. These errors
come up when I submit that value as text, date or date MS Access, in the
'submit as' dropdown menu in the record insertion form wizard. I would use
text when the field is formatted as text, and the latter two when the field in
the db is formated as a date. I am totally stumped. When I put in a string
into the date part, it works (when it is formatted as a string, but then when I
input something like ''04/27/06" I get an error.

[paross1]

How about trying this?

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

--or--

you might consider using cfqueryparam tags in your VALUES specifying the
appropriate cfsqltype, such as CF_SQL_TIMESTAMP CF_SQL_DATE, etc.

Phil

[manhattanmediaworks]

paross1, your first suggestion yielded this error:

The error occurred in C:\CFusionMX7\wwwroot\hours\easyNameInputter.cfm: line 34

32 : <cfelse>
33 : NULL
34 : </cfif>
35 : )
36 : </cfquery>


--------------------------------------------------------------------------------

SQL INSERT INTO C:\CFusionMX7\db\raskin4.HourLog (RecordID, "First Name",
"Last Name", Activity, Date) VALUES ( NULL , 'Mike' , 'Wilson' , 'Graphic
Design' , {d '2005-04-29'} )



ultimately I want to include a javascript calendar pop up date picker - the
issue seems to be getting the format of the field jiving between CF and Access.
text works when text is expected, but I can't get dates to work. I am looking
to do a mm/dd/yyyy format. This is so lame that the wizard is producing bad
code. Nice experience for a new user

[Dan Bracuk]

It might be more than that. Doesn't Access get angry when you use a reserved word like date, for a field name and then use that field in a cfquery? Someone who uses Access might know.

[paross1]

Ahhh yes, reserved words, good point! I should have tripped over the obvious on the way to trying to provide an answer.

Phil :o

[manhattanmediaworks]

That was it. Amazing...thanks so much anyway to all of you who tried to lend a
hand. When I get beyond my novice phase, I will come back and help others out.

Date seems like such a logical column header - I would have thought that when
searching on "date" or "reserved keywords" in MS Access I would see something
about this. Then again it is the evil empire.

Thanks again.

[manhattanmediaworks]

<a target=_blank class=ftalternatingbarlinklarge href="http://www.aspfaq.com/show.asp?id=2080

Good">[url]http://www.aspfaq.com/show.asp?id=2080[/url]

Good</a> resource above on reserved keywords.