none
Apostrophe in Filter RRS feed

  • Question

  • Ok I found this thread here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=69805&SiteID=1

     

    that is very similar to the problem that I am having except, the author had control over the filter string.  I am pulling a string from a Database where I do not have write privliages, because it is a third party database.  Can I use parameters in DataRowView Objects.  If anyone has any suggestions please let me know, Thank you

     

    I am using VS2005 VB.NET

    Wednesday, June 20, 2007 9:08 PM

Answers

  • Your code in this case should be like

     

    _DataView.RowFilter = "col1 = '" & Replace(strName.Trim,"'","''") & "'"

     

    It will double all sinle quotes escaping them

     

    Friday, June 22, 2007 2:46 AM
    Moderator

All replies

  • What is exact issue you have? If you pulled data from the database, then everything is in your control and you could double single quotes in your Select method of the DataTable. It has nothing to do with the database and any restrictions for it.

    If you need to call actual SELECT SQL statement against database and pass value with single quotes, then you need to use parameterized query and pass value as parameter. See next sample how to pass values as parameters. It is sample for SQL Server, but shows you an idea how it supposes to be constructed

    http://support.microsoft.com/kb/308049/en-us

     

    Thursday, June 21, 2007 10:33 AM
    Moderator
  •  

    Thank you for your response.

     

    Code Snippet

    _DataView.RowFilter = "col1 = '" & strName.Trim & "'"

     

    the problem is occasionally, strName has an apotrophe.  For example;

     

    strName = 'Smith'

    strName = 'Green'

    strName = 'O'Connor' <-- Error on this line

     

    I have parameters on my update statement for this same reason.  I have an issue with the dataview object's rowfilter method.  If I have to write a function to check for the apostrophe then add a second next to it, I heard this remedies the problem, then I will.  However time is very critical and the more functions I call the slower my program becomes.  I need to process about 300,000 records in under 10 minutes The record count will be higher at the end of the month, and higher at the end of the year.  Hope that is what you needed to assist me.

     

    Thursday, June 21, 2007 1:43 PM

  • This is a problem when using code side filter methods. You would need to write a function to escape the single quote and any other special characters that could cause the filter statement to be invalid.

    I would highly recommend using the method that Val is suggesting. Not only do you eliminate the single quote, et al issue but the performance of your app should improve as well.

    Thursday, June 21, 2007 1:56 PM
  • Your code in this case should be like

     

    _DataView.RowFilter = "col1 = '" & Replace(strName.Trim,"'","''") & "'"

     

    It will double all sinle quotes escaping them

     

    Friday, June 22, 2007 2:46 AM
    Moderator
  • VMazur said:

    Your code in this case should be like

     

    _DataView.RowFilter = "col1 = '" & Replace(strName.Trim,"'","''") & "'"

     

    It will double all sinle quotes escaping them

     


    Could you please tell if it works for C#? I have no success so far. Do you have working example?

    Thank you!
    Monday, December 15, 2008 7:41 AM
  • Here is example for C# 

     _DataView.RowFilter = "col1 = '"strName.Trim().Replace("'","''") + "'";

     

     


    Val Mazur (MVP) http://www.xporttools.net
    Monday, December 15, 2008 10:54 AM
    Moderator