Solved

Insert Statement with Coldfusion based on a previous query

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MDT 2013 Update 2 8 57
coldfusion cfloop help 6 31
VBS to download an image from our website 4 15
Coldusion - DATA insert syntax problem 12 22
Developer tools in browsers have been around for a while, yet they are still heavily underused by developers. Developers still fix html or CSS then refresh page to see effect, or they put alert or debugger in JavaScript and then try again and again …
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…
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…
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.

813 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

17 Experts available now in Live!

Get 1:1 Help Now