Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

CREATE ID, NOT IDENTITY, FROM CONCATENATED VALUES

Posted on 2012-12-20
12
Medium Priority
?
262 Views
Last Modified: 2012-12-21
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.
0
Comment
Question by:LUISOFO
  • 5
  • 5
  • 2
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38712147
>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?
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 1600 total points
ID: 38712447
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
0
 

Author Comment

by:LUISOFO
ID: 38712626
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 1600 total points
ID: 38712637
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.
0
 

Author Comment

by:LUISOFO
ID: 38714297
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.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1600 total points
ID: 38714311
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

0
 

Author Comment

by:LUISOFO
ID: 38714391
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"
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 38714405
Change to

Dim MaxID As Integer = Integer.Parse(dbcmd.ExecuteScalar())
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 38714418
that would then be:

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

Author Comment

by:LUISOFO
ID: 38714426
I'm getting same error message
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 1600 total points
ID: 38714427
Do you need option strict? If not remove that line from top of file.
0
 

Author Comment

by:LUISOFO
ID: 38714458
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question