Access and GUID Column Type

[Guest]

I have created an access database table in code with the field "KeyId" as a
GUID type. What is the SQL syntax for updating a record matching this GUID,
i.e.,

Dim myGUid as New GUID(....)

mySQL = "UPDATE ITEMS SET Title="New Record No. 1", WHERE KeyID = myGUID)

The above of course tries to match the KeyID field value with the value
"myGUID"

Any help would be appreciated.

 

[Oenone]

mySQL = "UPDATE ITEMS SET Title="New Record No. 1", WHERE KeyID =
myGUID)

Try:

\\\
mySQL = "UPDATE ITEMS SET Title='New Record No. 1' WHERE KeyID = '" &
myGUID &"'"
///

(sorry if that wraps, it should all be on one line). Note the use of
quotation marks -- single quotes to delimit strings in the SQL statement,
single quotes around the GUID, and double-quotes to end the literal string
to allow the myGUID variable contents to be inserted.

Hope that helps,

 

[Ken Tucker [MVP]]

Hi,

In addition to Oenone comments I would use newguid to create the
grid.

Dim myGUid as GUID = GUID.NewGuid

http://msdn.microsoft.com/library/d...ref/html/frlrfsystemguidclassnewguidtopic.asp

Ken
----------------------
I have created an access database table in code with the field "KeyId" as a
GUID type. What is the SQL syntax for updating a record matching this GUID,
i.e.,

Dim myGUid as New GUID(....)

mySQL = "UPDATE ITEMS SET Title="New Record No. 1", WHERE KeyID = myGUID)

The above of course tries to match the KeyID field value with the value
"myGUID"

Any help would be appreciated.

 

[Guest]

That doesn't compile...Error is Operator '&' is not defined for string or
system.GUID

 

[Guest]

Thanks. Also, the string

mySQL = "UPDATE ITEMS SET Title='New Record No. 1' WHERE KeyID = '" &
myGUID &"'"

does not compile and gives the error "Operator '&' not defined for string or
System.GUID"

 

[Guest]

This works:

mySQL = "UPDATE ITEMS SET Title="New Record No. 1", WHERE KeyID ='" &
myGUID.ToString & "'")

 

[Guest]

I got it to work...sort of. This works for selecting a record with a GUID:

"SELECT * FROM Items WHERE KeyID= '" & myGUID.ToString & "'"

but this doesn't for updating a record:

"UPDATE Items SET Title='new title' WHERE KeyID= '" & myGUID.ToString & "'"

I don't get an error, it just doesn't update the record. The WHERE clauses
in the SQL's are exactly the same. Why does Select work and Update doesn't?

 

[Oenone]

That doesn't compile...Error is Operator '&' is not defined for
string or system.GUID

Ah sorry, hadn't noticed that your myGUID variable was a GUID object and not
a string. I see from your other posts that you've got it working though.

 

[Oenone]

"UPDATE Items SET Title='new title' WHERE KeyID= '" & myGUID.ToString
& "'"

I don't get an error, it just doesn't update the record. The WHERE
clauses in the SQL's are exactly the same. Why does Select work and
Update doesn't?

I can't see anything obviously wrong with that statement. Are you sure the
GUID isn't being re-generated? Perhaps you could assign the SQL statement to
a string variable and Debug.WriteLine() it just to be completely sure, then
execute the SQL in the string.

Are you using a SQL Server database? If so you could try these:

- execute the statement that is returned by the Debug.WriteLine() in Query
Analyzer. This will tell you how many records were updated. This will
discount ADO.NET problems.

- run SQL Profiler to see exactly what query was executed.

Hopefully one of these will track down the problem.

 

[Guest]

I'm using the Jet Engine and Access DataBase.
I finally found a syntax that works and it's a mystery...must be a bug in
Access.

This works
"SELECT * FROM Items WHERE KeyID= '" & myGUID.ToString & "'"
but this doesn't
"UPDATE Items SET Title='new title' WHERE KeyID= '" & myGUID.ToString & "'"

However, both of these work:
"SELECT * FROM Items WHERE KeyID= '{" & myGUID.ToString & "}'"
"UPDATE Items SET Title='new title' WHERE KeyID= '{" & myGUID.ToString & "}'"

Now that's an abberation that shouldn't exist! Just adding the {} makes it
work. Gotta be a bug in the Jet Engine.

 

[Cor Ligthert]

Dennis,

Why don't you use simple oledb parameters, it is much easier to use.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope this helps,

Cor

 

[Guest]

I normally do use parameters but sometimes using an SQL query with values in
the string is much less coding. Also, I like to understand as much as I can
and when the GUID worked in the Select and not the Update, I wanted to know
why! Do you know why? At least now you know that you can include the GUID
directly in the SQL Update/Where and how to do it!

 

[Cor Ligthert]

Dennis,

Why not use that parameters a Guid is not a string it is a uniqueidentifier.

I hope this helps,

Cor

 

[Guest]

How do you find a GUID in a DataSet without looping thru all the rows?

 

[Cor Ligthert]

Dennis,

Where comes that DataSet comes in this thread, I am all the time thinking
that you are reading something using a datareader and wants to update
something directly using command.execute

I assume that everybody does

Cor

 

[Guest]

I'm doing both!

How do you find a GUID in a Dataset without looping thru all the rows?

 

[Cor Ligthert]

Dennis,

If you want to have returned a datarow collection you can use the
datatble.select, if you want to use a datarowview you can use a rowfilter
with a dataview

If you don't know how to do it with one of those, give than a reply and than
tell which one.

I hope this helps,

Cor

 

[Cor Ligthert]

Dennis,

I almost forgot, you can as well use a dataview.find

That returns a datarowview.


Cor

 

[Guest]

I am currently using the below statement to return a specific row from a
DataSet which matches the GUID. I was trying to find an alternative like you
suggested that didn't require using a string GUID.

Dim ra() As DataRow = myDataSet.Tables("Items").Select("ColGuid ='" &
myGuid.ToString & "'")

 

[Cor Ligthert]

Dennis,

I made this little sample. it needs only a label on a form to try.

\\\
Private Sub Form1_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim dt As DataTable = CreateTables()
Dim thekey As Object = DirectCast(dt.Rows(1)(0), Guid)
'just a simple way to get a key
dt.DefaultView.Sort = "TheKey"
Dim index As Integer = dt.DefaultView.Find(thekey)
Label1.Text = dt.DefaultView(index)("Name").ToString
End Sub

'To have a table to use is one created below
Private Function CreateTables() As DataTable
Dim dt As New DataTable
dt.Columns.Add("TheKey", GetType(System.Guid))
dt.Columns.Add("Name", GetType(System.String))
dt.LoadDataRow(New Object() {Guid.NewGuid, "Ken"}, True)
dt.LoadDataRow(New Object() {Guid.NewGuid, "Cor"}, True)
Return dt
End Function
///

I hope this helps a little bit?

Cor