vb.netms-accessoledbdataadapter

Access db table update replace

I want to update an access database table 'TableName' by replacing strings in a column 'ColName' as the following:

 Dim Sql As String = "UPDATE [" & TableName & "] SET [" & ColName & "] = REPLACE([" & ColName & "], '" & OldPart & "', '" & NewPart & "')"  
 Dim Cmd As new OleDbCommand
 Cmd.Connection = My data bsae connection string
 Cmd.CommandType = CommandType.Text
 Cmd.CommandText = Sql
 Cmd.ExecuteNonQuery()

and I got the following error:

Data type mismatch in criteria expression.

Whats wrong with my code?

Solution

  • You probably have some empty fields, so try this:

    Dim Sql As String = "UPDATE [" & TableName & "] SET [" & ColName & "] = REPLACE([" & ColName & "], '" & OldPart & "', '" & NewPart & "') WHERE [" & ColName & "] Is Not Null"