9
I'm trying to execute an SQL query against a MS Access database containing a "REPLACE" function:
UPDATE MyTable
SET MyColumn = REPLACE(MyColumn, 'MyOldSubstring', 'MyNewSubstring')
WHERE Id = 10;
If I run this query from inside MS Access (the application) it works fine. But when I try to run it from my application an exception is thrown.
The exception:
System.Data.OleDb.OleDbException was unhandled
Message="Undefined function 'REPLACE' in expression."
Source="Microsoft Office Access Database Engine"
ErrorCode=-2147217900
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
...
Why do I get this exception?
More info:
- My application is a WPF application
- I'm using .NET 3.5
- I run MS Access 2007
- My connectionstring is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyFolder\MyDatabase.accdb"
My database access code looks something like this, where I'll just pass in the mentioned SQL as a string:
public void ExecuteNonQuery(string sql)
{
OleDbCommand command = new OleDbCommand(sql);
OleDbConnection connection = new OleDbConnection(ConnectionString);
command.Connection = connection;
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
connection.Close(