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 20 5723
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
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
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
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
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
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
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
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
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
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
Dennis,
Why not use that parameters a Guid is not a string it is a uniqueidentifier.
I hope this helps,
Cor
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
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
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
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
Dennis,
I almost forgot, you can as well use a dataview.find
That returns a datarowview.
Cor
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: chris |
last post by:
What is the recommended way of storing 10 digit phone numbers? I like
bigint(10), but it seems most are using varchar? Or I've also seen 2
int columns, one for area code and another for the rest...
|
by: vadnala |
last post by:
I am a newbie and struggling to get the correct Column "Type". I really
appreciate if someone can shed some light here..
I have a table XXX which has 4 columns with types char(2), varchar(5),...
|
by: Leon |
last post by:
Hi,
I have SQL DB table with a GUID Primary key. i used the C#
data form wizard to create a data form (though has a typed
dataset) with a Grid (the GUID column is not shown on the
grid). when the...
|
by: Dan Hartshorn |
last post by:
VS.NET 2003, C#, Windows Server 2003. I have a datagrid and I want the
last column to be either an EditCommandColumn or a template column,
depending on a value I have. The value changes for each...
|
by: Melson |
last post by:
Hi
I've created a GUID column of a table in ms sql server and bind the table to
a datagrid. How can I automatically create new GUID when i add new row in
the datagrid.
Pls help. Thanks.
...
|
by: Ying Lu |
last post by:
Hello,
Under mysql, we have "desc tablename" to get the detail information
about a table. My question is about to get column name, and column type
for a specific table under PostgreSQL through...
|
by: joe |
last post by:
how to access invisible column inside gridview
|
by: lee1212 |
last post by:
Hi
I use vb.Net to open an access db for my software and i have one issue that i don't know how to do, I need to change an exsiting Column that might have some records in it from Text(250) to Memo...
|
by: lucindaa |
last post by:
Hi EveryBody,
i have a problem in setting the column type of datagrid dynamically in C# .Net Windows App.
i am generating the grid automatically by assigning datasource of a datatable
the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |