Problem inserting a GUID into an UniqueIdentifier field on SQL 200

A

Andrew

Vb.net 2005 app

I am looking up records in a sql 2005 database and I grab the
uniqueidentifier and put it into a variable (gID) of type GUID.

When I try to create a new record in a new table and link a uniqueidentifier
field with variable( gID) I get the following error:

operator & is not defined for types string and system.guid

my code is:

cmd.CommandText = "INSERT INTO sim_groups
(service_item_lib_id,service_item_group_name,description,delete_ind,create_timestamp)" & _
"VALUES (" & gLibID & ", '" & GroupName & "', '" & GroupDes &
"', 'N',current_timestamp)"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.Execute()

the variable name is gLibID and the field going into service_item_lib_id
witch is a uniqueidentifier..

I don’t remember how to overcome this, can anybody help?
 
C

Chris Anderson [MVP-VB]

Andrew said:
Vb.net 2005 app

I am looking up records in a sql 2005 database and I grab the
uniqueidentifier and put it into a variable (gID) of type GUID.

When I try to create a new record in a new table and link a uniqueidentifier
field with variable( gID) I get the following error:

operator & is not defined for types string and system.guid

my code is:

cmd.CommandText = "INSERT INTO sim_groups
(service_item_lib_id,service_item_group_name,description,delete_ind,create_timestamp)" & _
"VALUES (" & gLibID & ", '" & GroupName & "', '" & GroupDes &
"', 'N',current_timestamp)"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.Execute()

the variable name is gLibID and the field going into service_item_lib_id
witch is a uniqueidentifier..

I don’t remember how to overcome this, can anybody help?
Click to expand...
Ah.... took me a moment there... use gLibID.ToString..... apparently,
GUID types cannot be concatenated to strings implicitly. So you'll need
to convert it to a string (ala .ToString) before appending (or as you
append in this case).

-ca
 
G

Guest

cmd.CommandText = "INSERT INTO sim_groups
(service_item_lib_id,service_item_group_name,description,delete_ind,cre
ate_timestamp)" & _
"VALUES (" & gLibID & ", '" & GroupName & "', '" &
GroupDes &
"', 'N',current_timestamp)"
Click to expand...


Two things:

#1. You shouldn't build SQL strings like this - it's ripe for an injection
attack. Rather you should use SQL parameters.

#2. Use SQL Parameters

However, if you plan on sticking with bad habits, take a look at
Guid.Tostring().
 
A

Andrew

when I do that I get: "Operand type clash: numeric is incompatible with
uniqueidentifier"

code:
cmd.CommandText = "INSERT INTO sim_groups
(service_item_lib_id,service_item_group_name,description,delete_ind,create_timestamp)" & _
"VALUES (" & gLibID.ToString & ", '" & GroupName & "', '" &
GroupDes & "', 'N',current_timestamp)"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText
cmd.Execute()


tried this, and don't know what else to do..
 
A

Andrew

Spam Catcher said:
Two things:

#1. You shouldn't build SQL strings like this - it's ripe for an injection
attack. Rather you should use SQL parameters.

#2. Use SQL Parameters

However, if you plan on sticking with bad habits, take a look at
Guid.Tostring().
Click to expand...

can you give me an example of SQL Parameters?

when I do the .ToString() I still get an error:
"Operand type clash: numeric is incompatible with
uniqueidentifier"

any other ideas..?
 
G

Guest

can you give me an example of SQL Parameters
Click to expand...

In .NET 2.0:

cmd.CommandText = "INSERT INTO MyTable (SomeValue) VALUES
(@MyValueParameter)

cmd.Parameters.AddWithValue("@MyValueParameter", SomeValue)

..NET 1.1 is similar but there is no AddWithValue function :)
 
A

Andrew

I have used the vb.net 2.0 to update records and to insert records, but not
to retrieve records, haven’t had to.. now I do how to I get the field
information.. I have my example pasted below:

Dim strConncetionstring As String = ""
'connection
If Mdl1.bTrustCon = True Then
'trusted connection
strConncetionstring = "Trusted_Connection=True; Initial
Catalog=" & Mdl1.strDatabase & ";Data Source=" & Mdl1.strServerName & ""
Else
'non-trusted connection
strConncetionstring = "User ID=" & Mdl1.strUser & "; password="
& Mdl1.strPass & "; Initial Catalog=" & Mdl1.strDatabase & ";Data Source=" &
Mdl1.strServerName & ""
End If
Dim myConnection As New SqlConnection(strConncetionstring)
Dim myCommand As String = "select
LTRIM(RTRIM(service_item_group_name)) as SGoupName from sim_groups where
LTRIM(RTRIM(service_item_group_name))='" & strGroupN & "' and
service_item_lib_id='" & strLibID & "'"
Dim da As SqlDataAdapter = New SqlDataAdapter(myCommand, myConnection)
Dim ds As New DataSet

da.Fill(ds) 'filling dataset

If ds.Tables(0).Rows.Count = 0 Then
Return (False) 'creat new
Else
If ds.Tables(0).Rows(1).Item("SGoupName").ToString = strGroupN
Then
Return (True)
End If
End If

da.Dispose()
ds.Dispose()
myConnection.Close()

am I pretty good with the vb.net 2.0 connection, just one thing, how do I
get the record that is in field “SGoupName�
 
G

Guest

Dim myConnection As New SqlConnection(strConncetionstring)
Dim myCommand As String = "select
LTRIM(RTRIM(service_item_group_name)) as SGoupName from sim_groups
where LTRIM(RTRIM(service_item_group_name))='" & strGroupN & "' and
service_item_lib_id='" & strLibID & "'"
Dim da As SqlDataAdapter = New SqlDataAdapter(myCommand,
myConnection) Dim ds As New DataSet
Click to expand...

Still doing string SQL concatenatino huh? :)
 
Top