By Charles R Hankey - 2/19/2009
I am using guid keys.
In a custom data source BO for a report I am putting a filter on a child cursor
Basically I want the filter to be "cclientkey =" and then the guid as a string - but of course in quotes
_policydisplay.SourceBO.Filter = "cclientkey = " & Me.ckey.ToString
doesn't work as the guid is not surrounded by quote in the resulting filter string.
I googled and tried things like
Dim quotes As Char = ChrW(34)
Dim filtstr As String = "cclientkey = " & quotes & Me.ckey.ToString & quotes
_policyDisplay.SourceBO.Filter = filtstr
Dim filtstr As String = "cclientkey = """ & Me.ckey.ToString & """"
_policyDisplay.SourceBO.Filter = filtstr
etc and no joy
Then reverted to the old Foxpro way
Dim filtstr as String = "cclientkey = '" & me.ckey.toString & "'" ( using single quotes to surround the guid )
and it works.
But a single quote to surround a string will throw and error. Is this some magic the old Foxers at Microfour have made possible? Never saw any solution like this in VB land? And what other ways are there to do this that work?
|
By Edhy Rijo - 2/19/2009
Charles R Hankey (02/19/2009)
...And what other ways are there to do this that work?How about this one: Dim filtstr As String = String.Format("cclientkey = '{0}'", Me.cKey)
|
By Charles R Hankey - 2/19/2009
Thanks, Eddy
Yes I like that syntax better.
But once again, the guid is surrounded by single quotes. Are single quotes valid in VB .net or is that just something made possible by Microfour in setting the filter. I realize that in code actually sent in a sql server query the single quote would be appropriate but I was surprised to find it working in within the BO. ( now I need to test it with square brackets ... ) 
No complaints, of course, as there is a lot to like about some of the tricks we learned in Fox.
|
By Charles R Hankey - 2/19/2009
FWIW - the square brackets don't work 
|
By Edhy Rijo - 2/19/2009
Charles R Hankey (02/19/2009)
But once again, the guid is surrounded by single quotes.Charles, I am sorry, but I don't quite understand the problem here, does the version of the code I gave you worked? The value of the GUID is of String Type and does not have a single quote in it, the single quote would be needed to construct the BO.Filter value, but using the String.Format would take care of it.
|
By Charles R Hankey - 2/19/2009
The code you gave me and works fine. I was just wondering why a single quote works surrounding the guid as I have usually found VB doesn't like single quotes (except as part of a string passed to a sql backend) and doesn't see them as an alternative to doublequote. You know what I mean about Foxpro, where you can use single quotes, double quotes or square brackets so putting quotes inside a string can be done a lot of different ways.
Since a BO filter is all within the BO I am surprised the VB compiler sees the single around the guid as defining a string where you could not say
Dim str as string = 'This is a string'
|
By Edhy Rijo - 2/19/2009
Now I understand, and yes you are right, unfortunately I don't know the answer, of course coming from VFP I also have been bit with this situation before. Talking about VFP I had to release an update to one of my applications done in VFP and while coding I often used the single quote instead of the "*" to comment a line realizing that I was not coding in VB.NET  , also I expected the IDE to properly format the code after finish a line without having to run the Beautify command. It was a bit frustrating going back to the VFP IDE.
|
By Charles R Hankey - 2/19/2009
I know what you mean. I do most of my billable work in VFP and while I like a lot of things about VFP (at least in the context of my framework) the IDE is definitely not one of them. The debugger feels pretty primative too.
Even at my beginner level in .net i am really seeing the power that is available. Definitely not the RAD environment i have in VFE/VFP but I have a feeling that as my knowledge grows - and Strataframe matures - the result will be a much more powerful programming environment.
( I am about to test some of my training theories by posting a Sharpshooter walk through that I hope will save others some of the frustration I have experienced in the last couple weeks )
|
By Keith Chisarik - 2/19/2009
Interesting. Single quotes bit me hard going from VFP to VB.NET (like most things), until I realized I had to replace single quotes in my SQL strings with double quotes for TSQL to interpret correctly, perhaps somewhere in the bowels of the datalayer the SF boys do this before the filter gets passed to the underlying datatable of the BO? Replace(<<some string>>, "'", "''")
|
By Greg McGuffey - 2/20/2009
I don't think has anything to do with either SQL Server or SF. This is an ADO.NET thing. I'm pretty sure (I haven't looked it up in source code yet, but the SF guys don't tend to build stuff that already exists) they just set the filter on the current view of the datatable. So, I think something like this is actually going on when you set the filter:
'-- This is the BO syntax
myBo.Filter = "stringColumn = 'somefilter value'"
'-- Under the covers, Expanded to make it clear
Dim view As System.Data.DataView = myBo.CurrentDataView
view.RowFilter = "stringColumn = 'somefilter value'"
Based on this, single quotes makes sense, because MS knows that you'll be setting the value of the filter in code and that means that single quotes will work better. Here is info on the RowFilter property of the DataView:
http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx
Again, I think this is what's going on. I didn't get a chance to look at the source, but this is what I'd expect.
|
By Charles R Hankey - 2/20/2009
Interesting. Makes perfect sense of course but I just never saw anything in VB docs that said single quotes was an option for anything as a string delimiter ( as I said in Foxpro we could use single or double quotes or square brackets to fiddle with any kinds of strings ) and when I googled for a solution nobody mentioned single quotes.
This makes things a lot easier.
|
By Charles R Hankey - 2/20/2009
Just regoogled and found you're correct dataview.rowfilter uses single quotes as a reserved character ( also means if the single quote - or apostrophe - is part of the string you need to double it to get the desired result
dataview1.rowfilter = "searchfield = 'Mike''s Apartment'"
|
By Charles R Hankey - 2/21/2009
Apropos of this discussion I just ran across this snippet re dates in filter strings so thought I'd stick it here for the benefit of anyone (like me) who may be searching later and need it.
filterON = String.Format("EnterDateTime >= #{0}# and EnterDateTime <= #{1}#", begDate.Date,endDate.Date)
|
By Greg McGuffey - 2/23/2009
Thanks for the additional info Charles! 
These sorts of things can be hard to figure out when moving to .NET. Nice to have em in one place.
|