locked
Undefined function 'replace' in expression error RRS feed

  • Question

  • User400145976 posted

    I want to be able to use replace function or at least accomplish the results of replacing database field in search request (select statement). Basically be able to search for part numbers not requiring proper placement of hyphens, periods or spaces. Someone has had to have figured out a way around the Undefined function 'replace' in expression error.

    I feel like I’m coding in circles. I need to access a dBaseIII dbf file that is updated in a local file daily through access in an intranet web application. The thought of deleting records in SQL database and importing the dBaseIII file into a SQL database on a daily basis does not entertain me. I have tried OLDB connections using vfpoledb.1, Jet.OLEDB.4.0, ACE.OLEDB.12.0 and System DSN connections. I can get select statements to work in MS Access directly, and in SQL 2005 management studio directly, but get variations of System.Data.OleDb.OleDbException: Undefined function 'replace' in expression errors when I try to use select statements in AccessDataSources, and SQLDataSources. Should I figure out how to use LinqDataSource or what?

    To follow is what I have already attempted:

    dBaseIII dbf file: ConnectionString = "Provider=vfpoledb.1;Data Source=C:\dbfiles\dBase3file.dbf;Exclusive=false;Nulls=false" This works: CommandText = "Select TOP 250  IIf(pnum='',dBase3file.item,dBase3file.pnum) AS PartNr, item, pndesc  from dBase3file WHERE pnum like '239A%' ORDER BY pnum"

    This does not work: CommandText = "Select TOP 250  IIf(pnum='',dBase3file.item,dBase3file.pnum) AS PartNr, item, pndesc  from dBase3file WHERE replace(pnum,'-','') like '239A%' ORDER BY pnum" Exception Details: System.Data.OleDb.OleDbException: File 'replace.prg' does not exist.  

    ************************************************* MS Access 2003 mdb file using linked table of dBaseIII dbf file connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dbfiles\MSaccess03.mdb" This works: CommandString = "Select pnum AS PARTNR, item, pndesc from dBase3file where pnum like '239a%s-%'"

    This does not work: CommandString = "Select pnum AS PARTNR, item,pndesc from dBase3file where replace(pnum,'-','') like '239a%s-%'" Exception Details: System.Data.OleDb.OleDbException: Undefined function 'replace' in expression.

    ************************************************* MS Access 2003 mdb file using Query of linked table of dBaseIII dbf file referencing Public Function stripchar(PartNR) connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dbfiles\MSaccess03.mdb"

    This does not work: CommandString = "select * from SearchPartNoDash WHERE PartNR like '239%'" Exception Details: System.Data.OleDb.OleDbException: Undefined function 'stripchar' in expression.

    ***************************************

    connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\dbfiles\MSaccess03.mdb"

    This works: CommandString = "Select pnum AS PARTNR, item, pndesc from dBase3file where pnum like '239a%s-%'"

    This does not work: CommandString = "Select pnum AS PARTNR, item, pndesc from dBase3file where replace(pnum,'-','') like '239a%s-%'" Exception Details: System.Data.OleDb.OleDbException: Undefined function 'replace' in expression.

    ****************************************** SQL command in works using Linked Server in 2005 management studio

    SELECT TOP 5 * FROM OPENQUERY(SBTVFP, 'SELECT pnum, item, pndesc from dBase3file') WHERE replace(replace(pnum,'-',''),' ','') like '239as1%'

    connectionString="Dsn=dbaseDSN" providerName="System.Data.Odbc"

    Does work with System DSN connection (One time only- then refresh or subsequent attempts gets No data found) SelectCommand="SELECT [PNUM], [ITEM] FROM [dBase3file] WHERE ([PNUM] LIKE '%' + ? + '%') ORDER BY [PNUM]">

    Does not work with System DSN connection SelectCommand="SELECT TOP 5 * FROM OPENQUERY(SBTVFP, 'SELECT pnum, item, pndesc from dBase3file') WHERE replace(replace(pnum,'-',''),' ','') like '239as1%'" Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC dBase Driver] Syntax error in FROM clause.

    Friday, February 1, 2013 11:24 AM

Answers

  • User400145976 posted

    I finally found the solution. I had to change Replace() function with STRTRAN() function. STRTRAN(cSearched, cExpressionSought [, cReplacement]

    I was only able to get it to work with the vfpoledb.1 provider. It would not work with Jet.OleDb.4.0 or ACE.OLEDB.12.0 connections.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 2, 2013 10:31 PM

All replies

  • User-1716253493 posted
    looks like dbase dont have replace function like sql. www.dbase.com/help/Xbase/IDH_XBASE_REPLACE.htm
    Friday, February 1, 2013 11:36 AM
  • User400145976 posted

    I do not want to update or replace contents of data field, simply use the equivalent of the SQL replace() function in my Select statement / command.

    Friday, February 1, 2013 11:55 AM
  • User400145976 posted

    I finally found the solution. I had to change Replace() function with STRTRAN() function. STRTRAN(cSearched, cExpressionSought [, cReplacement]

    I was only able to get it to work with the vfpoledb.1 provider. It would not work with Jet.OleDb.4.0 or ACE.OLEDB.12.0 connections.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, February 2, 2013 10:31 PM