Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    access database data insertion

    I am currently trying to teach myself coldfusion and am having an issue getting the data to transfer through. any help would be appreciated.

    none of the data goes through, but the database shows a null insertion.

    <cfparam name="userFirst" default="">
    <cfparam name="userLast" default="">
    <cfparam name="userName" default="">
    <cfparam name="userPWD" default="">
    <cfparam name="userEmail" default="">
    <cfparam name="WarningBit" default="0">
    <cfparam name="Mode" default="view">

    <cfif Mode is "Submit">
    <cfif userFirst is ""><cfset WarningBit=1><cfset firstError="yes"></cfif>
    <cfif userLast is ""><cfset WarningBit=1><cfset lastError="yes"></cfif>
    <cfif userName is ""><cfset warningBit=1><cfset nameError="yes"></cfif>
    <cfif userPWD is ""><cfset WarningBit=1><cfset pwdError="yes"></cfif>
    <cfif userEmail is ""><cfset WarningBit=1><cfset emailError="yes"></cfif>

    <cfif WarningBit is 0>
    <cfif find(" ", userEmail) GT 0><cfset WarningBit=1><cfset emailError="yes"></cfif>
    <cfif find("@", userEmail) is 0><cfset WarningBit=1><cfset emailError="yes"></cfif>
    <cfif find(".", userEmail) is 0><cfset WarningBit=1><cfset emailError="yes"></cfif>
    </cfif>
    </cfif>

    <cfquery name="RegisterUser" datasource="fusion">
    INSERT INTO Users(userName, userPWD, userEmail, userFirst, userLast)
    SELECT <cfif userName is not "">'#userName#'<cfelse>NULL</cfif>,
    <cfif userPWD is not "">'#userPWD#'<cfelse>NULL</cfif>,
    <cfif userFirst is not "">'#userFirst#'<cfelse>NULL</cfif>,
    <cfif userLast is not "">'#userLast#'<cfelse>NULL</cfif>,
    <cfif userEmail is not "">'#userEmail#'<cfelse>NULL</cfif>
    </cfquery>
    <html>
    <br /><br /><br /><br /><br /><br />
    <div align="center">
    <head align=center>Registry</head>
    </div>
    <body>
    <h2 align=center>New User Registration</h2>

    <table align=center>
    <tr>
    <td>First Name: </td>
    <td><input type="text" name="userFirst" value="#userFirst#" size="30" maxlength="30">*</td>
    </tr>
    <tr>
    <td>Last Name: </td>
    <td><input type="text" name="userLast" value="#userLast#" size="30" maxlength="30">*</td>
    </tr>
    <tr>
    <td>User Name: </td>
    <td><input type="text" name="userName" value="#userName#" size="30" maxlength="30">*</td>
    </tr>
    <tr>
    <td>Password: </td>
    <td><input type="password" name="userPWD" value="#userPWD#" size="30" maxlength="30">*</td>
    </tr>
    <tr>
    <td>E-Mail: </td>
    <td><input type="text" name="userEmail" value="#userEmail#" size="30" maxlenght="30">*</td>
    </tr>
    <div align="center">
    <table>
    <div align=center>
    <input align="center" type="submit" value="submit">
    </div>
    </table>
    </table>
    <input type="hidden" name="mode" value="Submit">

    </div>
    </body>
    </html>

  • #2
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    You're going to get NULLs inserted as soon as you load the form in your browser, because you're setting all of your variables to default to empty strings. The INSERT statement is going to happen no matter what (i.e. it's not waiting for a form submission).

    However, right now you can't even submit data from the form, because you don't have a <form> tag. The code should look something like below (much simplified; you can add whatever error checking you like). Also note that the <head> tag is not a displayable element, it just signifies a section for tags that the browser understands which are not displayed directly on the page (like the <title> tag to set the document title).

    Code:
    <!--- Only do a database insert once the form has been submitted --->
    <cfif structKeyExists( form, 'mode' )>
      <cfquery name="registerUser" datasource="fusion">
        INSERT INTO Users ( userName, userPWD, userEmail, userFirst, userLast )
        SELECT 
          '#form.userName#',
          '#form.userPWD#',
          '#form.userFirst#',
          '#form.userLast#',
          '#form.userEmail#'
      </cfquery>	
    </cfif>
    
    
    <html>
      <head>
        <title>My Page</title>
      </head>
    
      <body>
        <form method="POST" action="">
    
          <div align="center">Registry</div>
        
          <h2 align=center>New User Registration</h2>
    
          <table align=center>
            <tr>
              <td>First Name: </td>
              <td><input type="text" name="userFirst" value="#userFirst#" size="30" maxlength="30">*</td> 
            </tr>
            <tr>
              <td>Last Name: </td>
              <td><input type="text" name="userLast" value="#userLast#" size="30" maxlength="30">*</td>
            </tr>
            <tr>
              <td>User Name: </td>
              <td><input type="text" name="userName" value="#userName#" size="30" maxlength="30">*</td>
            </tr>
            <tr>
              <td>Password: </td>
              <td><input type="password" name="userPWD" value="#userPWD#" size="30" maxlength="30">*</td>
            </tr>
            <tr>
              <td>E-Mail: </td>
              <td><input type="text" name="userEmail" value="#userEmail#" size="30" maxlenght="30">*</td>
            </tr>
          </table>
    
          <div align="center">
            <input align="center" type="submit" value="submit">
          </div> 
          <input type="hidden" name="mode" value="Submit">
        </form>
      </body>
    </html>
    Btw, I don't know why you'd rather to insert NULLs into the database rather than empty strings. This is generally not a good design principle to have fields like that accept NULL, and adds an extra level of complexity into your query processing. So I took out the <cfif>'s that inserted NULLs into your database. In general, those fields shouldn't even accept NULL as a value; NULL should be reserved for a field where it can not yet be determined what the value should be, or needs to explicitly be set to "no object/relation".

    Also, you must learn to indent your code so that you know which tags are "children" of other tags. It makes the code MUCH more readable and understandable, and helps you get your markup correct so that you don't have unmatched start / end tags.

    -Greg

  • #3
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ty for your help. I'm still really new to this so I appreciate your honesty and i will look over the changes that you have made.


  •  

    Posting Permissions

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