adding uniqueidentifier value to SQL table with VB

[tavie]

Hello...What I am trying to do is add records to a SQL table with VB ADO recordset. One of the values I need to add is a uniqueidentifier. I know how to do it in T-SQL but I have been unsuccssful in VB-ADO. All help is appreciated.....Below is a sample of my code...

Private Sub cmdaddsvr_Click()
Set cnsvr = New ADODB.Connection
Set svradd = New ADODB.Recordset
cnsvr.Open "serverdb", "svruser", "svruser"
Set newguid = uniqueidentifier
Set newguid = NEWID()

inputstr = InputBox("Enter Server Name&quot

svradd.Open "Master", cnsvr, adOpenKeyset, adLockOptimistic, adCmdTable

svradd.Fields("Servername&quot.Value = inputstr
svradd.Fields("svrguid&quot.Value = NEWID()
svradd.Update
svradd.Close
End Sub

 

[MarkSweetland]

Do you need a unique identifier at the record level or the user level?

Does NEWID() return this unique identifier or are you wanting to use an IDENTITY column?

What errors are you getting?

I see back-to-back assignments to newguid but they aren't used anywhere...

>> Set newguid = uniqueidentifier
>> Set newguid = NEWID()


???






Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[tavie]

I need to insert a unique ID for a new record. The example I provided is most likey incorrect. I just don't see how I can generate a new GUID for a record and insert into into a table.

 

[CCLINT]

If you do not want to use an IDENTITY field then:

Option Explicit
Private Declare Function StringFromGUID2 Lib "ole32.dll" (firstbyte As Byte, ByVal lpsz As Long, ByVal cbMax As Long) As Long
Private Declare Function CoCreateGuid Lib "ole32.dll" (firstbyte As Byte) As Long

Public Function CreateGuid() As String
Dim firstbyte(15) As Byte
Dim ret As Long
Dim charLen As Long
Dim lpsz As String

lpsz = String$(128, 0)
ret = CoCreateGuid(firstbyte(0))
charLen = StringFromGUID2(firstbyte(0), StrPtr(lpsz), 128&)
CreateGuid = Left$(lpsz, charLen)
End Function

 

[tavie]

In T-SQL I would use NewID() Is there any way to output that value into VB and basically add it into the table ???

 

[MarkSweetland]

Since the NEWID() is a SQL function and not a VB function you'll have to use it as part of your SQL statement.

Although I've never used it, NewID() can be used with an INSERT statement to generate an id for the uniqueidentifier data type column in your table:

Set cnsvr = New ADODB.Connection
Set svradd = New ADODB.Recordset
cnsvr.Open "serverdb", "svruser", "svruser"
strSQL = "INSERT INTO Master(Servername, svrguid) " & _
"SELECT '" & inputstr & "', NEWID()"
svradd.Open strSQL, cnsvr



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[tavie]

Thanks Mark...That is what I have now, I just wanted to see if it was possible to use the Addnew method and insert records with a guid in that manner by passing in the NEWID()
output as a variable...Tim

 

[Libertarian]

Try this:

Private Sub cmdaddsvr_Click()
   
   Dim cnsver As ADODB.Connection
   Dim svradd As ADODB.Recordset
   
   Dim inputstr As String
   Dim svrguid As String
 
   Set cnsvr = New ADODB.Connection
   Set svradd = New ADODB.Recordset

   cnsvr.Open "[ConnectionString]"

   inputstr = InputBox("Enter Server Name")
   
   SQL = "SET NOCOUNT ON DECLARE @LicenseKey UNIQUEIDENTIFIER SET @svrguid = NewID() INSERT INTO svruser (svrguid, Servername) Values (@svrguid, '" & inputstr & "') SELECT @svrguid AS 'NewGUID' SET NOCOUNT OFF"
  
   Set svradd = cnsvr.Execute(SQL)
   
   svrguid = svradd.Fields("svrguid").Value
   
   cnsvr.Close

   Set svradd = Nothing
   Set cnsvr = Nothing

End Sub

(I hope I synched up with your code okay.)

The variable "svrguid" contains the value of the new identifier created by the SQL call. The reason you need the SET NOCOUNT ON and SET NOCOUNT OFF clauses is because otherwise you'd get a "can't find this item in the collection" error when your recordset is returned. (SQL Server returns a count as well as a field value with this type of call.)

To fetch your record, use a SELECT statement like the following:

"SELECT * FROM Companies WITH (NOLOCK) WHERE svrguid = CAST('" & svrguid & "' AS UNIQUEIDENTIFIER)"

Note that, in these examples, "svrguid" is serving double duty as a field name and variable name. You may leave as is (SQL and VB will figure it all out) or, for readability, you could change one or the other or add brackets to the SQL call.

 

[Libertarian]

Oops. "Companies" should have been "svruser". Sorry. My own work was on my mind.

 

[Stereoparty]

What I do in a case like this is simply giving the uniqueidentifier field a Default Value of (newid()) when designing the table.

That way I can use VB ADO recordsets (.AddNew and .Update) to add new records, and let SQL Server take care of adding the GUID.