• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

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

Hello, I have a page in my member-admin area where the member can edit their service area (zip codes). This page contains a cfgrid (format:flash) with the only editable column being zipCode. The action page that this grid subt to uses cfquery method to update, delete or insert records. All of this works ok 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. However, the same zip code must be allowed multiple times within the table because other members may have that zip as well. 1 member can have >100 zips in their area.
Here is what I have tried without success:
  <cfelseif Form.grid2.rowstatus.action[counter] is "I" and form.grid2.zipCode[counter] neq form.grid2.original.zipCode[counter]> Problem here is originals are not read on value 'I'.

I think I need to put the members existing zip codes into some kind of array on the insert page and only insert if NEQ array. Problem is, I have never worked with this type of problem. Thakns, Andy
0
Electriciansnet
Asked:
Electriciansnet
  • 7
  • 3
1 Solution
 
James RodgersWeb Applications DeveloperCommented:
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?


0
 
ElectriciansnetAuthor Commented:
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.
0
 
James RodgersWeb Applications DeveloperCommented:
>>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
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
ElectriciansnetAuthor Commented:
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.
0
 
James RodgersWeb Applications DeveloperCommented:
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">)

0
 
ElectriciansnetAuthor Commented:
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
0
 
ElectriciansnetAuthor Commented:
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#">
0
 
ElectriciansnetAuthor Commented:
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.
0
 
ElectriciansnetAuthor Commented:
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.
0
 
ElectriciansnetAuthor Commented:
From what I've read, I'm pretty sure we're not allowed to use WHERE with VALUES only SELECT.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now