ExcelFile data load to DataGridView and replace

[SoftBa]

Hi, I have one Win Form app to load Excel data into DataGridView. I want to store DataGridView data into MS Access database. Everithing works great, but in some cases inside ExcelFile I have single quote mark, which breaks SQL Syntax. My question is, How to find & replace "'" with "I" on a first step (while populating DataGridView).?

This is the part of my code

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            openfiledialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
            openfiledialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
            If openfiledialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK Then
                Dim fi As New IO.FileInfo(openfiledialog.FileName)
                Dim filename As String = openfiledialog.FileName
                excel = fi.FullName
                conn = New OleDbConnection("provider=microsoft.ACE.OLEDB.12.0;Data source=" + excel + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1';")
                dta = New OleDbDataAdapter("Select * from [List1$] ", conn)
                dts = New DataSet
                dta.Fill(dts, "[List1$]")
                DataGridView1.DataSource = dts
                DataGridView1.DataMember = "[List1$]"
                conn.Close()

            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            conn.Close()
            Exit Sub
        End Try
    End Sub

[AndreRet]

You should try and loop through each row adn catch the characters you want to replace rather than just copying the entire file over via your list box.

[SoftBa]

Something like this

For i As Int32 = 0 To DataGridView2.Rows.Count - 1

            Dim val = DataGridView2.Rows(i).Cells(5).Value.ToString().Replace("'", "I")

        Next

[AndreRet]

Makes sense, see if the below will work for you. Keep in mind I did not do vb code for quite some time but this will give you the general idea -

For i As Int32 = 0 To DataGridView2.Rows.Count - 1
    Dim xCol = not sure what to use here for coloumn(Cells) count...
    Dim valcol = DataGridView2.Rows(i).Cells(xCol).Value
    if valcol =  "'"
        DataGridView2.Rows(i).Cells(xCol).Value.ToString().Replace("'", "I")
    End If        
Next