Solved

How to detect duplicates in my SQL Server database and not create another record.

Posted on 2011-03-01
9
383 Views
Last Modified: 2012-05-11
Hello Experts, if this can be done, then this is the place to ask this question. Okay here's my situation. I have created an app that captures all the pertinent information that we need to process our clients licensing needs. By the way, we're an insurance licensing company. So, here's where things are messing up on me. For whatever reason, we are getting duplicates in our database. Now, we have some clients that call in and tell us that a certain page is not acting right for whatever reason. I can't for the life of me figure out what is going on with my app. I have filled this thing out from anywhere to a dial-up connection to a wireless connection and it ALWAYS works good for me. And from different locations, still, no problems for me. But, so they say, it's messing up on them. I don't know if there are memory leaks, I don't know, it's driving me crazy, anyway, so this is how I built the application. It's basically several "pages" and so at the bottom of each page there is a button that says "Next Page" and when the clients click that button it dumps their information from that page into a database table built for that page only. Now, I was wondering, is there a way to, I guess, like poll the database if the app messes up on them to see of their data is already there? And then if their data does exist, to NOT do a database insert a second time, but just go ahead and move them to the next page? It's really filling up my little database that I built for this, and I keep having to fire up SQL Enterprise Manager and delete the duplicates.
I already added jQuery BlockUI with a neat little "Processing data...Please wait." message to kind of slow them down a little so they won't get so impatient while waiting to move to the next page. I don't know what else to do, I have tested this thing to the hilt and I like I said, I cannot figure out what is going on. I just know I'm tired of duplicates. Also, just for the record, I'm not using any kind of Ajax or ASP.Net Ajax or anything, don't know if that matters or not.
If any of you have had this problem and came up with a solution, I would really, really appreciate it if you could pitch me a solution.

RFR

Oh one more thing, I am creating a unique userid on the first page and passing it encrypted through the query string to the next page, and then the next page, and so on like that, and it gets dumped to the database as well.
0
Comment
Question by:RunForrestRun
9 Comments
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 166 total points
ID: 35011820


Why are you storing data for each page into the database separately.

Why don't you store them in a session variable and after all the data is gathered on the last page, store everything at once. You can create a serializable object for that.

0
 
LVL 9

Accepted Solution

by:
sshah254 earned 167 total points
ID: 35011884
When you are inserting data into the table, it seems like you are not checking if the data already exists.

Insert the data into a temp table - let's sa temp1

Now insert the data only if it does not exist ...

If your table is table1, then

Insert into table1
select a.* from temp1 a left join table1 b on a.something = b.something
where b.something is null ...

This way, you are sure to not have duplicates.

Ss
0
 

Author Comment

by:RunForrestRun
ID: 35012026
ewangoya, this used to be one LONG application and management wanted it broken out into "modules" so each page would have it's own database table. And I had sort of thought something about session variables but on some pages there's SO MUCH data that I just didn't want to do it that way.
sshah254, thanks for that, I guess I didn't think about checking first if the data existed. Some things just slip by me sometimes. ewangoya, sshah254, thanks for your replys, you both have really got me to thinking down some different avenues.
0
 
LVL 76

Expert Comment

by:arnold
ID: 35012244
Adding to the others points with your existing setup.
I.e. you would use the data collection as a "temporary storage"
One the application is complete where they fill all the pages either in a single sitting or over time. (this is where you would check if the person had previously started the process" caution should be used to make sure the person filling up is really the same person as he one who initially began the process since you would not want to reveal the information to someone else.

Once the process is complete, you would take the data from these per page "temporary tables" and arrange the data more sensibly within the permanent database structure.

Alternatively, your initial page will be for the user to create the minimal account for access.
The user once logged in will have the option to fill the additional information at will which you will then use to populate the permanent table and would not need the "temporary".
this approach will shorten the amount of time the user has to spend in one sitting.
You could limit the user's ability/access until the application process is complete.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:RunForrestRun
ID: 35012538
Hmm, ok thanks arnold, something to think about, I really appreciate the great ideas, keep em coming.
0
 
LVL 76

Expert Comment

by:arnold
ID: 35012737
If you are planning on rewriting/rearranging the app, you may wish to rearchitect the database.
The duplicate detection/elimination can be done on either on both ends.
Setting up a unique index/primary keys within database tables will prevent the insertion of a duplicate record.
Checking for non existance of a record within your frontend application is the other option.

The design of the database could help.
There are draw backs the more tables the more queries and at times the queries can get complex.
If there is a single table with all the information in it, redundant information will be present i.e. city, state, country, etc. will increase the amount of "useles" data in the database.

You may want to use the ERD (database diagram in SSMS) to arrange the database
i.e. countries, states, cities, customer, etc. customer/client/insurer
0
 

Author Comment

by:RunForrestRun
ID: 35017995
arnold, I would really like to do it front-end if possible, being that the application is already deployed; that way I can work on my copy locally in Visual Studio, and I'm not in there messing around in SQL while the clients are trying to fill anything out. If I could figure out a way to do it in my code-behind file (VB.Net), that would be great. That way I can play around with it, and when I'm done and have it working the way I want, deploy it to the server.
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 167 total points
ID: 35019806
It is often better to design the database while taking into consideration the complexity of the number of queries that would need to be generated and their complexity.
You could work on enforcing/eliminating which would that you would have to check whether the user/account is present or depending on what other identifying/unique info you ask, whether such info was already provided because of a typo. etc.

You need to layout the flow of the signup/registration/initial set of entries your firm requires to establish the login and as much identifying info from the user such as:
username, email, password, Name, address, phone, etc.
Then the user has an option to login and has a reminder on the page indicating which forms they have to complete to gain whatever it is they are trying to gain access/license to operate etc.

Based on what I think your setup is, you might be better off considering starting from scratch and see whether that would simplify things for you versus trying to patch the existing app./database.

Hopefully you are extremely knowledgeable with the existing database structure and front end application. If not, you should get very familiar with the existing database structure,record flow, table relationships both explicit and implicit i.e. there is a field in two tables that contains the same information versus one having the info and the other a reference to the info.

I think trying to set up a new flow using an old database structure where you would have to both validate data and maintain data integrity in the existing structure might be a daunting task.
0
 

Author Closing Comment

by:RunForrestRun
ID: 35021883
ewangoya, sshah254, arnold, I want to thank all 3 of you for your answers, you all got me to thinking down some different avenues. Note: I tried to give the same number of points to all 3 of you. Good day.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 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

21 Experts available now in Live!

Get 1:1 Help Now