C# - MS Access SQL using OleDbCommand

  • Thread starter Thread starter D2
  • Start date Start date
D

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
 
I believe Left and Mid work:

UPDATE [Files]
SET FilePath = 'c:\\' & Mid(FilePath, 5)
WHERE Left(FilePath, 4) = 'd:\\'

or

UPDATE [Files]
SET FilePath = 'c:\\' & Mid(FilePath, 5)
WHERE FilePath LIKE 'd:\\*'

(depending on how you're running the SQL, you might need to use % as the
wildcard rather than *)
 
I believe Left and Mid work:

UPDATE [Files]
SET FilePath = 'c:\\' & Mid(FilePath, 5)
WHERE Left(FilePath, 4) = 'd:\\'

or

UPDATE [Files]
SET FilePath = 'c:\\' & Mid(FilePath, 5)
WHERE FilePath LIKE 'd:\\*'

(depending on how you're running the SQL, you might need to use % as the
wildcard rather than *)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




Hi All,
Click to expand...
I am trying to run the following query from OleDbCommand object but
getting an exception.
UPDATE [Files] SET FilePath = REPLACE(FilePath, 'd:\\', 'c:\\')
Click to expand...
Exception is:
Undefined function 'REPLACE' in expression.
Click to expand...
Above query executes fine from within access but throws exception when
used from code.
Click to expand...
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?
Click to expand...
Cheers!
d2
Click to expand...
Click to expand...

thanks for your reply.
that seems to be working. I believe Mid, substr, instr etc all are vba
methods. How come substr, instr, substring etc dont work but Mid
works? Is there a list of methods that jet drivers would be happy
with?
 
The Jet Engine knows about certain functions, fortunately by the same name
as their VBA equivalent.

I'm sure there are lists somewhere, but I'm afraid I don't know where.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


D2 said:
thanks for your reply.
that seems to be working. I believe Mid, substr, instr etc all are vba
methods. How come substr, instr, substring etc dont work but Mid
works? Is there a list of methods that jet drivers would be happy
with?
I believe Left and Mid work:

UPDATE [Files]
SET FilePath = 'c:\\' & Mid(FilePath, 5)
WHERE Left(FilePath, 4) = 'd:\\'

or

UPDATE [Files]
SET FilePath = 'c:\\' & Mid(FilePath, 5)
WHERE FilePath LIKE 'd:\\*'

(depending on how you're running the SQL, you might need to use % as the
wildcard rather than *)





Hi All,
Click to expand...
I am trying to run the following query from OleDbCommand object but
getting an exception.
UPDATE [Files] SET FilePath = REPLACE(FilePath, 'd:\\', 'c:\\')
Click to expand...
Exception is:
Undefined function 'REPLACE' in expression.
Click to expand...
Above query executes fine from within access but throws exception when
used from code.
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top