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.