Error during save to database cause data contain apostrophe [ ' ] 1

[nnuswantari]

I have any data that show in datagridview
ex:
column name = ID Number, name, Company, salary
1st data:{001, Miss NN, CV Mellow, 500}
2nd data:{002, Mr. I , CV Mafia, 800}
3th data:{003,Miss MK, GORILA'CC , 200}

I will save them to a table in Access database (eg: tbl_salary)
my structure's table is:
IDnumber : char(3)
name : char(50)
company : char(50)
salary : int

Dim CNString As String = ""
Dim DtConn As OdbcConnection
CNString = "Driver=XXX....;"

DtConn = New OdbcConnection(CNString

For i As Integer = 0 To Me.DataGridView1.RowCount - 1

   Dim id As String = Me.DataGridView1.Rows(i).Cells(0).Value
   Dim name As String = Me.DataGridView1.Rows(i).Cells(1).Value
   Dim comp As String =Me.DataGridView1.Rows(i).Cells(2).Value
   Dim sal As String = Me.DataGridView1.Rows(i).Cells(3).Value
               

   Dim cmdr As New OdbcCommand("INSERT INTO tbl_miss VALUES ('" & id & "','" & name & "','" & comp & "'," & sal & ")",  DtConn)
               
    DtConn.Open()
    cmdr.ExecuteNonQuery()
    DtConn.Close()

Next

But, I have problem during insert 3th data {003, Miss MK, GORILA'CC, 200}; because a field [company] contain character apostrophe (GORILA'CC) .

Error message:
ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''GORILA'CC',200)'.

What can I do to solve this problem?
Please tell me about it. Thanx

 

[vb5prgrmr]

Use the replace function and Replace(comp, "'", "''")...



Good Luck

 

[nnuswantari]

Thanks for ur tips. I have used function replace.
Replace (comp,"'"," ").
But field comp (GORILLA'CC) have been saved as (GORILLA CC) without apostrophe.
Can I save GORILLA'CC as GORILLA'CC?
Not replace this apostrophe with other character?

 

[vb5prgrmr]

Copy my replace above and you will see in the code editor I replace a single tick (') with two ticks ('')...



Good Luck

 

[nnuswantari]

Yes, I understand.
My problem was solved. Thanx