Insert Statement with Coldfusion based on a previous query

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>
LVL 2
ostashenpAsked:
Who is Participating?
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.

ostashenpAuthor Commented:
I only want to do that first INSERT query if the #Company# does not exist in that table.
0
gdemariaCommented:


<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

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
ostashenpAuthor Commented:
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
gdemariaCommented:

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

Use

 #ModDate#

or  

 #createODBCdate(ModDate)#

You have them as '#ModDate#'

0
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 Applications

From novice to tech pro — start learning today.