none
SQL CE How to ignore duplicate insert RRS feed

  • Question

  •   SqlCeCommand command = new SqlCeCommand(@"INSERT INTO fpl_table 
                                                        (FLIGHT_ID, BPN_TIME, BPX_TIME, DAY_NB) 
                                                        VALUES (@FLIGHT_ID, @BPN_TIME, @BPX_TIME, @DAY_NB)
                                                        ON DUBLICATE UPDATE FLIGHT_ID = @FLIGHT_ID, BPN_TIME=@BPN_TIME,BPX_TIME=@BPX_TIME,DAY_NB=@DAY_NB"
                                                        ,connection);

                command.Parameters.AddWithValue("FLIGHT_ID", format);
                command.Parameters.AddWithValue("BPN_TIME", format1);
                command.Parameters.AddWithValue("BPX_TIME", format2);
                command.Parameters.AddWithValue("DAY_NB", format3);

    Hi everyone!

    Ive got the problem with inserting 4 values into columns. I wanna prevent inserting 4 existing columns into database, i cant set them unique, cause the same column can be inserted with other 1,2 or 3 columns, i just wanna prevent only 4 existing columns insert.

    Tuesday, May 8, 2018 11:56 AM

All replies

  • I'm not sure if I understand the question. You can create a unique index for multiple columns, four in your case. Basically only the four columns together would be unique and it would not matter if any one of them was duplicated.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 8, 2018 1:04 PM
  • Then do first a select with an executescalar. 

    If that returns a Null object then you know it does not exist. 

    https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlcecommand.executescalar(v=vs.100).aspx


    Success
    Cor

    Tuesday, May 8, 2018 1:15 PM
  • Can u write a code please? I mean how to create unique index for multiple columns

    Tuesday, May 8, 2018 2:10 PM
  • Can u write a code please? I mean how to create unique index for multiple columns

    What tool did you use to create the SQL CE database? You should be able to create a unique index using that tool. Otherwise you can use an SQL DDL statement:

    https://technet.microsoft.com/en-us/library/ms345331%28v=sql.110%29.aspx

    CREATE UNIQUE INDEX idxMultCols ON Customers (Col2, Col3, Col4, Col5)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 8, 2018 3:23 PM
  • I ve set the unique index on 4 columns, but now it doesn't insert values, though it doesn't give an error.
    Tuesday, May 8, 2018 4:07 PM
  • I ve set the unique index on 4 columns, but now it doesn't insert values, though it doesn't give an error.

    Is your SQL CE database file in your project (Solution Explorer)? If so, make sure the Copy to Output Directory property is set to "Do not copy". I think you might be overwriting your updates each time you run the app in VS.

    Otherwise, there is no other explanation if your call to Command.ExecuteNonQuery is succeeding (without error).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 8, 2018 5:46 PM
  • Hello,

    I would recommend trying Cor's recommendation as this is widely used method to ensure a duplicate does not occur.

    This works for any database. 

    In the following TechNet article .NET: Defensive data programming (Part 2) I show this method under the section Correct method to handle constraint violations. The code sample does one column yet works with one to many columns.

    This does not negate using constraints (were the above one works with or without constraints), just another way to prevent duplicates. 

    Ignore how the INSERT statement is constructed as this is for SQL-Server.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Tuesday, May 8, 2018 10:37 PM
    Moderator
  • Hello Fazka,

    A suggestion is you could use an Insert or Update pattern instead, which has more flexible than statement sugar ON DUBLICATE UPDATE.  You could choose how to update column value freely and also debug easily.  You could refer the below link.

    Insert or Update pattern for Sql Server 

    Best Regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 9, 2018 3:26 AM
    Moderator
  • I've done this in Oracle because I didn't want to be bothered with handling the exception. It's a good suggestion.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 9, 2018 12:49 PM