Resolved - A lesson learned re: dataset exception handling

[Slow_Learner]

I'm working with a dataset with a number of tables that contain unique-constraint fields. The data I'm working with sometimes contains single or double quotes. At first I was testing to see if a given unique already existed in the table before trying to insert it, but I noticed that the single quotes (probably the double quotes too) can cause the .Select method to generate an exception. I wasn't so concerned about that (for my project I just care if the unique stays unique) so as an experiment I tried omitting the .Select and just trying to insert the record anyhow, trusting the exception handler to catch and discard the error.

WOW WAS THAT DUMB! A process that took 1-2 seconds ballooned into 20+ seconds! The overhead caused by generating and discarding the exceptions is many many times worse than rather than testing for the presence of a unique. Possibly this is excruciatingly obvious to an experienced programmer, but this stuff is pretty tough to learn without a teacher. Anyhow I just thought I'd pass this along in case anyone has a similar task.

Now I guess I have to find an alternative to .Select that won't choke on quote marks. Maybe I'll have better luck with DataRowCollection.Find.

Update: .Find works great for enforcing uniqueness, but I wish there was a better way to handle .Select (I have occasions where I need to return multiple records when searching). I have no idea how to phrase a .Select parameter when the value I'm .Selecting against contains a quote mark. Anyone have an idea?



Aha! Thank you Mr. Forum Search!

http://www.vbforums.com/showthread.p...ect+quote+mark

[hoshantm]

I think you should replace each quote mark by two quote marks.

[Slow_Learner]

Yeah, something along the lines of:

                If lvwCategories.SelectedItems(0).Text.IndexOf("'") > -1 Then
                    Dim strTemp As String
                    strTemp = Replace(lvwCategories.SelectedItems(0).Text, "'", "''")
                    For Each objRow In dsData.Tables("Table").Select("Category = '" + _
                    strTemp + "'")

...ended up doing the trick.