Solved

Insert Statement with Coldfusion based on a previous query

Posted on 2007-03-29
4
214 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
  • 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

A Change in PHP Behavior with Session Write Short Circuit (http://php.net/manual/en/book.session.php#116217) (Winter 2014)** With the release of PHP 5.6 the session handler changed in a way that many think should be considered a bug.  See the note …
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…
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.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

708 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

13 Experts available now in Live!

Get 1:1 Help Now