CREATE ID, NOT IDENTITY, FROM CONCATENATED VALUES

LUISOFO
LUISOFO used Ask the Experts™
on
Hello there, this is my case:

I have a main table which has an ID (not identity), which is composed by two values: 1 is a value which is read and added in 1, and 2, a constant which is used to determine the location where the record was created. Like this:

71001     51001
71002     51002
.              .
.              .
7nnnn     5nnnn

A column like I said, contains the Incremented values (1001), which runs depending on the used done on the location prefixed (7, 5), this prefix is the constant used.

I need then, to be able to read the last incremented value, assign it to the field, and concatenate that value with the prefix pro assign this way the ID of the main table.

I need to ask this question because actually I'm a complete novice in VS. Within VS I code in VB.NET, becase I have some experience usin VBA, but can't get it. A copy of my VBA code is this

In my module, this is the code I used to determine the last used Number within the location set:
_________________________________________________________________________
Function ControlNumber(TableName As String) As Integer
Dim RSCN As DAO.Database
Dim RSUCN As DAO.Recordset
Dim STRCN

Set RSCN = CurrentDb()
STRCN = "SELECT * FROM " & TableName & " WHERE " & TableName & ".EmpID LIKE '" & PrefixDb & "*' ORDER BY EmpID"
Set RSUCN = RSCN.OpenRecordset(STRCN, dbOpenDynaset, dbSeeChanges, dbOptimistic)
With RSUCN
.MoveLast
ControlNumber = RSUCN.Fields(1) + 1

End With
RSUCN.Close
Set RSUCN = Nothing

End Function
______________________________________________________

And this is the sub where I concatenate the new ID based on conditions:

_______________________________________________________


Private Sub Command163_Click()
Dim CtlNum
CtlNum = ControlNumber("EMPLOYEE")
If IsNull(Me.EmpID) Then
Me.EmpAutogen = CtlNum
EmpID = PrefixDb & CtlNum
DoCmd.RunCommand acCmdSaveRecord

End If

End Sub
________________________________________________________

I do really hope you can give me the light I need to continue with my project, which is moving into a web application the current one which run within a LAN.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>which is composed by two values
this is, for your information, against data normalisation. it should be 2 fields ... makes things easier ...

in your case, your (sql) code is completely inefficient due to that design.

anyhow, what IS the exact question?
Most Valuable Expert 2012
Top Expert 2014
Commented:
I would change the design to include LocationID as a separate column and keep the ID incrementing for all records regardless of location.
To get last ID in this case would require a simple Select Max(ID) command


The other option is to breakout location to separate column but increment ID for each location separately but you would need to use both columns for your primary key.
To get last ID in this case would require a Select Max(ID) ... Where LocationID=... command



>I need then, to be able to read the last incremented value, assign it to the field, and concatenate that value with the prefix pro assign this way the ID of the main table.

To answer the question as it is, try following

Select Max(right(ID, len(ID)-1)) From table where Left(ID, 1) = LocationID

Author

Commented:
Hello, and thanks for your replies.

Well, my current code (shown above) works fine. The ID field, is in deed composed like I wanted to explain of one field value, which I increment manually each time a new record is saved, plus the Location ID, which I use as a constant on the objects used to create the record.

My exact question, as I'm novice on VB.NET, is how to code my function to determine the last used number for that specific location for assigning that value to the manually incremented field.

I don't need to break down the ID's.

This is a running project which will be upgraded into a web based application, and now I can't make changes to the design.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2012
Top Expert 2014
Commented:
I answered that question in the last line of my comment

Select Max(right(ID, len(ID)-1)) From table where Left(ID, 1) = LocationID

You will pass the LocationID to this command and it will return you the max value of ID currently being used so you can increment it for new ID.

Author

Commented:
Thanks for your interest CodeCruiser, but please remember I'm new on VB.NET.
I do believe that your advice regarding the use of SELECT MAX() is the right one, but I really don't know the way to use it. I have wrote this on mu current VB.NET project:

        Public Sub GetLastID()
            Dim KeyNums As Integer
            Dim SQLStr As SqlCommand
            Dim StrSQL As String
            SQLStr.CommandType = CommandType.Text
            SQLStr.CommandText = Select Max(right(TbSubjectID, len(TbSubjectID)-1)) From TbSubject where Left(TbSubjectID, 1) = Me.Prefix

        End Sub

Open in new window

But so far I'm getting an error message under the select statement saying expression expected.

Could I ask more of your help and break down for me the complete way of using the solution you wrote about before?

I have done a research on the web trying to find the right way to use the select max() but all what I find doesn't seem to be my answer. I hope not to be so dumb that I can't get this done with some help.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Try following


  Public Function GetLastID(LocationID As Integer) As Integer
            Dim KeyNums As Integer
            Dim dbcon As New SqlConnection("connection string here")
            dbcon.Open
            Dim dbcmd As New SqlCommand("", dbcon)
            dbcmd.CommandText = "Select Max(right(TbSubjectID, len(TbSubjectID)-1)) From TbSubject where Left(TbSubjectID, 1) = " & LocationID
            Dim MaxID As Integer = dbcmd.ExecuteScalar()
            dbcmd.Dispose
            dbcon.Dispose
            Return MaxID
        End Function

Open in new window

Author

Commented:
CodeCruiser, thanks. Now I'm getting this message at line 7 of your code:

  7:          Dim MaxID As Integer = dbcmd.ExecuteScalar()

                                Option Strict On disallows implicit conversion from "object" to "integer"
Most Valuable Expert 2012
Top Expert 2014

Commented:
Change to

Dim MaxID As Integer = Integer.Parse(dbcmd.ExecuteScalar())
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
that would then be:

Dim MaxID As Integer = CType(dbcmd.ExecuteScalar() , Integer)

Author

Commented:
I'm getting same error message
Most Valuable Expert 2012
Top Expert 2014
Commented:
Do you need option strict? If not remove that line from top of file.

Author

Commented:
Thanks a lot CodeCruiser.

I think I will post other questions for this project as they come. Lot of help required for this very first project.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial