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
– rumi, Commented Jan 20, 2015 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
– rumi, Commented Jan 20, 2015 at 12:55
Tried with this namespace now "using Oracle.DataAccess.Client;" but now back to the original error "ORA-00936: missing expression" — rumi
– rumi, Commented Jan 20, 2015 at 12:58

Francois · Accepted Answer · 2016-09-07 20:28:45Z

3

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

answered Sep 7, 2016 at 20:28

Francois

434 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rob · Accepted Answer · 2015-03-26 10:57:53Z

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, 2015 at 10:57

answered Mar 25, 2015 at 22:19

Rob

858 bronze badges

Comments

VMAtm · Accepted Answer · 2015-01-20 15:32:33Z

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, 2015 at 15:32

answered Jan 20, 2015 at 14:17

VMAtm

28.4k17 gold badges85 silver badges133 bronze badges

5 Comments

rumi Over a year ago

thanks for the answer but I m getting still the same error after putting '

rumi Over a year ago

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 Over a year ago

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

VMAtm Over a year ago

@Learner Did you try the " quatation?

rumi Over a year ago

Yes I did but that does not make any difference string UpdateSqlString = @"Update ""SOURCE"" Set DESCRIPTION={0} where SOURCEID={1}";

Collectives™ on Stack Overflow

ORA-00936: missing expression using Entity framework update statement

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related