Solved

Insert Statement with Coldfusion based on a previous query

Posted on 2007-03-29
4
224 Views
Last Modified: 2010-04-06
Here is my problem:

I am trying to do an insert query based on information queried in a previous statement.  I am doing a query to see if the client exists, if it does then it should proceed to the next insert, if the client does not exist it should input the information then proceed with the next insert.  Here is my code for it.

<THIS PART QUERIES THE FIELD I WANT TO CHECK>

<cfquery name="getClients" datasource="test" dbtype="test">
      select Company
      from Clients
</cfquery>
<THIS IS SUPPOSED TO CHECK TO SEE IF THERE IS AN ENTRY FOR THE ABOVE FIELD>

<CFIF #ParameterExists(getClients.Company)# is "No">
<cfquery name="insertCompany" datasource="test" dbtype="test">
INSERT INTO Clients
(City,Company,CTDue,CTPackage,CTR,CTRenewed,CTVis,DateCreated,DateMod,ID,Login,OSJ,OSR,Password,Referred,State,TimeCreated,TimeMod,Single,CBIA)
VALUUES('#City#','#Company#','#CTDue#','#CTPackage#','#CTP#','#CTRenewed#','#CTVis#','#DateCreated#','#DateMod#','#Jobnumber#','#Login#','#OSJ#','#OSR#','#Passwaord#','#Referred#','#State#','#TimeCreated#','#TimeMod#','#Single#','#CBIA#')
</cfquery>
</cfif>
 
<THIS PART BELOW WORKS >
               
      <CFQUERY name="insertJobs" DATASOURCE="test" datatype="test">
         INSERT INTO JOBS (CBia,Company,jobnumber,City,State,location,JobTitle,topurl,JobField2,Joblisting,County,login,pw,single,dateentered,timeentered,Moddate,Modtime,eResumes,send_resumes,exp_date,topusa)
         VALUES ('#CBia#','#Company#','#jobnumber#','#location#','#state#','#location#','#JobTitle#','#website#','#jobfield2#','#Joblisting#','#ValueList(getCounties.Counties)#','#login#','#pw#','#single#',#dateentered#,#timeentered#,#Moddate#,#Modtime#,'#eResumes#','#send_resumes#',#exp_date#,'1')
      </CFQUERY>
0
Comment
Question by:ostashenp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Author Comment

by:ostashenp
ID: 18821099
I only want to do that first INSERT query if the #Company# does not exist in that table.
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 18821288


<cfquery name="getClients" datasource="test" dbtype="test">
      select Company
      from Clients

      ADD A WHERE CLAUSE HERE TO FIND THAT CLIENT

</cfquery>

 Now test to see if any records were found...

 <cfif getClients.recordCount eq 0>
    <!---- record does not exist---->

0
 
LVL 2

Author Comment

by:ostashenp
ID: 18822825
I got that but now im getting an error on the data entry:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 3: Incorrect syntax near '2007'.
 
The error occurred in D:\Inetpub\test\get_jobs.cfm: line 89

87 : INSERT INTO Clients
88 : (City,Company,CTDue,CTPackage,CTR,CTRenewed,CTVis,DateCreated,DateMod,ID,Login,OSJ,OSR,Password,Referred,State,TimeCreated,TimeMod,Single,CBIA)
89 : VALUES('#Location#','#Company#','#CTDue#','#CTPackage#','#CTR#','#CTRenewed#','#CTVis#','#dateentered#','#Moddate#','#Jobnumber#','#Login#','#OSJ#','#OSR#','#PW#','#Referred#','#State#','#timeentered#','#Modtime#','#Single#','#CBIA#')
90 : </cfquery>
91 : </cfif>

SQL          INSERT INTO Clients (City,Company,CTDue,CTPackage,CTR,CTRenewed,CTVis,DateCreated,DateMod,ID,Login,OSJ,OSR,Password,Referred,State,TimeCreated,TimeMod,Single,CBIA) VALUES('Houston','Alpha Synopsys Inc','{d '2007-04-29'}','30','N','{d '2007-04-28'}','Y','{d '2007-03-30'}','{d '2007-03-30'}','J16996','login','Y','N','ocjobs','','Texas','{t '05:38:57'}','{t '05:38:57'}','J','N')
DATASOURCE         test
VENDORERRORCODE         170
SQLSTATE         HY000
Resources:

    * Check the ColdFusion documentation to verify that you are using the correct syntax.
    * Search the Knowledge Base to find a solution to your problem.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18822899

 Your date/time values should not be in quotes...

Use

 #ModDate#

or  

 #createODBCdate(ModDate)#

You have them as '#ModDate#'

0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
This video teaches users how to migrate an existing Wordpress website to a new domain.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

623 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