dapi

Hi All,

I am trying to run the following query from OleDbCommand object but getting an exception.
UPDATE [Files] SET FilePath = REPLACE(FilePath, 'd:\\', 'c:\\')

Exception is:
Undefined function 'REPLACE' in expression.

Above query executes fine from within access but throws exception when used from code.

I understand that the Replace() method is available from access but not from oledb. I'm just wondering what is the best approach to achieve the same results(as given by above query) from code


Cheers!
d2


Re: .NET Framework Data Access and Storage MS Access SQL using OleDbCommand

Merin Gazell

try

UPDATE Files SET FilePath='d:\\' & right(FilePath,5)





Re: .NET Framework Data Access and Storage MS Access SQL using OleDbCommand

Paul P Clement IV


The following should take into consideration the length of the path and whether the replace criteria is actually in the string:

UPDATE [Files] SET FilePath = IIf(InStr([FilePath],'d:\\'),'d:\\' + Right([FilePath],LEN([FilePath])-4),[FilePath])






Re: .NET Framework Data Access and Storage MS Access SQL using OleDbCommand

dapi

Thanks to both of you for your kind replies.

I have implemented it using the Mid(). But I dont find a solution that will work for both Access and SQL server. As of now i'm working with access, but i may need to port it to sql server. I was looking at a solution where i won't have to make any changes in the code, connection string change could take care or the database.

Thanks.
D2




Re: .NET Framework Data Access and Storage MS Access SQL using OleDbCommand

Paul P Clement IV


To support both SQL dialects that will limit you to the string functions Right, Left and Len.