ORA-00936: missing expression using Entity framework update statement

Question

I'm new to using ORACLE with entity framework 5. Trying a simple update statement which won't work and I m getting an error "ORA-00936: missing expression"

Entities context = new Entities();

var Description="UpdateTesting";
var Id = "1";

string UpdateSqlString = @"Update SOURCE Set DESCRIPTION={0} where SOURCEID={1}";

int RowsUpdated = context.Database.ExecuteSqlCommand(UpdateSqlString, Description, Id);

context.SaveChanges();

I have also tried the following but still getting the same error

 Entities context = new Entities();

 var Description = "UpdateTesting";
 var Id = "1";

 var sql = @"Update SOURCE Set DESCRIPTION = @DESCRIPTION WHERE SOURCEID = @Id";

 int RowsUpdated = context.Database.ExecuteSqlCommand(sql, new OracleParameter("DESCRIPTION", Description),
                                                                  new OracleParameter("Id", Id));
 context.SaveChanges();

I have now tried with the following syntax but nothing happens after the ExecuteSqlCommand and the application probably goes in some not-ending loop

var Description="UpdateTesting";
var SOURCEId = "1";

var sql = "Update SOURCE SET DESCRIPTION = :Description WHERE SOURCEID = :SOURCEId";

 int RowsUpdated=context.Database.ExecuteSqlCommand(
            sql,
            new OracleParameter(":Description", Description),
            new OracleParameter(":SOURCEId", SOURCEId));

I can provide SQL Create table script If that would help resolve this.

Any ideas? thanks

ermm. Tried it but getting this error now "Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'Oracle.DataAccess.Client.OracleParameter'." — rumi, Jan 20 '15 at 12:49
Getting slightly different error now "Unable to cast object of type 'System.Data.OracleClient.OracleParameter' to type 'Oracle.DataAccess.Client.OracleParameter'" — rumi, Jan 20 '15 at 12:55
Tried with this namespace now "using Oracle.DataAccess.Client;" but now back to the original error "ORA-00936: missing expression" — rumi, Jan 20 '15 at 12:58

score 3 · Answer 1 · answered Sep 07 '16 at 20:28

Have you try this? without colon

    var Description="UpdateTesting";
var SOURCEId = "1";

var sql = "Update SOURCE SET DESCRIPTION = :Description WHERE SOURCEID = :SOURCEId";

 int RowsUpdated=context.Database.ExecuteSqlCommand(
            sql,
            new OracleParameter("Description", Description),
            new OracleParameter("SOURCEId"   , SOURCEId));

Rob · Answer 2 · 2015-03-26T10:57:53.707

2

Rather than using {0} use :0 instead.

Entities context = new Entities();

var Description="UpdateTesting";
var Id = "1";

string UpdateSqlString = @"Update SOURCE Set DESCRIPTION=:0 where SOURCEID=:1";

int RowsUpdated = context.Database.ExecuteSqlCommand(UpdateSqlString, Description, Id);

context.SaveChanges();

edited Mar 26 '15 at 10:57

answered Mar 25 '15 at 22:19

Rob

85
8

VMAtm · Answer 3 · 2015-01-20T15:32:33.657

0

I think that you have to add ' symbol for your update script as the SQL doesn't recognize it as string, like this:

Update SOURCE Set DESCRIPTION='{0}' where SOURCEID={1}

Try to edit other expressions accordingly.

Update:

May be, your problem is in table name (SOURCE may be a reserved keyword, and doesn't be recognized as table name) . Try to wrap it in "", like this:

Update "SOURCE" Set DESCRIPTION='{0}' where SOURCEID={1}

edited Jan 20 '15 at 15:32

answered Jan 20 '15 at 14:17

VMAtm

27,943
17
79
125

thanks for the answer but I m getting still the same error after putting `'` – rumi Jan 20 '15 at 14:20
I have tried the following PL SQL in ORACLE query editor and it works `Update SOURCE Set DESCRIPTION='SOme Updates Testing' where SOURCEID=1;` – rumi Jan 20 '15 at 14:33
In the application, the following line does bring the returns `var Rows = context.SOURCEs.ToList();` so there are no issues in the connection or premissions – rumi Jan 20 '15 at 14:35
@Learner Did you try the `"` quatation? – VMAtm Jan 20 '15 at 15:28
Yes I did but that does not make any difference `string UpdateSqlString = @"Update ""SOURCE"" Set DESCRIPTION={0} where SOURCEID={1}";` – rumi Jan 20 '15 at 15:31

ORA-00936: missing expression using Entity framework update statement

3 Answers3