ColdFusion Insert Query Inserts the Record Twice

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>


LVL 2
ostashenpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mkishlineCommented:
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
SidFishesCommented:
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
ostashenpAuthor Commented:
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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

mkishlineCommented:
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
ostashenpAuthor Commented:
<td><input type="submit" name="submit" value="InsertJobs">
0
ostashenpAuthor Commented:
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
ostashenpAuthor Commented:
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
mkishlineCommented:
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
ostashenpAuthor Commented:
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
mkishlineCommented:
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
ostashenpAuthor Commented:
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
mkishlineCommented:
In your cfquery that is inserting the record, you should be able to just change '#County#' to '#ValueList(getCounties.Counties)#'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.