Embarrassing simple..but I'm stumped

[dmjarby]

I'm using OleDbCommand to update records in an Access table as follows:

update Table1 set Field2=Replace(Field1,'value','newvalue')

This works in Access directly using a query but in ASP.NET, I get the error:

Undefined function 'Replace' in expression

 

[ca8msm]

You should do the Replace command first and pass the correct SQL statement to the OleDbCommand.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[dmjarby]

Can't I just have a SQL statement do the replace dynamically without iterating though the rows individually in my code? (the statement works in an MS Access query)

 

[ca8msm]

The OleDbCommand is not database specific so it doesn't know that the Microsoft Access Replace function exists which is why it works in the database itself but not from your page.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[dmjarby]

Thanks ca8msm, is there an OleDBCommand that I could use instead to achieve the same results?

 

[RTomes]

You can call other access queries using the "execute Query1" for your command object. Unfortionately the replace function causes an error. I don't see any work around for this other than using the old ADODB with a wrapper.

 

[ca8msm]

An alternative method would be to use the Microsoft Access Object Library and execute the query from within the file itself (but I'm not sure what speed implications this would have so you would have to try it out).

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.