Undefined function 'replace' in expression error : The Official Microsoft ASP.NET Forums

Reply

doublehaul

Member

15 Points

Undefined function 'replace' in expression error

Feb 01, 2013 03:24 PM|LINK

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.