CodingForums.com

CodingForums.com (http://www.codingforums.com/index.php)
-   ColdFusion (http://www.codingforums.com/forumdisplay.php?f=45)
-   -   access database data insertion (http://www.codingforums.com/showthread.php?t=242784)

chrisfusion 11-03-2011 10:49 AM
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.

Quote:
<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>

Gjslick 11-04-2011 05:07 PM
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

chrisfusion 11-05-2011 07:29 AM
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.


All times are GMT +1. The time now is 01:24 AM.

Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.