Solved

ColdFusion Insert Query Inserts the Record Twice

Posted on 2007-03-27
12
376 Views
Last Modified: 2013-12-16
I cant figure this out I think I have stared at it to long.  When I hit the insert Job Button can anyone tell me why it is inserting each record twice.  What is happening is this:

Text File:

Record1 ~
Record2 ~

Database:

Record1
Record2
Record1
Record2

Here is my Code:

<html>
<head>
<title>t</title>
<body>

<cfset file ="test.txt">
<cfset path = "D:\inetpub\test.txt">
<cfoutput>#Path#</cfoutput><br />

<cfif FileExists("#path#") is "Yes">

  File Is There: <br />
  <cfelse> no file
</cfif>  
<CFFILE ACTION="READ" FILE="#Path#" VARIABLE="datavar">


<CFSET crlf = "~">
<CFLOOP INDEX="orec" LIST="#datavar#" DELIMITERS="#crlf#">
<cfif len(trim(orec)) >

<cfquery name="getjobnumberInfo" datasource="test" dbtype="test">
      select max(jid) as NaxNum from Jobs
</cfquery>



<CFSCRIPT>
Jobnumber = ListGetAt(orec, 1,"|");
Jobtitle = ListGetAt(orec, 2,"|");
Location = ListGetAt(orec, 3,"|");
State = ListGetAt(orec, 4,"|");
Company = ListGetAt(orec, 5,"|");
Jobfield2 = ListGetAt(orec, 6,"|");
Website = ListGetAt(orec, 7,"|");
Joblisting = ListGetAt(orec, 8,"|");
</CFSCRIPT>
<cfparam name="County" default="Providence">
<cfparam name="Experience" default="">
<cfparam name="Referred" default="">
<cfparam name="Single" default="J">
<cfparam name="CBIA" default="N">
<cfparam name="exp_date" default="">
<cfparam name="eresumes" default="N">
<cfparam name="send_resumes" default="0">
<cfset dateentered = CreateODBCDate(dateadd('d',-1,now()))>
<cfset timeentered = CreateODBCtime(now())>
<cfset Moddate = CreateODBCDate(dateadd('d',-1,now()))>
<cfset Modtime = CreateODBCtime(now())>
<cfset exp_date = CreateODBCdate(dateadd("YYYY",5,now()))>
<cfset Temp = Replace(getjobnumberInfo.NaxNum, 'J', '', 'ONE') + 3000 >
<cfset jobnumber = "J#Temp#" >
<cfset login = "login">
<cfset pw = "ocjobs">
<cfset topusa = 1>







<table align="center">

<form action="test_get_jobs.cfm" method="post" name="InsertJobs">




<CFQUERY DATASOURCE="test" dbtype="test">
 INSERT INTO JOBS
(Company,jobnumber,City,State,location,JobTitle,topurl,JobField2,County,login,pw,single,dateentered,timeentered,Moddate,Modtime,eResumes,send_resumes,exp_date,topusa)
VALUES
('#Company#','#jobnumber#','#location#','#state#','#location#','#JobTitle#','#website#','#jobfield2#','#County#','#login#','#pw#','#single#',#dateentered#,#timeentered#,#Moddate#,#Modtime#,'#eResumes#','#send_resumes#',#exp_date#,'1')
 </CFQUERY>

</cfif>
</CFLOOP>

<tr>
<td><input type="submit" value="InsertJobs">
</td>
</tr>
</form>
</table>

</body>
</head>
</html>


0
Comment
Question by:ostashenp
  • 6
  • 5
12 Comments
 
LVL 12

Expert Comment

by:mkishline
ID: 18808054
The way that this page is laid out, every time you go to it the page will insert the records from the file, not just when you click the "insert job" button.

In your form, add a name attribute to your submit button <input type="submit" name="submit" value="InsertJobs" />

Then try this on the test_get_jobs.cfm page:

<cfif isDefined('form.submit')>
      <cfset file ="test.txt">
      <cfset path = "D:\inetpub\test.txt">
      <cfoutput>#Path#</cfoutput><br />

      <cfif FileExists("#path#") is "Yes">
            File Is There: <br />
      <cfelse>
            no file
      </cfif>  

      <CFFILE ACTION="READ" FILE="#Path#" VARIABLE="datavar">

      <CFSET crlf = "~">
      <CFLOOP INDEX="orec" LIST="#datavar#" DELIMITERS="#crlf#">
            <cfif len(trim(orec)) >
                  <cfquery name="getjobnumberInfo" datasource="test" dbtype="test">
                        select max(jid) as NaxNum from Jobs
                  </cfquery>
                  
                  <CFSCRIPT>
                        Jobnumber = ListGetAt(orec, 1,"|");
                        Jobtitle = ListGetAt(orec, 2,"|");
                        Location = ListGetAt(orec, 3,"|");
                        State = ListGetAt(orec, 4,"|");
                        Company = ListGetAt(orec, 5,"|");
                        Jobfield2 = ListGetAt(orec, 6,"|");
                        Website = ListGetAt(orec, 7,"|");
                        Joblisting = ListGetAt(orec, 8,"|");
                  </CFSCRIPT>
                  
                  <cfparam name="County" default="Providence">
                  <cfparam name="Experience" default="">
                  <cfparam name="Referred" default="">
                  <cfparam name="Single" default="J">
                  <cfparam name="CBIA" default="N">
                  <cfparam name="exp_date" default="">
                  <cfparam name="eresumes" default="N">
                  <cfparam name="send_resumes" default="0">
                  <cfset dateentered = CreateODBCDate(dateadd('d',-1,now()))>
                  <cfset timeentered = CreateODBCtime(now())>
                  <cfset Moddate = CreateODBCDate(dateadd('d',-1,now()))>
                  <cfset Modtime = CreateODBCtime(now())>
                  <cfset exp_date = CreateODBCdate(dateadd("YYYY",5,now()))>
                  <cfset Temp = Replace(getjobnumberInfo.NaxNum, 'J', '', 'ONE') + 3000 >
                  <cfset jobnumber = "J#Temp#" >
                  <cfset login = "login">
                  <cfset pw = "ocjobs">
                  <cfset topusa = 1>
                  
                  <CFQUERY DATASOURCE="test" dbtype="test">
                  INSERT INTO JOBS (Company,jobnumber,City,State,location,JobTitle,topurl,JobField2,County,login,pw,single,dateentered,timeentered,Moddate,Modtime,eResumes,send_resumes,exp_date,topusa)
                  VALUES ('#Company#','#jobnumber#','#location#','#state#','#location#','#JobTitle#','#website#','#jobfield2#','#County#','#login#','#pw#','#single#',#dateentered#,#timeentered#,#Moddate#,#Modtime#,'#eResumes#','#send_resumes#',#exp_date#,'1')
                  </CFQUERY>
            </cfif>
      </CFLOOP>
</cfif>
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 18808121
my guess is that the query fires when you load the page and then when you submit the form. I can't tell from your logic if orec would have a value when the page loads...

I would recommend changing your code to help simplify this...

A general "best practice" is separate presentation from the action

I'd put this in 2 files..

one that does the Display.. call it dsp_MyForm.cfm
one that does the Action.. call it act_MyForm.cfm

put all of the code for user input and feedback in dsp_MyForm and all the query and cffile stuff in act_MyForm. There would be no user interaction or html in the act_ file. It's strictly a processing page.

Call act_MyForm in the form action and use cflocation to send the user back to dsp_MyForm for feedback (or to a totally different form)

You can also group these files by type in directories called dsp and act (and qry if you like for all your general queries) The benefit of this is that as your project grows it makes debugging easier as you always know where your inserts and updates will be and where your UI stuff will be...




0
 
LVL 2

Author Comment

by:ostashenp
ID: 18810243
When I put in the line like suggested below, no data no is inputed... ?

<cfif isDefined('form.submit')>   <------THIS LINE
      <cfset file ="test.txt">
      <cfset path = "D:\inetpub\test.txt">
      <cfoutput>#Path#</cfoutput><br />

      <cfif FileExists("#path#") is "Yes">
            File Is There: <br />
      <cfelse>
            no file
      </cfif>  

      <CFFILE ACTION="READ" FILE="#Path#" VARIABLE="datavar">

      <CFSET crlf = "~">
      <CFLOOP INDEX="orec" LIST="#datavar#" DELIMITERS="#crlf#">
            <cfif len(trim(orec)) >
                  <cfquery name="getjobnumberInfo" datasource="test" dbtype="test">
                        select max(jid) as NaxNum from Jobs
                  </cfquery>
                 
                  <CFSCRIPT>
                        Jobnumber = ListGetAt(orec, 1,"|");
                        Jobtitle = ListGetAt(orec, 2,"|");
                        Location = ListGetAt(orec, 3,"|");
                        State = ListGetAt(orec, 4,"|");
                        Company = ListGetAt(orec, 5,"|");
                        Jobfield2 = ListGetAt(orec, 6,"|");
                        Website = ListGetAt(orec, 7,"|");
                        Joblisting = ListGetAt(orec, 8,"|");
                  </CFSCRIPT>
                 
                  <cfparam name="County" default="Providence">
                  <cfparam name="Experience" default="">
                  <cfparam name="Referred" default="">
                  <cfparam name="Single" default="J">
                  <cfparam name="CBIA" default="N">
                  <cfparam name="exp_date" default="">
                  <cfparam name="eresumes" default="N">
                  <cfparam name="send_resumes" default="0">
                  <cfset dateentered = CreateODBCDate(dateadd('d',-1,now()))>
                  <cfset timeentered = CreateODBCtime(now())>
                  <cfset Moddate = CreateODBCDate(dateadd('d',-1,now()))>
                  <cfset Modtime = CreateODBCtime(now())>
                  <cfset exp_date = CreateODBCdate(dateadd("YYYY",5,now()))>
                  <cfset Temp = Replace(getjobnumberInfo.NaxNum, 'J', '', 'ONE') + 3000 >
                  <cfset jobnumber = "J#Temp#" >
                  <cfset login = "login">
                  <cfset pw = "ocjobs">
                  <cfset topusa = 1>
                 
                  <CFQUERY DATASOURCE="test" dbtype="test">
                  INSERT INTO JOBS (Company,jobnumber,City,State,location,JobTitle,topurl,JobField2,County,login,pw,single,dateentered,timeentered,Moddate,Modtime,eResumes,send_resumes,exp_date,topusa)
                  VALUES ('#Company#','#jobnumber#','#location#','#state#','#location#','#JobTitle#','#website#','#jobfield2#','#County#','#login#','#pw#','#single#',#dateentered#,#timeentered#,#Moddate#,#Modtime#,'#eResumes#','#send_resumes#',#exp_date#,'1')
                  </CFQUERY>
            </cfif>
      </CFLOOP>
</cfif>
0
 
LVL 12

Expert Comment

by:mkishline
ID: 18810374
Two quick points:
1) You need to make sure that you changed the submit button on your form to <input type="submit" name="submit" value="Insert Jobs" />
2) The records won't be inserted until you click the "Insert Jobs" button

If you're still running into trouble let me know and we'll dig up another solution
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18810387
<td><input type="submit" name="submit" value="InsertJobs">
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18810456
Im not opposed to creating a second file, but im not successful in getting it to execute correctly.  If I take all of the form data out with the submit button and I navigate to the page it will insert each record once.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:ostashenp
ID: 18810507
Alright scratch my last couple posts I GOT IT.

I have a new problem though and I really hope i can get a QUICK answer.

In my text file im getting my data from there is a field called state:

the State names are spelled out.

And based off of the state name Certain Counties have to be added to the table for each job.

Each state has about 10 Counties and all 10 counties will be inserted for each job depending on the state.

I can output on the screen with <cfoutput> the correct counties but I cant add a comma list to that field in each row.

Can someone help my code is as above  ^
0
 
LVL 12

Expert Comment

by:mkishline
ID: 18810518
If you use the approach with two files, you won't need the <cfif isDefined>. You can just do something like this:

<!--- updateform.cfm --->
<html>
   <head><title></title></head>
<body>
<form action="process.cfm" method="post">
  <input type="submit" name="submit" value="InsertJobs" />
</form>
</body>
</html>

<!--- process.cfm --->
<cfset file ="test.txt">
<cfset path = "D:\inetpub\test.txt">
<cfoutput>#Path#</cfoutput><br />

<cfif FileExists("#path#") is "Yes">
    File Is There: <br />
<cfelse>
   no file
</cfif>  

<CFFILE ACTION="READ" FILE="#Path#" VARIABLE="datavar">

<CFSET crlf = "~">
<CFLOOP INDEX="orec" LIST="#datavar#" DELIMITERS="#crlf#">
   <cfif len(trim(orec)) >
      <cfquery name="getjobnumberInfo" datasource="test" dbtype="test">
          select max(jid) as NaxNum from Jobs
      </cfquery>
                 
      <CFSCRIPT>
         Jobnumber = ListGetAt(orec, 1,"|");
         Jobtitle = ListGetAt(orec, 2,"|");
         Location = ListGetAt(orec, 3,"|");
         State = ListGetAt(orec, 4,"|");
         Company = ListGetAt(orec, 5,"|");
         Jobfield2 = ListGetAt(orec, 6,"|");
         Website = ListGetAt(orec, 7,"|");
         Joblisting = ListGetAt(orec, 8,"|");
      </CFSCRIPT>
                 
      <cfparam name="County" default="Providence">
      <cfparam name="Experience" default="">
      <cfparam name="Referred" default="">
      <cfparam name="Single" default="J">
      <cfparam name="CBIA" default="N">
      <cfparam name="exp_date" default="">
      <cfparam name="eresumes" default="N">
      <cfparam name="send_resumes" default="0">
      <cfset dateentered = CreateODBCDate(dateadd('d',-1,now()))>
      <cfset timeentered = CreateODBCtime(now())>
      <cfset Moddate = CreateODBCDate(dateadd('d',-1,now()))>
      <cfset Modtime = CreateODBCtime(now())>
      <cfset exp_date = CreateODBCdate(dateadd("YYYY",5,now()))>
      <cfset Temp = Replace(getjobnumberInfo.NaxNum, 'J', '', 'ONE') + 3000 >
      <cfset jobnumber = "J#Temp#" >
      <cfset login = "login">
      <cfset pw = "ocjobs">
      <cfset topusa = 1>
                 
      <CFQUERY DATASOURCE="test" dbtype="test">
         INSERT INTO JOBS (Company,jobnumber,City,State,location,JobTitle,topurl,JobField2,County,login,pw,single,dateentered,timeentered,Moddate,Modtime,eResumes,send_resumes,exp_date,topusa)
         VALUES ('#Company#','#jobnumber#','#location#','#state#','#location#','#JobTitle#','#website#','#jobfield2#','#County#','#login#','#pw#','#single#',#dateentered#,#timeentered#,#Moddate#,#Modtime#,'#eResumes#','#send_resumes#',#exp_date#,'1')
      </CFQUERY>
   </cfif>
</CFLOOP>
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18810596
I GOT IT.

BUT would you be so kind as to help me with this last issue ... ?

I have a new problem though and I really hope i can get a QUICK answer.

In my text file im getting my data is coming from there is  a field called state:

the State names are spelled out.

And based off of the state name Certain Counties have to be added to the table for each job entry.

Each state has about 10 Counties and all 10 counties will be inserted for each job depending on the state. (I know this might not be feasable but its a temporary solution i needed done weeks ago)

I can output on the screen with <cfoutput> the correct counties output based on the state input but I cant add a comma list to the state field on the jobs table.  In my code above I use a default value so I could get everything else to work.

This is the last problem

Help !

Can someone help my code is as above  ^
0
 
LVL 12

Expert Comment

by:mkishline
ID: 18810698
I don't see where you're getting the county based on the state in the code you have provided above. It looks like the county is just always being set to "Providence". If you're doing a query based on the state to get the counties you can generate a comma-delimited list from that using #QuotedValueList(queryname.counties)# if that doesn't help, please explain how you're getting the counties from the state.
0
 
LVL 2

Author Comment

by:ostashenp
ID: 18810810
Whoops sorry about that:  I will try your suggestion, I forgot I took that part out:

This is what I am doing:

<cfif (state) is "District of Columbia">
<cfset state = "Maryland">
</cfif>
<CFQUERY name="getCounties" DATASOURCE="test" dbtype="test">
Select Counties
From Counties
Where Counties like '%#state#%'
</CFQUERY>

The code below just shows that the correct counties are outputting

<cfoutput query="getCounties">
#Counties# <br />
</cfoutput>
0
 
LVL 12

Accepted Solution

by:
mkishline earned 500 total points
ID: 18811517
In your cfquery that is inserting the record, you should be able to just change '#County#' to '#ValueList(getCounties.Counties)#'
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now