C# - MS Access SQL using OleDbCommand

5 May 2007 10:06 AM

D2

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

5 May 2007 3:57 PM

Kerry Moorman

D2,

You could retrieve all the rows that have a FilePath containing "d:\\" into
a datatable, make the necessary changes and then update the database.

Kerry Moorman

Show quoteHide quote

"D2" wrote:

> 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
>
>

7 May 2007 4:35 AM

D2

On May 5, 8:57 pm, Kerry Moorman
<KerryMoor***@discussions.microsoft.com> wrote:
Show quoteHide quote

> D2,
>
> You could retrieve all the rows that have a FilePath containing "d:\\" into
> a datatable, make the necessary changes and then update the database.
>
> Kerry Moorman
>
> "D2" wrote:
> > 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

hmmm.. but thats a very long procedure.. i believe database will
handle the query better than me writing code to do this. Anyways, i hv
got the solution in a different thread. we can use Mid() method but
not substr, instr etc.

Cheers!
D2