6 Replies - 6070 Views - Last Post: 13 December 2008 - 01:06 PM Rate Topic: -----

#1 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

how do I insert record in DB ONLY if it does not already exist.how to

Post icon  Posted 10 December 2008 - 04:44 AM

My insert function works, but...
I want it to only add the record if it does not already work.


<cfif isdefined("form.zipcodenum")>
<cfquery name="insertCityStateZip" datasource="DTDatabase">
			INSERT INTO Zips
				(CITY, STATE, ZIPCODENUM)
			 VALUES
				('#form.city#', '#form.state#', '#form.zipcodenum#') 
</cfquery>
		



</cfif>
<cfform>
<table width="700" border="0" cellpadding="0" cellspacing="0" id="formtable">
  <tr>
	<td height="50" colspan="3" id="pissanellos">&nbsp;</td>
  </tr>
  <tr>
	<td width="25%" height="20">&nbsp;</td>
	<td width="25%" height="20">*City:</td>
	<td width="50%" height="20"><cfinput type="text" name="city" required="yes" id="city" tabindex="7" maxlength="20"></td>
  </tr>
  <tr>
	<td width="25%" height="20">&nbsp;</td>
	<td width="25%" height="20">*State:</td>
<td width="50%" height="20"><cfselect name="State" id="State" tabindex="8" required="yes">
					<option value="AK">AK</option>
					<option value="AL">AL</option>
					<option value="AR">AR</option>
					<option value="AZ">AZ</option>
					<option value="CA">CA</option>
					<option value="CO">CO</option>
					<option value="CT">CT</option>
					<option value="DC">DC</option>
					<option value="DE">DE</option>
					<option value="FL">FL</option>
					<option value="GA">GA</option>
					<option value="HI">HI</option>
					<option value="IA">IA</option>
					<option value="ID">ID</option>
					<option value="IL">IL</option>
					<option value="IN">IN</option>
					<option value="KS">KS</option>
					<option value="KY">KY</option>
					<option value="LA">LA</option>
					<option value="MA">MA</option>
					<option value="MD">MD</option>
					<option value="ME">ME</option>
					<option value="MI">MI</option>
					<option value="MN">MN</option>
					<option value="MO">MO</option>
					<option value="MS">MS</option>
					<option value="MT">MT</option>
					<option value="NC">NC</option>
					<option value="ND">ND</option>
					<option value="NE">NE</option>
					<option value="NH">NH</option>
					<option value="NJ">NJ</option>
					<option value="NM">NM</option>
					<option value="NV">NV</option>
					<option value="NY">NY</option>
					<option value="OH" selected="selected">OH</option>
					<option value="OK">OK</option>
					<option value="OR">OR</option>
					<option value="PA">PA</option>
					<option value="PR">PR</option>
					<option value="RI">RI</option>
					<option value="SC">SC</option>
					<option value="SD">SC</option>
					<option value="TN">TN</option>
					<option value="TX">TX</option>
					<option value="UT">UT</option>
					<option value="VA">VA</option>
					<option value="VT">VT</option>
					<option value="WA">WA</option>
					<option value="WI">WI</option>
					<option value="WV">WV</option>
					<option value="WY">WY</option>
	</cfselect></td>
  </tr>
  <tr>
	<td width="25%" height="20">&nbsp;</td>
	<td width="25%" height="20">*Zip:</td>
	<td width="50%" height="20"><cfinput type="text" name="zipcodenum" validateat="onsubmit" validate="zipcode" required="yes" id="zipcodenum" tabindex="9" maxlength="5"></td>
  </tr>
  <tr>
	<td width="25%" height="20" align="right">&nbsp;</td>
	<td width="25%" height="20" align="right"><cfinput type="submit" name="Submit" value="Submit" id="Submit" tabindex="10"></td>
	<td width="50%" height="20"><cfinput type="button" name="cancel" id="Reset" value="Cancel" onclick="history.back()" tabindex="11"></td>
  </tr>
</table>
</cfform>




Is This A Good Question/Topic? 0
  • +

Replies To: how do I insert record in DB ONLY if it does not already exist.how to

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1947
  • View blog
  • Posts: 3,488
  • Joined: 13-January 08

Re: how do I insert record in DB ONLY if it does not already exist.how to

Posted 10 December 2008 - 07:18 AM

Your current query:

<cfquery name="insertCityStateZip" datasource="DTDatabase">
			INSERT INTO Zips
				(CITY, STATE, ZIPCODENUM)
			 VALUES
				('#form.city#', '#form.state#', '#form.zipcodenum#')
