|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Access and GUID Column TypeI 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:
> mySQL = "UPDATE ITEMS SET Title="New Record No. 1", WHERE KeyID = Try:> myGUID) \\\ 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 That doesn't compile...Error is Operator '&' is not defined for string or
system.GUID -- Show quoteHide quoteDennis 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 > > > Dennis wrote:
> That doesn't compile...Error is Operator '&' is not defined for Ah sorry, hadn't noticed that your myGUID variable was a GUID object and not> string or system.GUID a string. I see from your other posts that you've got it working though. :) -- (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/default.asp?url=/library/en-us/cpref/html/frlrfsystemguidclassnewguidtopic.asp Ken ---------------------- "Dennis" <Den***@discussions.microsoft.com> wrote in message I have created an access database table in code with the field "KeyId" as anews:EDE272F4-CE41-46C4-888B-D6419CD63E8C@microsoft.com... 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 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" -- Show quoteHide quoteDennis 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/default.asp?url=/library/en-us/cpref/html/frlrfsystemguidclassnewguidtopic.asp > > Ken > ---------------------- > "Dennis" <Den***@discussions.microsoft.com> wrote in message > news:EDE272F4-CE41-46C4-888B-D6419CD63E8C@microsoft.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 & "'") -- Show quoteHide quoteDennis 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/default.asp?url=/library/en-us/cpref/html/frlrfsystemguidclassnewguidtopic.asp > > Ken > ---------------------- > "Dennis" <Den***@discussions.microsoft.com> wrote in message > news:EDE272F4-CE41-46C4-888B-D6419CD63E8C@microsoft.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? -- Show quoteHide quoteDennis 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/default.asp?url=/library/en-us/cpref/html/frlrfsystemguidclassnewguidtopic.asp > > Ken > ---------------------- > "Dennis" <Den***@discussions.microsoft.com> wrote in message > news:EDE272F4-CE41-46C4-888B-D6419CD63E8C@microsoft.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:
> "UPDATE Items SET Title='new title' WHERE KeyID= '" & myGUID.ToString I can't see anything obviously wrong with that statement. Are you sure the> & "'" > > 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? 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. -- Show quoteHide quoteDennis 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 > > > 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 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! -- Show quoteHide quoteDennis in Houston "Cor Ligthert" wrote: > 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 > > > 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?
-- Show quoteHide quoteDennis 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? -- Show quoteHide quoteDennis 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 & "'") -- Show quoteHide quoteDennis 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.
-- Show quoteHide quoteDennis 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 > > > 
Other interesting topics
Does String mashal default to UnmanagedType.LPTStr
(newbie warning) vb.net, stdregprov, deletekey - Invalid cast Which to use, AllocCoTaskMem or AllocHGlobal use ADOX for this :-) Transparent Color in an Icon questions about VB.NET, and uses in education Is there code to convert a c# module to VB? Adding points to a bitmap Access DB questions score object property values against a decision rule created from from database table record values |
|||||||||||||||||||||||
