[Webinar] Streamline your web hosting managementRegister Today

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

Sorting records alphabetically in VB.net

I've got an MS Access table; each record has a column which gives that record's unique alphanumeric code e.g. A0001W, A002W, B001W etc. (The "W" at the end is constant.) When a user creates a new record in the table (through a VB.net application), I need this VB.net application to assign a new code to the new record based on the previous highest value for that letter. So if the Contact's name in the new record is George and the previous highest G code was G056W, I need the program to assign G057W to the new record. I'm using VB.net 2005 with Access XP. Any suggestions would be most welcome. I had something similar working just fine in VBA but now I'm at sea trying to achieve the same in VB.net.

2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you using a single column to store this? Generally this wouldn't be the best method; it appears that your "0001", "0002" is the "counter" section ... you should store this value for each record, then build the "unique alphanumeric code" as needed ...
Agreed the proper way would be to have an separate numeric field and build the code as needed.  In lieu of this, you could do it through code.
For your example, you need to isolate the number.  You could use something like Mid([uniqueID],2,InStrRev([uniqueID],"W")-2).  This will returen 0001, 002, 001, respectively.  Increase that result by 1.  Then concatenate the user's initial and the trailing "W".
You could use something like

   Imports System.Data.OleDb

        Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & <yourdatabasename>
        Dim con As New OleDbConnection(conString)
        Dim cmd As New OleDbCommand
        Dim Result As String

        Dim sql As String = "SELECT MAX(<yourfieldname>) FROM <yourtablename> WHERE <yourfieldname> LIKE 'G%'"
        cmd.Connection = con
        cmd.CommandText = sql
        Result = cmd.ExecuteScalar

Then Result would have in it the highest number so far used for 'G'.  You could extract the numerical portion with .SubString.

BozMAuthor Commented:
Thanks lads, LSMConsulting gave me the common sense way to structure the database in the first place and Roger gave me the code to work around my current structure. Thanks to both of you.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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