Generate Unique Max ID for each records 1

[sam4help]

Dears,

I am using VB6 and MS SQL 2005.
I need to generate Unique ID for each records (due to some manipulations required later I cannot use Auto ID's) currently I am using

SELECT Max(trID) as maxID FROM trData

which works well until other users would Press SAVE at same moment, so what would be the better way of doing this, any help?

Best Regards,

Sam

 

[HughLerwill]

>Press SAVE at almost the same moment

The pseudo code could go like this;

Try = 0
Do
try = try + 1
GetMaxId
CreateNewReCord GetMaxID+1
If Created without 'already exists' error Exit do
if try > 10 then
Msg 'something is up'
end if
Loop

 

[sam4help]

Dear HughLerwill, first of all thanks for the help; I tried your advice but I am getting error on executing this code which was working fine without do loop

        If rs1.State = 1 Then rs1.Close
        rs1.Open "SELECT * FROM trData where 1=2", conn, adOpenDynamic, adLockOptimistic

and yes first time it passes without error but when it comes back for second try it gives this error : Run-time error '3219': Operation is not allowed in this context.


My full code is down for your ready reference;

try = 0
Do
    On Error GoTo errHAndler
    try = try + 1
    If rs9.State = 1 Then rs9.Close
        rs9.Open "SELECT Max(trID) as maxID FROM trData", conn, adOpenKeyset, adLockReadOnly
        If IsNull(rs9!MaxID) = False Then
            trMaxID = rs9!MaxID + 1
        Else
            trMaxID = 1
        End If
        rs9.Close
        
        lblID.Caption = trMaxID
        
        MsgBox trMaxID
        
        If rs1.State = 1 Then rs1.Close
        rs1.Open "SELECT * FROM trData where 1=2", conn, adOpenDynamic, adLockOptimistic
        
            rs1.AddNew
            rs1!trID = trMaxID
            
            rs1!trProfileID = lblProfileID.Caption
            rs1!trProfileName = comboProfileSelection.Text
            Trim(d(2).Text) <> "" Then rs1!trLabelName9 = d(2).Text
            Trim(d(3).Text) <> "" Then rs1!trLabelName10 = d(3).Text
            rs1!trOptionName1 = Option1.Value
            rs1!trOptionName2 = Option2.Value
        
            If frame(11).Visible = True Then rs1!trEnableExpiryDate = e.Value
            rs1!trCreatedDate = Format(Now(), "yyyy-mm-dd")
                
            rs1!trOwner = Trim(lblUser.Caption)
            If chkConfidential.Value = 1 Then
                rs1!trType = "C"
            Else
                rs1!trType = "P"
            End If
            
            rs1.Update
            If rs1.State = 1 Then rs1.Close
    
   If try > 10 Then
       MsgBox "Currently Server is facing some Connection Issues, Please try saving After few minutes", vbCritical, "Cannot Save"
       Exit Sub
   End If

errHAndler:
    If Err.Number <> -2147217873 Then
        Exit Do
    End If
Loop

 

[HughLerwill]

I don't do much ADO so find it hard to comment on your exact code, however try..

Remove the existing error handler.

Put an On Error Resume Next before the line which causes the 'record already exists' error. I guess that will be before rs1.Update.

And put
If Err = 0 then Exit Do
On Error Goto 0
after it.

 

[sam4help]

OK, that above error is sorted out by adding following
rs1.CancelUpdate as follows

errHAndler:
    If Err.Number <> -2147217873 Then
        Exit Do
    End If
rs1.CancelUpdate

but now, for one time it runs fine but when the error comes up again in second loop its bypassing the Err handler and error is flashed saying
Violation of Primary KEY constraint ... may be I am missing something donno how its bypassing err handler..

Best Regards,

Sam

 

[sam4help]

Thanks for the reply HughLerwill,

I tried as per your advice to change the error handler but
Still I am having error at the rs1.Update

 

[HughLerwill]

Sorry, I suggest you post the code you have now; may be someone else can help.

 

[sam4help]

final code

Dim trMaxID As Long
try = 0
Do
    try = try + 1
    If rs9.State = 1 Then rs9.Close
        rs9.Open "SELECT Max(trID) as maxID FROM trData", conn, adOpenKeyset, adLockReadOnly
        If IsNull(rs9!MaxID) = False Then
            trMaxID = rs9!MaxID + 1
        Else
            trMaxID = 1
        End If
        rs9.Close
        
        lblID.Caption = trMaxID
        
        MsgBox trMaxID
        
       If rs1.State = 1 Then rs1.Close
        rs1.Open "SELECT * FROM trData where 1=2", conn, adOpenDynamic, adLockOptimistic
        
            rs1.AddNew
            rs1!trID = trMaxID
            
            rs1!trProfileID = lblProfileID.Caption
            rs1!trProfileName = comboProfileSelection.Text
            Trim(d(2).Text) <> "" Then rs1!trLabelName9 = d(2).Text
            Trim(d(3).Text) <> "" Then rs1!trLabelName10 = d(3).Text
            rs1!trOptionName1 = Option1.Value
            rs1!trOptionName2 = Option2.Value
        
            If frame(11).Visible = True Then rs1!trEnableExpiryDate = e.Value
            rs1!trCreatedDate = Format(Now(), "yyyy-mm-dd")
                
            rs1!trOwner = Trim(lblUser.Caption)
            If chkConfidential.Value = 1 Then
                rs1!trType = "C"
            Else
                rs1!trType = "P"
            End If
            
            rs1.Update
            If rs1.State = 1 Then rs1.Close
    
    
   If try > 10 Then
       MsgBox "Currently Server is facing some Connection Issues, Please try saving After few minutes", vbCritical, "Cannot Save"
       Exit Sub
   End If

errHAndler:
    If Err.Number <> -2147217873 Then
        Err.Clear
        Exit Do
    Else
        rs1.CancelUpdate
    End If
Loop

first time it works fine, but second time when it goes it increments the MaxID but again when it comes back to the rs1.Update it gives same error which on the first time it passed on to the err handler Violation of Primary KEY constraint ..., I tried on err resume next also but it gives same error and at same place

 

[Andrzejek]

You may try, instead of:

rs1.AddNew
rs1!trID = trMaxID
            
rs1!trProfileID = lblProfileID.Caption
rs1!trProfileName = comboProfileSelection.Text
...
rs1.Update

Something like:

strSQL = "INSERT INTO trData ([blue]trID[/blue], trProfileID, trProfileName, ...) " _
    & " VALUES([blue](SELECT Max(trID) + 1 FROM trData)[/blue], " _
    & lblProfileID.Caption & ", '" & comboProfileSelection.Text & "', ...)"

conn.Execute strSQL

You may alse include some IF statement while building your SQL:

strSQL = "INSERT INTO ... VALUES("

If Something Then
  strSQL = strSQL & " Some value"
Else
  strSQL = strSQL & " Some other value"
EnD ID

Have fun.

---- Andy

 

[sam4help]

Thanks Andy, but again what happens if more than one users press SAVE same time and by the time this entry is inserted based on select max same max number is inserted by other user before this?

 

[sam4help]

Ok.. OK do you mean to say I will keep the do try loop and change between code to insert, is it?

 

[HughLerwill]

>I tried on err resume next also but it gives same error and at same place

Could be I am being dense but if you did abandon the existing error handler and used

On Error Resume Next
rs1.Update
If Err=0 then Exit Do

as suggested I fail to see how a 'crash' type error could occur on the line rs1.Update

 

[Andrzejek]

Good questions, but...
We have this logic for about 12 years now, with over 600 users, and 'that never had happen'

Besides, your SELECT MAX() is inside the INSERT statement, and both have to be executed before any other statement is run by the data base.

If you want to set your mind at ease, write a simple app with this INSERT statement and run it by a few people at the same and try to brake it: SAVE it at the same time. In my opinion, you cannot do it - which is what you want.

Also, just in case, have an error handler ready to detect (impossible in my opinion) “Violation of Primary KEY constraint”


Have fun.

---- Andy

 

[gmmastros]

Sam4Help,

I would encourage you to follow the advice given by Andy (Andrzejek). By embedding the Select Max + 1 in the insert statement, the entire thing is wrapped inside a transaction so you are protected against simultaneous inserts.

The other benefit of this approach is that is means you do not need to select all of the data from the table prior to adding a new row. This will help to improve performance, especially with large tables.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Ok Andy let me workout this and check;
One more issue will come for me now is that with the same MaxID I need to insert a corresponding record in one more table (i.e a image); I may need to insert this and get the max for that user and then insert the image based on that ID (or is there a better way to do that?)

best regards,

Sam

 

[gmmastros]

In that case, you need to use multiple statements which opens you up to simultaneous inserts unless you explicitly create a transaction for it. Something like...

strSQL = "Declare @Id Int " _
       & "Begin Transaction " _
       & "Select @id = Max(trID) + 1 From trData " _
       & "Set @Id = Coalesce(@id, 1) " _
       & "INSERT INTO trData (trID, trProfileID, trProfileName, ...) " _
       & " VALUES(@Id, " _
       & lblProfileID.Caption & ", '" & comboProfileSelection.Text & "', ...)" _
       & "Select @Id As NewIdValue " _
       & "Commit Transaction "

conn.Execute strSQL

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Thanks George
If I am gonna change this into Insert Statement; I guess there are some complication;
Ok let me put in steps what I am doing to clear the complications in my case

1. I am inserting data into a transaction table (which was discussed above)
2. with reference ID from 1st data i need to insert a image into another table (field is image)
-Now the question here is how do we insert image with insert statement in my case I was using
rs2!trImage.AppendChunk bytes() never done with Insert Statement before )
3. then one more record with same reference ID in another table
4. 1st table contains a varbinary(256) field how do I insert record in this via INSERT statement?

Best Regards,

Sam

 

[gmmastros]

I didn't see anywhere in your original code where you were inserting an image. Honestly, I prefer not to store images in the database. Instead, I usually set up a file share on the network somewhere and then store the path to the file in the database (as a varchar column).

Regardless, my suggestion is...

Use code similar to what I show above for inserting the data without the image data. Then, you can select the data based on the ID number returned and use the append chunk method to add the image. This will require multiple round trips to the database, which is something you ordinarily want to avoid for performance reasons. However, the data the would be moving around (between SQL Server and your VB6 app) would be small and therefore should be quick enough.

When you execute the code I show above, there should be a recordset returned that has exactly one row and one column. The single piece of data would represent the ID that was just inserted.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Thanks George,
Actually I didn't want to confuse pasting all code which was irrelevant when I started this thread, hence didn't paste the image insertion code which was working well and was after this data insertion.

Due to security reasons I had to insert the image inside database.

Yes, what I understood is I will use Insert statement to insert all the data except Image, then get the last record inserted id and using that will insert the image with append chunks.

One more question Do you know how to insert a byte data (which is a encrypted password) into varbinary(256) ?

Best Regards,

Sam

 

[gmmastros]

Are you encrypting the data in the VB6 code, or are you encrypting within the database?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom