WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.
Course Of The Month
8 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
CREATE ID, NOT IDENTITY, FROM CONCATENATED VALUES
Posted on 2012-12-20
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
2
12 Comments
>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?
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?
Active 4 days ago
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
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
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.
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.
Active 4 days ago
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.
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.
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:
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.
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
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.
Active 4 days ago
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
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"
7: Dim MaxID As Integer = dbcmd.ExecuteScalar()
Option Strict On disallows implicit conversion from "object" to "integer"
Featured Post
To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The purpose of this video is to demonstrate how to Import and export files in WordPress.
This will be demonstrated using a Windows 8 PC.
Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to set up basic WordPress SEO.
This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast.
Go to your WordPress login page. This will look like the following: myw…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 22 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.