none
stop the duplicate number RRS feed

  • Question

  • i create the project in VB.net. I want to PAN Number against Invoice Number entered.

    for ex :

    [ANBPN5452C] this is PAN Number against invoice number update.

    PAN NUMBER               INVOICE NUMBER

    ANBPN5452C               12

    ALLKJ3340I                 98799

    ANBPN542C                 9660

    ANBPN5452C                12

    When one PAN Number against Invoice Number same show the error.

    I want to prevent the duplicate number against PAN Number for specific PAN Number.

    please help me for this.


    Monday, August 14, 2017 5:03 PM

All replies

  • Where?

    And is it a single or a multi user solution. 

    For a single user application it is kid works but for multi user it has difficulties. 


    Success
    Cor

    Monday, August 14, 2017 5:16 PM
  • Is this is from a database table with existing rows of data as shown or is the data local e.g. being entered from a DataGridView into it's data source or something completely different. Best to be very specific when asking a question so we don't need to ask your for these details :-)

    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

    Monday, August 14, 2017 6:52 PM
    Moderator
  • Aniket,

    You're talking about data validation. Assuming that the "PAN" number is this:

    https://en.wikipedia.org/wiki/Permanent_account_number

    However you do it, I suggest that you first validate that the form of the PAN number is correct and follows the rules that's shown there for how it's to be set up.

    If that passes, check the persisted collection of PAN/Invoice Number for a match in both fields. I'd use LINQ but you can do that with a conventional way if you prefer.

    After a while that will get to be slow once the collection gets large, so to make it more efficient, do this:

    Also persist a separate collection of the PAN numbers by themselves. Before running the longer test, check first to see if the PAN number is in the list of them. If it's not, no need to run the long test because it's not in there.

    For what it's worth. :)


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Monday, August 14, 2017 7:09 PM
  • Okay, I wanted to address this (without you providing details) this as data existing in a database table and in this case using SQL-Server.

    The code below uses hard code values where you would have those as dynamic values. The first query checks to see if the two field combination exists, if not insert, if the combo exists don't insert. Note this is a text book example that needs to be modified for a production app.

    Lastly, when inserting the sample below returns the new primary key, may or may not be important to you, usually most developers need the new primary key for one reason or another.

    Code requires .NET Framework 3.5 or higher

    Public Sub NoDuplicate()
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT COUNT([id]) 
                        FROM Table_1
                        WHERE Pan_Number = @PanNumber AND Invoice_Number = @InvoiceNumber
                    </SQL>.Value
                cmd.Parameters.AddWithValue("@PanNumber", "ANBPN5452C")
                cmd.Parameters.AddWithValue("@InvoiceNumber", 1)
                cn.Open()
                Dim result = CInt(cmd.ExecuteScalar)
                If result = 0 Then
                    Dim NewIdentifier As Integer = 0
                    cmd.CommandText =
                        <SQL>
                            INSERT INTO Table_1 
                            (
                                Pan_Number,
                                Invoice_Number
                            ) VALUES 
                            (
                                @PanNumber,
                                @InvoiceNumber
                            );
                            SELECT CAST(scope_identity() AS int);
                        </SQL>.Value
    
                    NewIdentifier = CInt(cmd.ExecuteScalar)
                    Console.WriteLine(NewIdentifier)
                Else
                    Console.WriteLine("exists")
                End If
    
            End Using
        End Using
    End Sub

    Connection string (private variable in a class which has the code above)

    Private ConnectionString As String = "Data Source=KARENS-PC;Initial Catalog=NorthWindDemo;Integrated Security=True"

    Now I didn't simply write the above and test it, instead I wrote a test query in SQL-Server Management Studio which as shown below with slight changes could had been turned into a stored procedure with both queries in the database.

    DECLARE @PanNumber NVARCHAR(MAX) = 'ANBPN5452C';
    DECLARE @InvoiceNumber INT = 12;
    
    SELECT  COUNT([id])
    FROM    Table_1
    WHERE   Pan_Number = @PanNumber
            AND Invoice_Number = @InvoiceNumber;

    Screenshot of the data

    In closing, I didn't address existing duplicates, if you need that then consider looking at my find duplicate sample on MSDN

    Find duplicate records via SQL or LINQ in database table

    If not dealing with a database, you need to elaborate.


    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, August 15, 2017 12:36 AM
    Moderator