How to figure out unique identifier?

[dvst8]

One of the tables I write some values to has 2 columns. A 'binary' type column, and a 'uniqueidentifier' type colum.

When I insert a 1 into the binary column, a uniqueidentifier is created based on the number of seconds since midnight...or something to that effect.

But once I have created this item, I need a way to go back and extract this unique identifier so that I can put this value in some other tables. Trouble is, I have no means of looking this new uniqueidentifier that was just created.

Does anyone see a way how I can get it? It's been a long day and I'm tired...maybe I'm just not thinking straight.

If someone can see a way around my problem, I'd be very appreciative.

Thanks

dvst8

[BrandonSk]

Hey. I have a similar problem using INSERT INTO statement. but if you are using the AddNew method then it is easy.
It should look like this

Set rsRecordset=DB.OpenRecordset(...)

with rsRecordset
.AddNew
.Fields("Binary")=1
.Update
'And here is the trick
.Bookmark=.LastModified
end with

Hope that helps. Lastmodified stores the bookmark value of last modified record (in this case the new added record)

[AKA]

In SQL Server is it possible to make a stored prosedure that do the insert and in the end have something like :

SELECT @uniqueidentifier = @@Identity

That returns the identity of the created row.

You define the uniqueidentifier as firstparameter like :

@uniqueidentifier int output,
@BinaryData......

[dvst8]

AKA
Not sure...

Brando

does addNew do the same as an insert into the database?>?

[AKA]

AddNew and Insert is the "same". AddNew is ADO and Insert is SQL.

Do you use SQL Server ?

Asuming you have a stored procedure named Binary_InsOne simmalar to this example


CREATE PROCEDURE Binary_InsOne
@GroupId int output,
@BinaryData binary
AS

INSERT INTO Binary
(
BinaryData
)
SELECT
@BinaryData

SELECT @uniqueidentifier = @@Identity
RETURN 0


Then you could do this in VB


Dim objCmd As New ADODB.Command

On Error GoTo ErrHandler
With objCmd
.CommandText = "Binary_InsOne"
.CommandType = adCmdStoredProc
.ActiveConnection = dbSqlServer
End With

With objCmd.Parameters
.Append objCmd.CreateParameter ("@uniqueidentifier", adBigInt, adParamOutput, ,uniqueidentifier , )
.Append objCmd.CreateParameter( "@BinaryData", adBinary, adParamInput, , BinaryData)
End With

objCmd.Execute

Set objCmd = Nothing


ending up with the identifier in your uniqueidentifier varible.

[dvst8]

thanks AKA!

[dvst8]

I'm tyring to do the ADO method, and I get the following error:

"Object or provider is not capable of performing requested operation."

I'm just using default cursor, but this should still work...

dvst8

[dvst8]

AKA

how do I access the output variable from ado?
and, why do you return 0 in your stored procedure?

thanks

[Edited by dvst8 on 08-18-2000 at 10:23 AM]

[AKA]

You will get the result in your VB variable uniqueidentifier that you sent in with CreateParam.

The 0 have I inherit and I have always asumed that it is the errorcode being OK, but I dont realy know.

[666539]

I had a similar problem. I think I solved it by using the .movelast method on the recordset. Because the record has just been created it is the last one.

This may or may not work depending on how you connect to the database. I was using ADO 2.1 I think. Whether the recordset contains the identifier depends on how you connect to the database.