ORA-00936: missing expression

Question

0.00/5 (No votes)

See more:

My code is as follows:

C#

string sql = "Insert into Employee (EmpId,FirstName,LastName)" +
                         "values(@EmpID,'@FirstName','@LastName')";

           using (OracleConnection conn = new OracleConnection(connString))
           {
               conn.Open();

               using (OracleCommand cmd = new OracleCommand(sql, conn))
               {
                   cmd.Parameters.Add("@EmpID", OracleDbType.Int32).Value = empID;
                   cmd.Parameters.Add("@FirstName", OracleDbType.Varchar2).Value = firstName;
                   cmd.Parameters.Add("@LastName", OracleDbType.Varchar2).Value = lastName;



                   //cmd.Parameters.Add("@Dob", OracleDbType.Varchar2).Value = "2070.03.04";
                   //cmd.Parameters.Add("@Salary", OracleDbType.Int32).Value =333 ;
                   //cmd.Parameters.Add("@DeptId", OracleDbType.Int32).Value = 21;

                   cmd.ExecuteNonQuery();
               }
           }

//---------------------------------------------------------------------------------------------
On executing 'cmd.ExecuteNonQuery()' it results following error >> 'ORA-00936: missing expression'

i.e.

Server Error in '/' Application.
ORA-00936: missing expression
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Oracle.DataAccess.Client.OracleException: ORA-00936: missing expression

Source Error:

Line 409: //cmd.Parameters.Add("@DeptId", OracleDbType.Int32).Value = 21;
Line 410:
Line 411: cmd.ExecuteNonQuery();
Line 412: }
Line 413: }

Source File: G:\Practise\Default.aspx.cs Line: 411

Stack Trace:

[OracleException (0x80004005): ORA-00936: missing expression]
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) +1440
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) +69
Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() +6127
Practise._Default.InsertProduct(Int32 empID, String firstName, String lastName) in G:\Practise\Default.aspx.cs:411
Practise._Default.grdEmployee_RowCommand(Object sender, GridViewCommandEventArgs e) in G:\Practise\Default.aspx.cs:356
System.Web.UI.WebControls.GridView.OnRowCommand(GridViewCommandEventArgs e) +115
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +68
System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +121
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +125
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +167
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.272

Any sort of help is appreciated . Thanks in advance.

Posted 9-Jul-13 17:15pm

rAZHere

Updated 18-Jul-18 18:13pm

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 4679884 · Answer 1 · 2013-08-10T04:56:00

This cost me a lot of TIME.The @record_ID WORKED for Oracle on DELETE and INSERT.
However the UPDATE did not work until I changed the @ to a : ( I hope this helps someone else)
Below is a snippet. I was using RowUpdating on a gridview.

           <asp:sqldatasource id="ora_test_rec3" runat="server" xmlns:asp="#unknown">
            ConnectionString="<%$ ConnectionStrings:orcl_accrec %>"
            UpdateCommand="update test_rec3 set checkdate=:checkdate
               ,checkamt=:checkamt where   record_id=:record_id"
 
            DeleteCommand="Delete test_rec3 where record_id=@record_id" 
           InsertCommand="insert into test_rec3(invnum,checkdate,checkamt,currdate,record_id)

         values(@invnum,@checkdate,@checkamt,sysdate,seq_test_rec3.nextval);


</asp:sqldatasource>

To keep it the same, I went back and made it all ":" instead of "@"

Thanks,

Visar Uruqi · Answer 2 · 2013-11-09T01:36:00

Oracle has a different syntax for parameters than Sql-Server. So use : instead of @

C#

using(var con=new OracleConnection(connectionString))
{
   con.open();
   var sql = "insert into users values (:id,:name,:surname,:username)";

   using(var cmd = new OracleCommand(sql,con)
   {
      OracleParameter[] parameters = new OracleParameter[] {
             new OracleParameter("id",1234),
             new OracleParameter("name","John"),
             new OracleParameter("surname","Doe"),
             new OracleParameter("username","johnd")
      };

      cmd.Parameters.AddRange(parameters);
      cmd.ExecuteNonQuery();
   }
}

When using named parameters in an OracleCommand you must precede the parameter name with a colon

(:)

.

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters.aspx

rAZHere · Answer 3 · 2013-07-09T17:37:00

Solution 1

//Note : Incase of Oracele '@EmpID' Should be replaced with ':P_EmpID' in sql query. Otherwise it
// gives error in 'cmd.ExecuteNonQuery()' like 'ORA-00936: missing expression'

Posted 9-Jul-13 17:37pm

rAZHere

Member 13444132 · Answer 4 · 2018-07-18T18:14:00

string sql = "Insert into Employee (EmpId,FirstName,LastName)" +
"values(:EmpID,:FirstNam',:LastName)";

using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();

using (OracleCommand cmd = new OracleCommand(sql, conn))
{
cmd.Parameters.Add("EmpID", OracleDbType.Int32).Value = empID;
cmd.Parameters.Add("FirstName", OracleDbType.Varchar2).Value = firstName;
cmd.Parameters.Add("LastName", OracleDbType.Varchar2).Value = lastName;
cmd.ExecuteNonQuery();
}
}

ORA-00936: missing expression

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0