none
Data and Primary Key RRS feed

  • Question

  • I have an Application which is connected to an SQL Database.

    I have a form from where the user can either edit a current record from the database or add a new Record to the Database.

    The Datatable contains Customer Information and as a Primary Key it has the Customer Code which I would like to be in a Format of 'ABC001'. The First three carathers are generated from the Surname. Now i Need to add the last three digits to the Code in order to create a unique code and then write it back to the databse.

    Can this be done in anyway through Visual Basic?

    Thanks. 
    inimeg
    Friday, September 5, 2008 8:24 AM

Answers

  • You could try a query like this (assuming Sql Server)...

            SELECT CustomerID FROM Customers WHERE CustomerID LIKE 'ABC%' ORDER BY CustomerID

    .. where you are selecting all the ID values that match the prefix letters.  If you use a data adapter to retrieve the results into a data table (or a data reader to read the values one-by-one) then you can see how many lines were retrieved, find the maximum number suffix (not sure if the ORDER BY will work here but it might) and add one -- then insert the new record with the new CustomerId....  and hope that you do not have multiple clients on the database where someone can steal the ID value in the half-second of processing in-between :)

    Friday, September 5, 2008 12:02 PM
  • That shouldn't be a problem if your field is primary key
    just generate the code and fire an insert in the database.

    If the field already consists the value then database will not allow you to insert the duplicate value in the primary key field and which in result throw an exception.

    Then in return you can catch that exception and display appropriate message to the user.

    If the field is not primary key field then you can follow dig-Boy approach.


    Prateek Regmi Blog for Programmers
    Monday, September 8, 2008 6:33 AM
  • Thanks Guys....it worked perfectly.
    inimeg
    • Marked as answer by inimeg Thursday, September 11, 2008 10:46 AM
    Thursday, September 11, 2008 10:46 AM

All replies

  • yes 

    just write a function to get the first three characters and increase last 3 digits by 1 everytime you insert, then it shouldn't be a problem.

    Prateek Regmi Blog for Programmers
    Friday, September 5, 2008 9:47 AM
  • Ok . but how can i check that the generated code is not already been used in the Database?
    inimeg
    Friday, September 5, 2008 9:51 AM
  • You could try a query like this (assuming Sql Server)...

            SELECT CustomerID FROM Customers WHERE CustomerID LIKE 'ABC%' ORDER BY CustomerID

    .. where you are selecting all the ID values that match the prefix letters.  If you use a data adapter to retrieve the results into a data table (or a data reader to read the values one-by-one) then you can see how many lines were retrieved, find the maximum number suffix (not sure if the ORDER BY will work here but it might) and add one -- then insert the new record with the new CustomerId....  and hope that you do not have multiple clients on the database where someone can steal the ID value in the half-second of processing in-between :)

    Friday, September 5, 2008 12:02 PM
  • That shouldn't be a problem if your field is primary key
    just generate the code and fire an insert in the database.

    If the field already consists the value then database will not allow you to insert the duplicate value in the primary key field and which in result throw an exception.

    Then in return you can catch that exception and display appropriate message to the user.

    If the field is not primary key field then you can follow dig-Boy approach.


    Prateek Regmi Blog for Programmers
    Monday, September 8, 2008 6:33 AM
  • Thanks Guys....it worked perfectly.
    inimeg
    • Marked as answer by inimeg Thursday, September 11, 2008 10:46 AM
    Thursday, September 11, 2008 10:46 AM