Pulling data from a DataGridView for SQL filtering. How do I get around an apostrophe?

Question

This is my filtering method.

Public Function Filter(data As DataTable)
    Try
        Dim str As String = "[Name] IN ("
        Dim bs As New BindingSource()
        bs.DataSource = dtMain
        bs.Filter = dtMain.DefaultView.RowFilter
        Dim dv As DataView = CType(bs.List, DataView)
        Dim dt As DataTable = dv.ToTable()

        If dt.Rows.Count = 0 Then
            data.DefaultView.RowFilter = "[Name] = 'NULL'"
            Return 0
        End If

        For Each dr As DataRow In dt.Rows
            str = str + "'" & dr.Item("Name") & "', "
        Next
        str = str.Substring(0, str.Length - 2)
        str = str + ")"
        data.DefaultView.RowFilter = "(" & str & ")"
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
    Return 0
End Function

The problem is one of the names in the DataGridView has an apostrophe in it. So the filter hits the apostrophe, thinks it's its own value, then throws a syntax error because there's no comma after it. I don't know how I can get around this and I can't manipulate the data.

Answer 1

I'm not positive, but I'd wager the correct way to handle this (if it behaves the same way a database insertion does), is to escape the single quote with another single quote. So in your DataRow iterator, a minor change like this might be in order:

For Each dr As DataRow In dt.Rows
  str = str + "'" & dr.Item("Name").Replace("'", "''") & "', "
Next