468,251 Members | 1,464 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,251 developers. It's quick & easy.

Access and GUID Column Type

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.
--
Dennis in Houston
Nov 21 '05 #1
20 5526
Dennis wrote:
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,

--

(O) e n o n e
Nov 21 '05 #2
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/de...wguidtopic.asp

Ken
----------------------
"Dennis" <De****@discussions.microsoft.com> wrote in message
news:ED**********************************@microsof t.com...
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.
--
Dennis in Houston
Nov 21 '05 #3
That doesn't compile...Error is Operator '&' is not defined for string or
system.GUID
--
Dennis in Houston
"Oenone" wrote:
Dennis wrote:
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,

--

(O) e n o n e

Nov 21 '05 #4
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"
--
Dennis in Houston
"Ken Tucker [MVP]" wrote:
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/de...wguidtopic.asp

Ken
----------------------
"Dennis" <De****@discussions.microsoft.com> wrote in message
news:ED**********************************@microsof t.com...
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.
--
Dennis in Houston

Nov 21 '05 #5
This works:

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

--
Dennis in Houston
"Ken Tucker [MVP]" wrote:
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/de...wguidtopic.asp

Ken
----------------------
"Dennis" <De****@discussions.microsoft.com> wrote in message
news:ED**********************************@microsof t.com...
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.
--
Dennis in Houston

Nov 21 '05 #6
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?
--
Dennis in Houston
"Ken Tucker [MVP]" wrote:
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/de...wguidtopic.asp

Ken
----------------------
"Dennis" <De****@discussions.microsoft.com> wrote in message
news:ED**********************************@microsof t.com...
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.
--
Dennis in Houston

Nov 21 '05 #7
Dennis wrote:
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. :)

--

(O) e n o n e
Nov 21 '05 #8
Dennis wrote:
"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.

--

(O) e n o n e
Nov 21 '05 #9
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.


--
Dennis in Houston
"Oenone" wrote:
Dennis wrote:
"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.

--

(O) e n o n e

Nov 21 '05 #10
Dennis,

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

http://www.windowsformsdatagridhelp....3-eb8b44af0137

I hope this helps,

Cor
Nov 21 '05 #11
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!

--
Dennis in Houston
"Cor Ligthert" wrote:
Dennis,

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

http://www.windowsformsdatagridhelp....3-eb8b44af0137

I hope this helps,

Cor

Nov 21 '05 #12
Dennis,

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

I hope this helps,

Cor
Nov 21 '05 #13
How do you find a GUID in a DataSet without looping thru all the rows?
--
Dennis in Houston
"Cor Ligthert" wrote:
Dennis,

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

I hope this helps,

Cor

Nov 21 '05 #14
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
Nov 21 '05 #15
I'm doing both!

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

--
Dennis in Houston
"Cor Ligthert" wrote:
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

Nov 21 '05 #16
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
Nov 21 '05 #17
Dennis,

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

That returns a datarowview.
Cor
Nov 21 '05 #18
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 & "'")
--
Dennis in Houston
"Cor Ligthert" wrote:
Dennis,

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

That returns a datarowview.
Cor

Nov 21 '05 #19
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
Nov 21 '05 #20
Thanks Cor..I'll try the .Find Method.
--
Dennis in Houston
"Cor Ligthert" wrote:
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

Nov 21 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by chris | last post: by
reply views Thread by vadnala | last post: by
1 post views Thread by Leon | last post: by
reply views Thread by Dan Hartshorn | last post: by
4 posts views Thread by Melson | last post: by
2 posts views Thread by joe | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.