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

Answer 3

I commented it only at last in my other answer: my VBA code unfortunately does't work with OleDbCommand, but isn't this a solution for you:

1. For I guess that they had the same problem, see: Stackoverflow: Exception when trying to execute “REPLACE” against MS Access => They workarounded it with INSTR / MID... maybe this could help you?

2. And there is an additional solution: See: Codeguru: Replace doesnt work...

Does this help you?

Greetings

Adelphos

Answer 4

If you can put VBA code in a Access Module, you can use this code in Access VBA to replace a String with an other String instead of using buliltin Access Function Replace:

Public Function TransformString(ByVal ToTransformStr As String, ByVal ReplaceStr As String, ByVal ToReplaceStr As String) As String
  Dim i As Long, sTmpString As String

  sTmpString = ""
  For i = 1 To Len(ToTransformStr)
    If Mid$(ToTransformStr, i, Len(ReplaceStr)) = ReplaceStr Then
      sTmpString = sTmpString & ToReplaceStr
      If Len(ReplaceStr) > 1 Then
        i = i + Len(ReplaceStr) - 1
      End If
    Else
      sTmpString = sTmpString & Mid$(ToTransformStr, i, 1)
    End If
  Next i

  TransformString = sTmpString

End Function

Test this code with:

Sub test()

Dim test As String

test = TransformString(" xyzABC ABCxyz ", " ", "")

End Sub

This is the same as:

test = Replace(" xyzABC ABCxyz ", " ", "")

Result is in both cases:

"xyzABCABCxyz"

And then this should work (with additional escaped " as \"):

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 TransformString(ArtikelNaam, \" \", \"\") LIKE  '%' + @ArtikelNaam + '%'";

Greetings,

Adelphos

Answer 5

I suspect the problem is that you are using SQL Server syntax instead of MS Access syntax. I think this is the MS Access version:

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 & "*";

REPLACE() is an MS Access function, but maybe it doesn't recognize it because of problems with the quotes.

Answer 6

Per my experience, I think Gord Thompson's answer is correct. I had Microsoft Access Database Engine 2007 ( there is only 32 bit version) and 64 bit of Microsoft Access Database Engine 2010 Redistributable installed. When I published my .net Click Once App in 32 bits, I had the subjected error, when I published in 64 bits, things went well. I tried further, uninstalled Microsoft Access Database Engine 2007 ( there is only 32 bit version) and 64 bit of Microsoft Access Database Engine 2010 Redistributable, installed 32 bit Microsoft Access Database Engine 2010 Redistributable, and then published my App in 32 bit again, everything was in order.