Update statement won't work

[Slavan]

I have an update statement that I'm executing against Oracle database
from my C# code and it won't work.

UPDATE MenuCaptions SET Caption = N@Caption WHERE MenuId = @MenuId AND
CultureId = @CultureId

Caption is an nvarchar type. MenuId and CultureId are integers.
I get Exception with the following message: ORA-00933: SQL command not
properly ended.
It seems that problem lies in Caption column, because when I changed
command text to:

UPDATE MenuCaptions SET MenuId = @MenuId WHERE MenuId = @MenuId AND
CultureId = @CultureId

it would execute with no exceptions. Also, when I tried to execute
following statement from SQL editor in TOAD:

UPDATE MenuCaptions SET Caption = N'My Caption' WHERE MenuId = 1 AND
CultureId = 1

it would successfully update the record. I can't figure out why I am
getting exception.
Any help would be appreciated.

 

[Guest]

Are you using OracleParameter objects wih this update SQL Statement? Not
enough info in your post to really figure out exactly what's wrong. What's
the Provider?
Peter

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

|I have an update statement that I'm executing against Oracle database
| from my C# code and it won't work.
|
| UPDATE MenuCaptions SET Caption = N@Caption WHERE MenuId = @MenuId AND
| CultureId = @CultureId
|
| Caption is an nvarchar type. MenuId and CultureId are integers.
| I get Exception with the following message: ORA-00933: SQL command not
| properly ended.
| It seems that problem lies in Caption column, because when I changed
| command text to:

Can you post the actual code?

It seems you are missuing a " or a ' somewhere

 

[Slavan]

I'm using Relational classes that were developed by my company and
there are specific classes for different databases like Oracle, SQL
server and DB2. Parameters should not create any problems, because
they work fine with other queries. There is something wrong with the
query itself or with the way I'm passing Caption value.
Here's some code, although I don't think it will be very helpfull,
since Command object is just a wrapper to make calls to back end.
Anyways:

string UpdateMenuCaptionsQuery = "UPDATE MenuCaptions SET Caption =
N@Caption WHERE MenuId = @MenuId AND CultureId = @CultureId";

Database db = DatabaseFactory.CreateDatabase("Oracle");
DBCommandWrapper command =
db.GetSqlStringCommandWrapper(UpdateMenuCaptionsQuery);

command.AddInParameter("@Caption", DBDataTypeWrapper.UnicodeString,
caption.GetAttribute("value"));
command.AddInParameter("@MenuId", DBDataTypeWrapper.Int,
int.Parse(inputDoc.DocumentElement.GetAttribute("menuid")));
command.AddInParameter("@CultureId", DBDataTypeWrapper.Int,
int.Parse(caption.GetAttribute("cultureid")));

db.ExecuteNonQuery(command);

Hopefully this gives more info to my problem.

 

[Guest]

Have you tried removing the "N" from "SET Caption = N@Caption"?
It probably doesn't belong there, since you are using a typed parameter.
Peter

 

[Slavan]

Have you tried removing the "N" from "SET Caption = N@Caption"?
It probably doesn't belong there, since you are using a typed parameter.
Peter

Yes, that solved the problem. Thanks.