Solved

Insert Statement with Coldfusion based on a previous query

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

867 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

16 Experts available now in Live!

Get 1:1 Help Now