</cfquery>



What you need:

<cfquery name="insertCityStateZip" datasource="DTDatabase">
	 IF NOT EXISTS (SELECT *
							 FROM Zips
							 WHERE CITY = '#form.city#'
								   AND STATE = '#form.state#'
								   AND ZIPECODENUM = '#form.zipcodenum#')
	 INSERT INTO Zips (CITY, STATE, ZIPCODENUM)
	 VALUES ('#form.city#', '#form.state#', '#form.zipcodenum#')
</cfquery>



Give that a spin and see if it works. I might need an ELSE statement...but try it like that.
Was This Post Helpful? 1

#3 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: how do I insert record in DB ONLY if it does not already exist.how to

Posted 10 December 2008 - 08:37 AM

Tried this


<cfif isdefined("form.zipcodenum")>
<cfquery name="insertCityStateZip" datasource="DTDatabase">
	 IF NOT EXISTS (SELECT *
							 FROM Zips
							 WHERE CITY = '#form.city#'
								   AND STATE = '#form.state#'
								   AND ZIPECODENUM = '#form.zipcodenum#')
	 INSERT INTO Zips (CITY, STATE, ZIPCODENUM)
	 VALUES ('#form.city#', '#form.state#', '#form.zipcodenum#')
</cfquery>




Got:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Was This Post Helpful? 0
  • +
  • -

#4 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1947
  • View blog
  • Posts: 3,488
  • Joined: 13-January 08

Re: how do I insert record in DB ONLY if it does not already exist.how to

Posted 10 December 2008 - 08:49 AM

You're using Access?

Okay, the same can be done more mechanically. Try this instead:

<!--- Check for the record first --->
<cfquery name="ckDup" datasource="DTDatabase">
	SELECT *
	FROM Zips
	WHERE CITY = '#form.city#'
		AND STATE = '#form.state#'
		AND ZIPECODENUM = '#form.zipcodenum#'
</cfquery>

<!--- If it doesn't exist, add it --->
<cfif chkDup.recordcount EQ 0>
	<cfquery name="insertCityStateZip" datasource="DTDatabase">
		INSERT INTO Zips (CITY, STATE, ZIPCODENUM)
		VALUES ('#form.city#', '#form.state#', '#form.zipcodenum#')
	</cfquery>
</cfif>



That will require 2 queries to do the job...but it may be that Access can't run the type of query I posted in originally. Such does work on SQL2005 though.
Was This Post Helpful? 1

#5 cumupkid  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 63
  • Joined: 22-January 08

Re: how do I insert record in DB ONLY if it does not already exist.how to

Posted 10 December 2008 - 08:57 AM

Yeah it is a preexisting Access database that I am writing this for.

Tried the last post and got
Element CITY is undefined in FORM.

when trying to initally load the page

Thanks for the quick responses
Was This Post Helpful? 0
  • +
  • -

#6 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1947
  • View blog
  • Posts: 3,488
  • Joined: 13-January 08

Re: how do I insert record in DB ONLY if it does not already exist.how to

Posted 10 December 2008 - 09:14 AM

Well, think on it some...

The last code I posted needs to replace that one query you have. You have it inside the cfif checking against zipcodenum.

There are a couple of ways to address your error but the best way is to check for existence of all three form variables (instead of just the zip code) you're using in the chkDup and insertCityStateZip queries.

As a good rule of thumb, never assume you'll have particular variables before you reference them in your code. CFPARAM is your friend many days and when it doesn't do the trick the IsDefined() function will save you.
Was This Post Helpful? 0
  • +
  • -

#7 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: how do I insert record in DB ONLY if it does not already exist.how to

Posted 13 December 2008 - 01:06 PM

View PostCraig328, on 10 Dec, 2008 - 08:14 AM, said:

That will require 2 queries to do the job...but it may be that Access can't run the type of query I posted in originally. Such does work on SQL2005 though.


cumupkid,

Like I suggested on the other thread, this question isn't ColdFusion specific and I really think you would get a better answer asking in an MS Access forum. Like Craig and I, a lot of people use MS SQL or MySQL. So your chances of getting the right answer are better in an MS Access forum.
http://www.dreaminco...h...73631&st=12

Also with database questions, always mention what database you're using in the first post. So people can try and offer the right suggestions for your environment :-)

This post has been edited by sansclue: 13 December 2008 - 01:12 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1