My code keeps inserting duplicate records and I can't figure out why.
-
Hello dear experts, I have been having sleepless nights trying to figure why my insert statements keep inserting duplicate records and at this point, I am so stumped that I could really, really use your superior skills to help me out. First some background. I have been with this company for a little over two years and I was handed over an application that keeps breaking, mostly due to poor DB design. I am asked to not discard the current DB design to "work with what I have." The application accepts applications for request for some financial assistance but the applicant must meet certain conditions in order to be considered. If the applicant does not meet conditions, the application is rejected and returned to the applicant with reasons why it is rejected. The applicant is asked for adddress (install address and mailing address are requested.) there two key items to look for. (1), Is Install address (residential address, same as mailing address? (2), Is the application returned (rejected) or not. So, here is what the code I am working with is supposed to do. It searches the db for records and if record exists, then the code checks whether the application is rejected or not and is does install address same as mailing address or not. The logic goes like this: If applicant's address is same as mailing address (if SameAsMailing.Checked) If record exists, then if SameAsMailing.Checked and returnyesorno.SelectedValue=0 Then install address is same as mailing address and it is not a return then update three tables (Addresses, Owner and Application tables). If SameAsChecking is not checked and returnyesorno.SelectedValue=1 then install address is not same as mailing address, therefore, insert the both the install address and mailing address and also process for return by inserting records into Addresses, Owner, Application and return tables. If install address is same as mailing address but not for return, then insert into Addresses, Owner and Application tables. Finally, if install address is not the same as and also not a return then insert into Addresses, Owner and Application tables. No matter the variation that I use, I keep getting about 4 duplicate records inserted into Addresses and Applications table. It is a complicated mess and I need whatever help I can get from you superior talents, please. Here is what makes it so complicated. On the Addresses table, there are three key fields, AutoID (integer) Mailing and Installation are BIT datatypes. If both Install addr
-
Hello dear experts, I have been having sleepless nights trying to figure why my insert statements keep inserting duplicate records and at this point, I am so stumped that I could really, really use your superior skills to help me out. First some background. I have been with this company for a little over two years and I was handed over an application that keeps breaking, mostly due to poor DB design. I am asked to not discard the current DB design to "work with what I have." The application accepts applications for request for some financial assistance but the applicant must meet certain conditions in order to be considered. If the applicant does not meet conditions, the application is rejected and returned to the applicant with reasons why it is rejected. The applicant is asked for adddress (install address and mailing address are requested.) there two key items to look for. (1), Is Install address (residential address, same as mailing address? (2), Is the application returned (rejected) or not. So, here is what the code I am working with is supposed to do. It searches the db for records and if record exists, then the code checks whether the application is rejected or not and is does install address same as mailing address or not. The logic goes like this: If applicant's address is same as mailing address (if SameAsMailing.Checked) If record exists, then if SameAsMailing.Checked and returnyesorno.SelectedValue=0 Then install address is same as mailing address and it is not a return then update three tables (Addresses, Owner and Application tables). If SameAsChecking is not checked and returnyesorno.SelectedValue=1 then install address is not same as mailing address, therefore, insert the both the install address and mailing address and also process for return by inserting records into Addresses, Owner, Application and return tables. If install address is same as mailing address but not for return, then insert into Addresses, Owner and Application tables. Finally, if install address is not the same as and also not a return then insert into Addresses, Owner and Application tables. No matter the variation that I use, I keep getting about 4 duplicate records inserted into Addresses and Applications table. It is a complicated mess and I need whatever help I can get from you superior talents, please. Here is what makes it so complicated. On the Addresses table, there are three key fields, AutoID (integer) Mailing and Installation are BIT datatypes. If both Install addr
Out of interest, I took a look at your code. Honestly, that's going to be almost impossible for someone else to solve in its current form. Some of the things you should look at, to make it more readable.
- Actually use SQL Parameters properly. Right now, you're creating sql parameters, but you are injecting the values directly into your SQL statements - this means you are open for SQL Injection attacks
- Break the logic up into smaller chunks - that code is far too long to understand
- Close your connections when you're finished with them. You are going to hit issues very quickly if you don't.
- You're issuing insert statements that are using a value of -1 inserted records to determine whether or not a record is a duplicate. Rather than doing this, you should actually use real duplicate checks. You should use something like
IF NOT EXISTS (SELECT ....) INSERT INTO ...., but be aware that it is technically possible to still get duplicates if you encounter race conditions.
-
Out of interest, I took a look at your code. Honestly, that's going to be almost impossible for someone else to solve in its current form. Some of the things you should look at, to make it more readable.
- Actually use SQL Parameters properly. Right now, you're creating sql parameters, but you are injecting the values directly into your SQL statements - this means you are open for SQL Injection attacks
- Break the logic up into smaller chunks - that code is far too long to understand
- Close your connections when you're finished with them. You are going to hit issues very quickly if you don't.
- You're issuing insert statements that are using a value of -1 inserted records to determine whether or not a record is a duplicate. Rather than doing this, you should actually use real duplicate checks. You should use something like
IF NOT EXISTS (SELECT ....) INSERT INTO ...., but be aware that it is technically possible to still get duplicates if you encounter race conditions.
Thank you very much for the pointers Pete, much appreciated. For the first point you raised about sql injection attack, yes I forgot to explain that I did those intentionally so I could see what values are being passed to them when I try debugging the old fashioned way - using response.write(). I intent to use the sql parameters once I am able to work out all my issues with the code. There are three questions at play here and I believe this is where I am having my issues. 1), Does this application already exist on our DB? If yes, is it to be returned (rejected). 2), Is this application we received to be returned and is the install address same as mailing address? 3), Is this application to be returned and is install address and mailing address different from each other. If the answer to #1 is yes and no, then the user just updates the records If the answer to #1 is yes and yes, then the user updates the records and also process it for return by inserting it into Returns table. If the answer to #1 is no (record does not exist for this application). So, it is a new application but needs to be returned, then it is added to the all the tables I mentioned above as well as return table. If the application is new and not a return, just add them to the three tables mentioned above. We also have to process the application based on whether residential address is same as mailing address. Unfortunately, if I was designing the DB, I won't be having this issue now because I would have two tables, one for residential address and the other for mailing address but unfortunately, here we are. Very depressing to have to deal with this.
-
Hello dear experts, I have been having sleepless nights trying to figure why my insert statements keep inserting duplicate records and at this point, I am so stumped that I could really, really use your superior skills to help me out. First some background. I have been with this company for a little over two years and I was handed over an application that keeps breaking, mostly due to poor DB design. I am asked to not discard the current DB design to "work with what I have." The application accepts applications for request for some financial assistance but the applicant must meet certain conditions in order to be considered. If the applicant does not meet conditions, the application is rejected and returned to the applicant with reasons why it is rejected. The applicant is asked for adddress (install address and mailing address are requested.) there two key items to look for. (1), Is Install address (residential address, same as mailing address? (2), Is the application returned (rejected) or not. So, here is what the code I am working with is supposed to do. It searches the db for records and if record exists, then the code checks whether the application is rejected or not and is does install address same as mailing address or not. The logic goes like this: If applicant's address is same as mailing address (if SameAsMailing.Checked) If record exists, then if SameAsMailing.Checked and returnyesorno.SelectedValue=0 Then install address is same as mailing address and it is not a return then update three tables (Addresses, Owner and Application tables). If SameAsChecking is not checked and returnyesorno.SelectedValue=1 then install address is not same as mailing address, therefore, insert the both the install address and mailing address and also process for return by inserting records into Addresses, Owner, Application and return tables. If install address is same as mailing address but not for return, then insert into Addresses, Owner and Application tables. Finally, if install address is not the same as and also not a return then insert into Addresses, Owner and Application tables. No matter the variation that I use, I keep getting about 4 duplicate records inserted into Addresses and Applications table. It is a complicated mess and I need whatever help I can get from you superior talents, please. Here is what makes it so complicated. On the Addresses table, there are three key fields, AutoID (integer) Mailing and Installation are BIT datatypes. If both Install addr
If the database design is wrong, you don't "live with it"; you change it. Most "DBA's" have no practical application experience and design "from a book"; where performance and ease of use takes a back seat to 4th normal form.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
If the database design is wrong, you don't "live with it"; you change it. Most "DBA's" have no practical application experience and design "from a book"; where performance and ease of use takes a back seat to 4th normal form.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
I completely agree with you Gerry. The reality is we did make some changes to the DB but is sticky about this is that users are insistent on having the ability to search for and retrieve existing data and the change I need to make will make that nearly imposssible. So, yea, I have to live with this.
-
I completely agree with you Gerry. The reality is we did make some changes to the DB but is sticky about this is that users are insistent on having the ability to search for and retrieve existing data and the change I need to make will make that nearly imposssible. So, yea, I have to live with this.
Change can be introduced in different ways. You can extend; once the extension works; you cut away the part(s) the extension replaces. If you need to "stop" the system to make a change, it's the wrong kind of change. They didn't remove the Frank Slide; they built a new road around it.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
Hello dear experts, I have been having sleepless nights trying to figure why my insert statements keep inserting duplicate records and at this point, I am so stumped that I could really, really use your superior skills to help me out. First some background. I have been with this company for a little over two years and I was handed over an application that keeps breaking, mostly due to poor DB design. I am asked to not discard the current DB design to "work with what I have." The application accepts applications for request for some financial assistance but the applicant must meet certain conditions in order to be considered. If the applicant does not meet conditions, the application is rejected and returned to the applicant with reasons why it is rejected. The applicant is asked for adddress (install address and mailing address are requested.) there two key items to look for. (1), Is Install address (residential address, same as mailing address? (2), Is the application returned (rejected) or not. So, here is what the code I am working with is supposed to do. It searches the db for records and if record exists, then the code checks whether the application is rejected or not and is does install address same as mailing address or not. The logic goes like this: If applicant's address is same as mailing address (if SameAsMailing.Checked) If record exists, then if SameAsMailing.Checked and returnyesorno.SelectedValue=0 Then install address is same as mailing address and it is not a return then update three tables (Addresses, Owner and Application tables). If SameAsChecking is not checked and returnyesorno.SelectedValue=1 then install address is not same as mailing address, therefore, insert the both the install address and mailing address and also process for return by inserting records into Addresses, Owner, Application and return tables. If install address is same as mailing address but not for return, then insert into Addresses, Owner and Application tables. Finally, if install address is not the same as and also not a return then insert into Addresses, Owner and Application tables. No matter the variation that I use, I keep getting about 4 duplicate records inserted into Addresses and Applications table. It is a complicated mess and I need whatever help I can get from you superior talents, please. Here is what makes it so complicated. On the Addresses table, there are three key fields, AutoID (integer) Mailing and Installation are BIT datatypes. If both Install addr
Ihechi Alozie wrote:
I have delayed giving management demo and they have insisted that I show something by wednesday March 15th.
Show them this message.
Ihechi Alozie wrote:
I am asked to not discard the current DB design to "work with what I have."
And how, does management know that such is better than adapting the damn thing? What programming knowledge do they bring to the table to say whether or not that is a good idea or even viable? Would you hire a damned plumber and tell him not to use pipes? Do you tell the surgeon how to do his job? You hired a programmer for his knowledge. If yours is superior, you don't need him. The damned db IS the problem! If you keep that, you insist on keeping the core problem. Tell the programmer to normalize the DB design first. Then migrate the DB overnight and move on. If your toilet-selling company is listed on the stock exchange, then I will be betting on you loosing money. If I need to judge you as a company, just imagine the sound of a toilet flushing. Let the dev do the dev thing, and you keep managing the stuff that needs managing. I'm not joking; you handicapped a dev, and set him up for failure. Now, he needs to "show a demo" of something that doesn't work because you arbitrarily decided so. Normalize the DB. Adapt your app to work with it. Then migrate all data. Work from there or accept that the company will be "bits". I would help your dev to do so; on the one condition that you stop interfering with his work. Trust in the employee.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Ihechi Alozie wrote:
I have delayed giving management demo and they have insisted that I show something by wednesday March 15th.
Show them this message.
Ihechi Alozie wrote:
I am asked to not discard the current DB design to "work with what I have."
And how, does management know that such is better than adapting the damn thing? What programming knowledge do they bring to the table to say whether or not that is a good idea or even viable? Would you hire a damned plumber and tell him not to use pipes? Do you tell the surgeon how to do his job? You hired a programmer for his knowledge. If yours is superior, you don't need him. The damned db IS the problem! If you keep that, you insist on keeping the core problem. Tell the programmer to normalize the DB design first. Then migrate the DB overnight and move on. If your toilet-selling company is listed on the stock exchange, then I will be betting on you loosing money. If I need to judge you as a company, just imagine the sound of a toilet flushing. Let the dev do the dev thing, and you keep managing the stuff that needs managing. I'm not joking; you handicapped a dev, and set him up for failure. Now, he needs to "show a demo" of something that doesn't work because you arbitrarily decided so. Normalize the DB. Adapt your app to work with it. Then migrate all data. Work from there or accept that the company will be "bits". I would help your dev to do so; on the one condition that you stop interfering with his work. Trust in the employee.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
You guys are on point. Thank you guys for your useful inputs. I will keep tearing away at this, slowly but surely.
-
You guys are on point. Thank you guys for your useful inputs. I will keep tearing away at this, slowly but surely.
-
Hello dear experts, I have been having sleepless nights trying to figure why my insert statements keep inserting duplicate records and at this point, I am so stumped that I could really, really use your superior skills to help me out. First some background. I have been with this company for a little over two years and I was handed over an application that keeps breaking, mostly due to poor DB design. I am asked to not discard the current DB design to "work with what I have." The application accepts applications for request for some financial assistance but the applicant must meet certain conditions in order to be considered. If the applicant does not meet conditions, the application is rejected and returned to the applicant with reasons why it is rejected. The applicant is asked for adddress (install address and mailing address are requested.) there two key items to look for. (1), Is Install address (residential address, same as mailing address? (2), Is the application returned (rejected) or not. So, here is what the code I am working with is supposed to do. It searches the db for records and if record exists, then the code checks whether the application is rejected or not and is does install address same as mailing address or not. The logic goes like this: If applicant's address is same as mailing address (if SameAsMailing.Checked) If record exists, then if SameAsMailing.Checked and returnyesorno.SelectedValue=0 Then install address is same as mailing address and it is not a return then update three tables (Addresses, Owner and Application tables). If SameAsChecking is not checked and returnyesorno.SelectedValue=1 then install address is not same as mailing address, therefore, insert the both the install address and mailing address and also process for return by inserting records into Addresses, Owner, Application and return tables. If install address is same as mailing address but not for return, then insert into Addresses, Owner and Application tables. Finally, if install address is not the same as and also not a return then insert into Addresses, Owner and Application tables. No matter the variation that I use, I keep getting about 4 duplicate records inserted into Addresses and Applications table. It is a complicated mess and I need whatever help I can get from you superior talents, please. Here is what makes it so complicated. On the Addresses table, there are three key fields, AutoID (integer) Mailing and Installation are BIT datatypes. If both Install addr