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

 

[sam4help]

George I am encrypting it via VB6 Code
and the output is in byte which I am storing in varbinary(256)
all is fine until I was using rs1.AddNew to save this in db, but if I am going to save visa INSERT statement what and how i need to do?

 

[gmmastros]

I assume you are storing encrypted data in a byte array. To store this in the database (using the insert command) you will need to convert your byte array in to hex. You then store the hex data in the DB.

You can use the following VB6 function to convert a byte array in to a hex string suitable for sending to SQL.

Public Function ByteArrayToHexString(ByRef Data() As Byte) As String

    Dim i As Long
    Dim Output As String
    
    Output = ""
    For i = LBound(Data) To UBound(Data)
        Output = Output & Right$("0" & Hex(Data(i)), 2)
    Next
    
    ByteArrayToHexString = Output

End Function

When creating an insert string with binary data, it should look similar to this:

Insert Into SomeTable(Id, NormalData, EncryptedData)
Values (1, 'Hello World', 0x48656C6C6F2057726C64)

-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]

Thank you all George, Andrzejek and HughLerwill for your valuable time and help. I will look out all options and work out the best from your advice.

Best Regards,

Sam

 

[strongm]

>encrypted data in a byte array

Of course it might actually be simpler (and possibly more secure to use the CAPICOM library which will can produce a string from encryption rather than a binary result

 

[sam4help]

Hello Again,

I was trying to use this as advised

strSQL = "INSERT INTO trData (trID, trOwner) " _
                & " VALUES((SELECT Max(trID) + 1 FROM trData)" & ", '" & Trim(lblUser.Caption) & "')"
conn.Execute strSQL

It works well on my developement PC which is having MS SQL 2008 (Express) but when deployed on another PC for testing (having MS SQL 2005 - Express) it shouts errors saying Subqueries are not allowed in this context. Only scalar expressions are allowed.

 

[sam4help]

George Thanks again here your advise of multiple statement insert came out to be savior I used

strSQL = "Declare @Id Int " _
       & "Begin Transaction " _
       & "Select @id = Max(trID) + 1 From trData " _
       & "Set @Id = Coalesce(@id, 1) " _
       & "INSERT INTO trData (trID, trOwner) " _
       & " VALUES(@Id,'" & Trim(lblUser.Caption) & " ')" _
       & "Select @Id As NewIdValue " _
       & "Commit Transaction "
       
        conn.Execute strSQL

And it worked well, few clarifications
1. as we are using transaction will it roll-out if any errors is there and move out to my next code in VB?
2. whats is the role of this line "Select @Id As NewIdValue " _