SELECT * FROM province [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => toofastdad [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] => 6 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> VALUES ( '#Form.title#', '#Form.name#', #Form.date#, '#Form.email#', '#Form.location#', '#Form.comment#', '#Form.link#', '#Form.emailhideshow#', '#Form.approved#') [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => jdeline [ip] => jdeline@deline. [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] => 15 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Insert record - Coldfusion - Getting Started

Insert record - Coldfusion - Getting Started

Hi all, I'm having a bit of a problem at the moment. I'm basically trying to insert a record to a MS Access database. I'm using dreamweaver MX. What I want to do is insert the 1 record into two different tables in the database. I've tried just about everything now and still can't get it to work....

  1. #1

    Default Insert record

    Hi all, I'm having a bit of a problem at the moment. I'm basically trying to
    insert a record to a MS Access database. I'm using dreamweaver MX. What I want
    to do is insert the 1 record into two different tables in the database. I've
    tried just about everything now and still can't get it to work.

    t.d. Guest

  2. #2

    Default Re: Insert record

    Can you post the code you are using and/or any error messages you are getting? Its hard to diagnose without more specific information.
    TA-Selene Guest

  3. #3

    Default Re: Insert record

    Here is the code: <cfset CurrentPage=GetFileFromPath(GetTemplatePath())> <cfif
    IsDefined('FORM.MM_InsertRecord') AND FORM.MM_InsertRecord EQ 'form1'>
    <cfquery datasource='CGVEC' username='Tony' password='Admin'> INSERT INTO
    alltend (ID, title, 'desc', added, ends) VALUES ( <cfif IsDefined('FORM.ID')
    AND #FORM.ID# NEQ ''> #FORM.ID# <cfelse> NULL </cfif> ,
    <cfif IsDefined('FORM.title') AND #FORM.title# NEQ ''> '#FORM.title#'
    <cfelse> NULL </cfif> , <cfif IsDefined('FORM.desc') AND #FORM.desc#
    NEQ ''> '#FORM.desc#' <cfelse> NULL </cfif> , <cfif
    IsDefined('FORM.added') AND #FORM.added# NEQ ''> '#FORM.added#'
    <cfelse> NULL </cfif> , <cfif IsDefined('FORM.ends') AND #FORM.ends#
    NEQ ''> '#FORM.ends#' <cfelse> NULL </cfif> ) </cfquery>
    <cflocation url='/tenders.cfm'> </cfif> <?xml version='1.0'
    encoding='iso-8859-1'?> <!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0
    Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
    <html xmlns='http://www.w3.org/1999/xhtml'> <head> <title>Untitled
    Doent</title> <meta http-equiv='Content-Type' content='text/html;
    cht=iso-8859-1' /> </head> <body> <form method='post' name='form1'
    action='<cfoutput>#CurrentPage#</cfoutput>'> <table align='center'> <tr
    valign='baseline'> <td nowrap align='right'>ID:</td> <td><input
    type='text' name='ID' value='' size='32'></td> </tr> <tr
    valign='baseline'> <td nowrap align='right'>Title:</td> <td><input
    type='text' name='title' value='' size='32'></td> </tr> <tr
    valign='baseline'> <td nowrap align='right'>Desc:</td> <td><input
    type='text' name='desc' value='' size='32'></td> </tr> <tr
    valign='baseline'> <td nowrap align='right'>Added:</td> <td><input
    type='text' name='added' value='' size='32'></td> </tr> <tr
    valign='baseline'> <td nowrap align='right'>Ends:</td> <td><input
    type='text' name='ends' value='' size='32'></td> </tr> <tr
    valign='baseline'> <td nowrap align='right'>&amp;nbsp;</td>
    <td><input type='submit' value='Insert Record'></td> </tr> </table>
    <input type='hidden' name='MM_InsertRecord' value='form1'> </form>
    <p>&amp;nbsp;</p> </body> </html> What I want to do is to enter the form
    into 2 different tables within the same database. The other table is called
    generaltend. Would really appreciate it if you could help me. Thanks

    t.d. Guest

  4. #4

    Default Insert Record

    I would like a user to register prior to gaining access to the next level.
    This is working, however, when testing the database I realised that I was
    entered in the database many times!

    The information below is in the form, the table name is client:
    FirstName, LastName, Address, City, Province, PostalCode, Phone, Email,
    UserName, Password

    How do I check to see if the users email address is already in the database
    and if it isn't, insert the record. If it is, I would like to return an error
    message stating that the user and email address already exists. I am using CF
    MX 7 and MySQL database.

    Thanks for your help...Colin

    toofastdad Guest

  5. #5

    Default Re: Insert Record

    <cftransaction>
    <cfquery name="qryFind">
    Select emailaddress
    From myTable
    Where emailaddress = '#form.Email#'
    </cfquery>
    <cfif qryFind.RecordCount Is 0>
    <cfquery name="qryInsert>
    Insert Into myTable(emailaddress)
    Values('#form.Email#')
    </cfquery>
    <cfelse>
    Opps already exists..............
    </cfif>
    </cftransaction>

    Ken


    The ScareCrow Guest

  6. #6

    Default Re: Insert Record

    Ken...thanks for the snippet, however I keep getting errors. Here is the code
    from my page prior to inserting your snippet, could you please have a look. I
    think I may be inserting in the wrong places.

    Thanks...Colin

    <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

    <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "register">
    <cfquery datasource="#ds#">
    INSERT INTO client (FirstName, LastName, Address, City, Province,
    PostalCode, Phone, Email, UserName, Password) VALUES (
    <cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
    '#FORM.FirstName#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
    '#FORM.LastName#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.Address") AND #FORM.Address# NEQ "">
    '#FORM.Address#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.City") AND #FORM.City# NEQ "">
    '#FORM.City#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.Province") AND #FORM.Province# NEQ "">
    '#FORM.Province#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.PostalCode") AND #FORM.PostalCode# NEQ "">
    '#FORM.PostalCode#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.Phone") AND #FORM.Phone# NEQ "">
    '#FORM.Phone#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.Email") AND #FORM.Email# NEQ "">
    '#FORM.Email#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.UserName") AND #FORM.UserName# NEQ "">
    '#FORM.UserName#'
    <cfelse>
    NULL
    </cfif>
    ,
    <cfif IsDefined("FORM.Password") AND #FORM.Password# NEQ "">
    '#FORM.Password#'
    <cfelse>
    NULL
    </cfif>
    )
    </cfquery>
    <!--- Send the notification to the office administrator. --->
    <CFMAIL from="meblahblab.com"
    to="#IIf(isDefined("Form.Email"),"Form.Email",DE(" "))#" type="html"
    subject="Welcome to Blahblah">
    <!--- You can refernce any variables available to this page in the
    included page. --->
    <CFINCLUDE template="userpassword.cfm">
    <CFINCLUDE template="thank_you.cfm">
    </CFMAIL>
    <cflocation url="thank_you.cfm">
    </cfif>
    <cfquery name="province" datasource="#ds#">
    SELECT * FROM province
    </cfquery>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    toofastdad Guest

  7. #7

    Default Re: Insert Record

    I would need to see the error message before I could help, but here are a
    couple of things I have noticed.

    Password and possibly email are reserved words, this could cause an error. So
    either rename the columns or enclose in [ ]

    You have cfif's around the values in the insert and if the form value is not
    defined or is empty then you insert a null value (this is the correct way to do
    this).
    1. Are all fields set in the db to accept null ?
    2. Are you really sure you want to do this, at present it is possible to
    insert a completely empty record. I would think you would need to make at
    least the email, username and passwords required ?

    Just a point to note:
    If you make the email address the key to the table, this by default will not
    allow a duplicate entry, so this would also do what you want, but you would
    need to catch the error and process it.

    Ken

    The ScareCrow Guest

  8. #8

    Default Re: Insert Record

    Ken... thanks for the reply! Password and email are not reserved words
    according to Ben Forta (CF MX 7 WACK). I will now look a little more closely
    for this and use [ ] when appropriate.

    As for your suggestion of making the email the Primary Key, great idea. I
    changed the database and it works beautifully! Now, I need to create a custom
    error message. Is that easy to do? Do you have any suggestions on how to catch
    the error and process it?

    Thanks Ken...C



    toofastdad Guest

  9. #9

    Default Re: Insert Record

    Ken... I am using client side validation for all fields in the form priior to
    processing and the CLIENT_ID (PK) in the table was not NULL the new database
    has the email as the PK and CLIENT_ID is auto increment , INDEX Key

    toofastdad Guest

  10. #10

    Default Re: Insert Record

    I'm not sure what's in Ben's book, but I think he may be talking about CF.
    Password is a reserved work in MS Access.

    Anyway to catch the error use cftry

    <cftry>
    cfquery stuff in here.........

    <cfcatch type="Database">
    error message or cflocation in here.................
    </cfcatch>
    </cftry>

    Ken

    The ScareCrow Guest

  11. #11

    Default Re: Insert Record

    Thanks Ken...I will give this a try today. Ben's book was referring to Query
    of Queries. I also looked up reserved words for MySQL and it looks the same, I
    haven't compared. All these database systems have subtle changes so it seems.

    [url]http://dev.mysql.com/doc/mysql/en/reserved-words.html[/url]

    I will post my results later Thanks...C

    toofastdad Guest

  12. #12

    Default Re: Insert Record

    Dear Ken,

    Thank you for your help with my database query, the cftry example worked
    perfectly for the error message. Using the email address as the PK did the
    trick.

    Thanks for your help, I hope you are around for my next dilemma!!

    C

    toofastdad Guest

  13. #13

    Default Re: Insert Record

    Another thing, I don't think that so many <cfif> tags are very efficient. I will suggest using cfset or cfparam, before the insert query.
    SilverStrike Guest

  14. #14

    Default Insert record

    Hi All,

    I'm at a complete loss. I must have built over a hundred forms and insert
    statements but this bug has me utterly bewildered.

    We're running CF MX. with MS access.

    Any help or advice greatfully recieved.

    Trevor



    Form code:

    <cfform ACTION="../update_user_comments.cfm" METHOD=POST>
    <p><strong>Post your comment</strong></p>

    <div style="background-color:#ffffff; padding:10px; border:dashed 1px #000000">


    <div class="floatleftcomments" style="width:100px"><p><strong>Your
    name</strong></p></div>
    <input type="text" name="Name" size="30">
    <br style="clear: both; font-size: 1px; line-height: 0; height: 0;" />

    <div class="floatleftcomments"
    style="width:100px"><p><strong>Email</strong></p></div>
    <input type="text" name="Email" size="30">
    <br style="clear: both; font-size: 1px; line-height: 0; height: 0;" />

    <div class="floatleftcomments" style="width:400px">
    <p>Display email address, tick for yes &nbsp;
    <input name="emailhideshow" type="checkbox" value="yes"></p>
    </div>
    <br style="clear: both; font-size: 1px; line-height: 0; height: 0;" />

    <div class="floatleftcomments"
    style="width:100px"><p><strong>Location</strong></p></div>
    <input type="text" name="Location" size="30">
    <br style="clear: both; font-size: 1px; line-height: 0; height: 0;" />

    <div class="floatleftcomments"
    style="width:100px"><p><strong>Date</strong></p></div>
    <input type="text" name="Date" size="30">
    <br style="clear: both; font-size: 1px; line-height: 0; height: 0;" />

    <div class="floatleftcomments" style="width:100px"><p><strong>Related
    link</strong></p></div><input type="text" name="Link" size="30" value="http://">
    <br style="clear: both; font-size: 1px; line-height: 0; height: 0;" />

    <div class="floatleftcomments"
    style="width:100px"><p><strong>Comment</strong></p></div><textarea
    name="Comment" cols="32" rows="4"></textarea>

    <cfoutput><input type="hidden" name="title" value="#title#" /><input
    type="hidden" name="approved" value="no" /></cfoutput>
    <input type="submit" value="Submit">
    </div>
    </cfform>


    update statement

    <cfif not isdefined("Form.approved")>
    <cfset Form.approved = "No">
    </cfif>

    <cfif not isdefined("Form.emailhideshow")>
    <cfset Form.emailhideshow = "No">
    </cfif>

    <CFQUERY NAME="AddComments" DATASOURCE="uka_comments">
    INSERT INTO comments



    VALUES (
    '#Form.title#',
    '#Form.name#',
    #Form.date#,
    '#Form.email#',
    '#Form.location#',
    '#Form.comment#',
    '#Form.link#',
    '#Form.emailhideshow#',
    '#Form.approved#')
    </CFQUERY>



    error message


    Error Executing Database Query.
    No value given for one or more required parameters.

    The error occurred in
    C:\Inetpub\wwwroot\websites\UKA2006\update_user_co mments.cfm: line 24

    22 : '#Form.link#',
    23 : '#Form.emailhideshow#',
    24 : '#Form.approved#')
    25 : </CFQUERY>
    26 :




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

    SQL INSERT INTO comments VALUES ( 'wheat, its place in uk agriculture and
    farming', 'x', x, 'x', 'x', 'x', 'http://', 'No', 'no')
    DATASOURCE uka_comments
    VENDORERRORCODE 3088
    SQLSTATE &nbsp;

    Please try the following:
    Check the ColdFusion doentation to verify that you are using the correct
    syntax.
    Search the Knowledge Base to find a solution to your problem.


    Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
    Remote Address 192.168.1.33
    Referrer
    http://newserver/UKA2006/crops/wheat.cfml?comment=add&attributes.title=wheat,
    its place in uk agriculture and farming
    Date/Time 25-Jul-06 02:56 PM

    Stack Trace (click to expand)

    blackandwhite Guest

  15. #15

    Default Re: Insert record

    You need to enumerate the columns into which the VALUES are placed. See below.


    <CFQUERY NAME="AddComments" DATASOURCE="uka_comments">
    INSERT INTO comments

    <!--- your column names go here --->

    VALUES (
    '#Form.title#',
    '#Form.name#',
    #Form.date#,
    '#Form.email#',
    '#Form.location#',
    '#Form.comment#',
    '#Form.link#',
    '#Form.emailhideshow#',
    '#Form.approved#')
    </CFQUERY>

    jdeline Guest

Similar Threads

  1. Insert Record With cfloop...
    By Thomas D in forum Coldfusion - Getting Started
    Replies: 5
    Last Post: October 27th, 02:53 PM
  2. Conditionally Insert Record
    By jip in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 17th, 08:43 PM
  3. How to Insert record with foreign key id vs. value
    By KJ Klosson in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 7th, 01:02 AM
  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 a record
    By Tom in forum ASP Database
    Replies: 7
    Last Post: June 3rd, 05:05 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
  •