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

Prevent Duplicate Records on "Insert" .NET 2.0 Detailsview web application

Hi Expert:
I have a .NET 2.0 C# guestbook like web application that uses a SQL Microsoft Server database and
trying  to prevent duplicate records from being inserted with this InsertCommand in my C# .NET code

But I get an error indicating that there is something wrong near the WHERE?

InsertCommand="INSERT INTO Table_1 (First_Name, Last_Name,@Pay_Number)  
VALUES (@First_Name,@Last_Name,@Pay_Number)
WHERE NOT EXIST (SELECT Last_Name FROM Table_1 T1 WHERE T1.Pay_Number= Pay_Number AND T1.Person_ID = Person_Id)"

BackGround:

User enters his or her's First_Name, Last_Name  and Pay_Number into 3 textboxes
 This is how I note them in the Insert statement  above---> @First_Name,@Last_Name,@Pay_Number

I have 1 single table that looks something like this:

Table 1
  (primary key)  person_Id       int        
                     First_Name      varchar(50)
                     Last_Name       varchar(50)
                     Pay_Number     varchar(50)                      

The  primary  key "person_Id" is auto generated by the sql server and not returned to my C# .NET application or displayed  or  used anywhere that I know.

Help me fix the problem  in my InsertCommand= statement above.

Thanks,
0
RSSIAdmin
Asked:
RSSIAdmin
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

InsertCommand="INSERT INTO Table_1 (First_Name, Last_Name,@Pay_Number)  
SELECT @First_Name,@Last_Name,@Pay_Number
WHERE NOT EXIST (SELECT Last_Name FROM Table_1 T1 WHERE T1.Pay_Number= Pay_Number AND T1.Person_ID = Person_Id)"
0
 
anyoneisCommented:
If Person_Id is autogenerated, your NOT EXIST clause will always be true - since Person_Id will never repeat.

But, more importantly, there is no WHERE clause in an INSERT INTO statement.

Try something like this:

INSERT INTO Table_1 (First_Name, Last_Name, Pay_Number)
SELECT @First_Name, @Last_Name, @Pay_Number
WHERE NOT EXISTS (
      SELECT Last_Name FROM Table_1 T1 WHERE T1.Pay_Number= @Pay_Number
)

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think I need to post a minor correct of my suggestion:

InsertCommand="INSERT INTO Table_1 (First_Name, Last_Name,@Pay_Number)  
SELECT @First_Name,@Last_Name,@Pay_Number
WHERE NOT EXIST (SELECT Last_Name FROM Table_1 T1 WHERE T1.Pay_Number= @Pay_Number AND T1.Person_ID = @Person_Id)"

however, I somehow have to agree with anyoneis
0
 
anyoneisCommented:
Oops! I misread angel's suggestion - she had already addressed one of my points.

David
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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