Undefined function 'Replace' in expression , Replace alternative?

Question

as read in this question : Undefined function 'Replace' in expression , I'm getting the error "Undefined function 'Replace' in expression" because "you aren't using the Access query engine at all", but what do I use as an alternative ? Apparantly "a combination of Iif, Instr" would work, but I can't find out a way to actually replace something with these.

All I want is to remove the spaces out of a value, how would I do this?

const string strSql = "SELECT TOP 15 HOOFDGROEP.HOOFDGROEP, SUBGROEP.SUBGROEP, Artikels.*" +
                                  " FROM (Artikels LEFT JOIN HOOFDGROEP ON Artikels.HOOFDGROEPID = HOOFDGROEP.ID)" +
                                  " LEFT JOIN SUBGROEP ON Artikels.SUBGROEPID = SUBGROEP.ID WHERE REPLACE(ArtikelNaam, ' ', '') LIKE  '%' + @ArtikelNaam + '%'";

            var objCommand = new OleDbCommand(strSql, _objConnection);
            objCommand.Parameters.Add("@ArtikelNaam", OleDbType.Char).Value = naamZoeker.Replace(" ", "");

Answer 1

If you download and install the

Microsoft Access Database Engine 2010 Redistributable

then you can use the following in the connection string for your OleDbConnection object...

Provider=Microsoft.ACE.OLEDB.12.0

...and the Replace() function will be available to your queries. For example, the following code works for me:

using (var conn = new OleDbConnection())
{
    conn.ConnectionString =
            @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=C:\__tmp\testData.accdb;";
    conn.Open();
    using (var cmd = new OleDbCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText =
            "UPDATE Table1 SET ProductType = Replace(ProductType, ' ', '')";
        cmd.ExecuteNonQuery();
    }
    conn.Close();
}

Note that you need to download and install the version of the Access Database Engine with the same "bitness" as your .NET application: 32-bit applications require the 32-bit version of the database engine and 64-bit applications require the 64-bit version of the database engine.

Answer 2

I had the same problem with REPLACE function, however, I fixed by changing my OleDb connection with an Odbc Connection as follows:

Dim dbConn As New System.Data.Odbc.OdbcConnection
dbConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db_name.accdb;Uid=Admin;Pwd=;"
dbConn.Open()

Dim objCmd As New System.Data.Odbc.OdbcCommand()
With objCmd
    .Connection = dbConn
    .CommandType = CommandType.Text
    .CommandText = "UPDATE table_name SET target_field = Replace(source_field, ' ', '') "
End With
objCmd.ExecuteNonQuery()

dbConn.Close()

I hope this helps.

Regards