I need to prevent duplicate rows in cfgrid (flash) and DB table

lets see if i understand this, one memeber may have 1-n zipcodes in a table, they can pick the zipcodes from a list, is it a multiple list or can the only pick one at a time? then the zip code is inserted into a db table?

correct?

Hi Jester. you're pretty popular around here. You have helped me before.
When a member enrolls (http://www.electriciansnet.com/city.cfm) they query zipCodeDatabase which populates a cfgrid on contractor.cfm based on their city and state. This is not the cfgrid we're dealing with right now but duplicates are supposed to be prevented at insert time. There is a member-admin area where the member can edit their profile, logo or service area using their memberID as the argument for everything.
                                                            (All tables are still full of test data)

Okay, now I noticed that I could successfully enter say 13131 and insert multiple times from within the admin area.
On the user side there is 1 text box on the home page where they enter their zip code to find an electrician in their area. If they hit, their form is submitted to that member's (up to 3) cell phone.
What I'm concerned about is that in different environments, if the user uses say 13134, the member will be displayed/ mailed  multiple times. I actually have 13134 in the DB twice and I just went to the home page with IE and queried using 13134 and only 1 result was shown. Then I went to Firefox and the record count shows 2 and the member is displayed twice in the flash movie confirming my concerns.

>>went to the home page with IE and queried using 13134 and only 1 result was shown. Then I went to Firefox and the record count shows 2 and the member is displayed twice in the flash movie confirming my concerns.

thats strange, as they should both be the same, the cf output should not change in the browser
do you cf code that detects the browser and changes the code according to that?

>>except the possibility exists that the same zip code could be entered more than once under the same memberID. I must be able to prevent that
have you considered select distinct to get only unique values?
your suggestion to check if it already exists would also work.
the two combined should eliminate all duplicates in both input and output

do you cf code that detects the browser and changes the code according to that?  No but expert siva siva had suggested that with another issue. You see Jester, I have had many, manycross-browser issues recently. I prefer FireFox and being that this is my 1st site and the fact that I'm teach myself along the way, I neglected to watch IE. Now I'm paying for it! I think there was a datatype issue with distinct but I have since changed all my datatype-text to datyatype-varchar so I will check into that when I get back.

-your suggestion to check if it already exists would also work
Would this require putting the zips into an array? If so, can you show me how to do that? Also, I believe this would prevent the member from inserting the same zip twice in one insert but will not prevent from inserting a zip that is already in DB. I'm not sure. Its quite possible that I have no idea what I'm talking about with that last sentence.

yes coding for different browsers is an issue, but it is mainly for js and layout, server code should not be an issue
getting the distinct values by userid

SELECT DISTINCT USERID, ZIPCODE
FROM TABLE
WHERE CONDITION=TRUE

the from and where are pseudocode, replace the table name and conditions as required
inserting...  a little more difficult
INSERT INTO TABLE (USERID, ZIPCODE)
VALUES (form.userid, form.zipcode)
WHERE TABLE.ZIPCODE NOT IN (SELECT ZIPCODE FROM TABLE WHERE TABLE.USERID=form.userid)

this could be quite intensive if the zipcode list already in the system is huge, in that case you might want to make it two queries, run teh subquery first and then save it in a value list so teh sub query doenst have to run as much
 get user zipcodes
SELECT ZIPCODE FROM TABLE WHERE TABLE.USERID=form.userid

save to alist
<cfset lstZip=valuelist(query.ZIPCODE)>

use teh list in the insert query
INSERT INTO TABLE (USERID, ZIPCODE)
VALUES (form.userid, form.zipcode)
WHERE TABLE.ZIPCODE NOT IN (<cfqueryparam value="#lstZip#" list="yes">)

Hi Jester 48. I have been trying to work with the code you provided since yesterday evening but I cannot seem to make it past the WHERE. Maybe its because I'm inside a loop. I have the cfqueryparam removed so that we can see whts hapening with the code but there is error either way.
Here is the error:

 [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.
 
The error occurred in C:\Inetpub\wwwroot\electriciansnet.com\Admin_member\handle_grid2.cfm: line 76

74 :           <cfqueryparam value="#Form.grid2.zipCode[Counter]#"
75 :             CFSQLType="cf_sql_varchar" >)
76 :                   WHERE #Form.grid2.zipCode[Counter]#NOT IN#lstZip#
77 :       </cfquery><!---DISTINCT pt 3--->
78 :

SQL          INSERT into memberzip (memberID, zipCode) VALUES ( (param 1) , (param 2) ) WHERE 13134NOT IN13131,13132,13133

Here is the complete code:

<cfif isdefined("Form.editGrid")>


<!---DISTINCT pt 1--->
<cfquery name="noDupeZips" datasource="#request.dsn#">
select zipCode from memberzip where memberID = #session.admin#
</cfquery>


<!---DISTINCT pt 2--->
<cfset lstZip=valuelist(noDupeZips.zipCode)>
<cfif isdefined("Form.grid2.rowstatus.action")>

  <cfloop index = "Counter" from = "1" to =
    #arraylen(Form.grid2.rowstatus.action)#>


    <cfoutput>
      The row action for #Counter# is:
      #Form.grid2.rowstatus.action[Counter]#
      <br>
    </cfoutput>

    <cfif Form.grid2.rowstatus.action[counter] is "D">
 
      <cfquery name="DeleteExistingMember"
        datasource="#request.dsn#">
        DELETE FROM memberzip
        WHERE zipID=
          <cfqueryparam
            value="#Form.grid2.original.zipID[Counter]#"
            CFSQLType="CF_SQL_INTEGER" >
      </cfquery>

    <cfelseif Form.grid2.rowstatus.action[counter] is "U">

      <cfquery name="UpdateExistingMember"
        datasource="#request.dsn#">
        UPDATE memberzip
        SET
          memberID=
            <cfqueryparam
              value="#Form.grid2.memberID[Counter]#"
              CFSQLType="CF_SQL_VARCHAR" >,
          zipCode=
            <cfqueryparam
              value="#Form.grid2.zipCode[Counter]#"
              CFSQLType="cf_sql_varchar" >
        WHERE zipID=
        <cfqueryparam value="#Form.grid2.original.zipID[Counter]#"
            CFSQLType="CF_SQL_INTEGER">
      </cfquery>
                  
    <cfelseif Form.grid2.rowstatus.action[counter] is "I" and form.grid2.zipCode[counter] neq form.grid2.original.zipCode[counter]>
                  
      <cfquery name="InsertNewMember"
        datasource="#request.dsn#">
        INSERT into memberzip (memberID, zipCode)
        VALUES
          (<cfqueryparam
            value="#Form.grid2.memberID[Counter]#"
            CFSQLType="cf_sql_integer" >,
          <cfqueryparam value="#Form.grid2.zipCode[Counter]#"
            CFSQLType="cf_sql_varchar" >)
                  WHERE #Form.grid2.zipCode[Counter]#NOT IN#lstZip#
      </cfquery><!---DISTINCT pt 3--->

    </cfif>
  </cfloop>
</cfif>
</cfif>
<!---<cflocation url="grid2.cfm">--->
<p>Click<a href="grid2.cfm"> here </a>to display the updated grid</p>
<cfdump var="#lstZip#">

BTW, I also tried      WHERE memberzip.zipCode NOT IN (<cfqueryparam value="#lstZip#" list="yes">)
first and many other combinations never getting past the WHERE clause. At first I thought the server was having a problem with the NOT IN clause because I had tried it before with no success so  just for testing I tried <> , = and some others  but still the WHERE error.

Definitely in the application of WHERE. This throws the same error when trying to insert 13134:

 <cfquery name="InsertNewZip"
        datasource="#request.dsn#">
        INSERT into memberzip (memberID, zipCode)
        VALUES
          (<cfqueryparam
            value="#Form.grid2.memberID[Counter]#"
            CFSQLType="cf_sql_integer" >,
          <cfqueryparam value="#Form.grid2.zipCode[Counter]#"
            CFSQLType="cf_sql_varchar" > )
                  WHERE 13134 = 13134
      </cfquery>
Take the where clause away and no errors.