) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => manhattanmediaworks [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SQL INSERT INTO C:\CFusionMX7\db\raskin3.TimeEntries2 Shouldn't this be a DATASOURCE? I think Dreamweaver is messing you up (what a shock). Phil [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => paross1 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 10 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Need Help with Record Insert Error - Coldfusion Database Access

Need Help with Record Insert Error - Coldfusion Database Access

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 cirvent 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 ...

  1. #1

    Default Need Help with Record Insert Error

    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 cirvent
    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 pr 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 Guest

  2. #2

    Default Need Help with Record Insert Error

    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 cirvent
    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 pr 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 pr is finding to be problematic
    --->
    )
    </cfquery>
    <cflocation url="thanks.html">
    </cfif>

    manhattanmediaworks Guest

  3. #3

    Default Re: Need Help with Record Insert Error

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

  4. #4

    Default Re: Need Help with Record Insert Error

    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.

    Dan Bracuk Guest

  5. #5

    Default Re: Need Help with Record Insert Error

    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>

    manhattanmediaworks Guest

  6. #6

    Default Re: Need Help with Record Insert Error

    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
    paross1 Guest

  7. #7

    Default Re: Need Help with Record Insert Error

    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:



    manhattanmediaworks Guest

  8. #8

    Default Re: Need Help with Record Insert Error

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

  9. #9

    Default Re: Need Help with Record Insert Error

    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.

    sfbell Guest

  10. #10

    Default Re: Need Help with Record Insert Error

    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
    paross1 Guest

  11. #11

    Default Re: Need Help with Record Insert Error

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

  12. #12

    Default Re: Need Help with Record Insert Error

    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.

    Dan Bracuk Guest

  13. #13

    Default Re: Need Help with Record Insert Error

    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.

    manhattanmediaworks Guest

  14. #14

    Default Re: Need Help with Record Insert Error

    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


    paross1 Guest

  15. #15

    Default Re: Need Help with Record Insert Error

    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

    manhattanmediaworks Guest

  16. #16

    Default Re: Need Help with Record Insert Error

    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.
    Dan Bracuk Guest

  17. #17

    Default Re: Need Help with Record Insert Error

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

    Phil :o
    paross1 Guest

  18. #18

    Default Re: Need Help with Record Insert Error

    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 Guest

  19. #19

    Default Re: Need Help with Record Insert Error

    <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.
    manhattanmediaworks Guest

Similar Threads

  1. Insert record
    By t.d. in forum Coldfusion - Getting Started
    Replies: 14
    Last Post: July 25th, 02:25 PM
  2. JavaScript error in Insert Record server behavior
    By FredMurphy in forum Dreamweaver AppDev
    Replies: 2
    Last Post: July 22nd, 12:09 PM
  3. Replies: 0
    Last Post: March 7th, 08:23 PM
  4. How to insert a new record
    By European in forum ASP.NET Building Controls
    Replies: 0
    Last Post: December 1st, 03:39 AM
  5. Insert Record Error-502 Bad Gateway-HELP
    By rachelannmurphy webforumsuser@macromedia.com in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: September 13th, 06:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •