Solved

ColdFusion Insert Query Inserts the Record Twice

Posted on 2007-03-27
12
384 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

777 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