locked
Getting ORA-00936: missing expression error in ADO.net insert RRS feed

  • Question

  • User-297332451 posted

    private void DoInsertNewInsuranceApplication()
    {
    try
    {

    OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DB_conn_TI"].ConnectionString);


    string cmdstr = "INSERT INTO TRAVEL_INSURANCE (" +
    " FIRSTNAME, " +
    " SURNAME, " +
    " STAFF_NO, " +
    " STUDENT_NO, " +
    " DEPARTMENT, " +
    " AUTHORISED, " +
    " DEPTHEAD_NAME, " +
    " AGE, " +
    " DEPARTURE_DATE, " +
    " RETURN_DATE, " +
    " TRIP_LENGTH, " +
    " AREA_VISITED, " +
    " COMMENTS, " +
    "TI_SEQ_NO," +
    " DATE_CREATED, " +
    " DATE_UPDATED, " +
    " USERNAME" +
    ")" +
    " values (" +
    " @FIRSTNAME, " +
    " @SURNAME, " +
    " @STAFF_NO, " +
    " @STUDENT_NO, " +
    " @DEPARTMENT, " +
    " @AUTHORISED, " +
    " @DEPTHEAD_NAME, " +
    " @AGE, " +
    " @DEPARTURE_DATE, " +
    " @RETURN_DATE, " +
    " @TRIP_LENGTH, " +
    " @AREA_VISITED, " +
    " @COMMENTS, " +
    // "@TI_SEQ_NO," +
    " @DATE_CREATED, " +
    " @DATE_UPDATED, " +
    " @USERNAME " +
    ")";


    conn.Open();
    //OracleCommand cmd = new OracleCommand(cmdstr, conn);

    OracleCommand cmd = conn.CreateCommand();
    cmd.CommandText = cmdstr;


    ucDeptsDropdownList ucDlDept = (ucDeptsDropdownList)FindControl("ucDeptsDropdownList");

    cmd.Parameters.Add("@FIRSTNAME", OracleDbType.Varchar2);
    cmd.Parameters.Add("@SURNAME", OracleDbType.Varchar2);
    cmd.Parameters.Add("@STAFF_NO", OracleDbType.Varchar2);
    cmd.Parameters.Add("@STUDENT_NO", OracleDbType.Varchar2);
    cmd.Parameters.Add("@DEPARTMENT", OracleDbType.Varchar2);
    cmd.Parameters.Add("@AUTHORISED", OracleDbType.Varchar2);
    cmd.Parameters.Add("@DEPTHEAD_NAME", OracleDbType.Varchar2);
    cmd.Parameters.Add("@AGE", OracleDbType.Varchar2);
    cmd.Parameters.Add("@DEPARTURE_DATE", OracleDbType.Date);
    cmd.Parameters.Add("@RETURN_DATE", OracleDbType.Date);
    cmd.Parameters.Add("@TRIP_LENGTH", OracleDbType.Varchar2);
    cmd.Parameters.Add("@AREA_VISITED", OracleDbType.Varchar2);
    cmd.Parameters.Add("@COMMENTS", OracleDbType.Varchar2);

    //cmd.Parameters.Add("@TI_SEQ_NO", OracleDbType.Date);
    cmd.Parameters.Add("@DATE_CREATED", OracleDbType.Date);
    cmd.Parameters.Add("@DATE_UPDATED", OracleDbType.Date);
    cmd.Parameters.Add("@USERNAME", OracleDbType.Varchar2);

    cmd.Parameters["@FIRSTNAME"].Value = name.Text;
    cmd.Parameters["@SURNAME"].Value = "";// RadioAppTypeList.SelectedValue;
    cmd.Parameters["@STAFF_NO"].Value = IDNo.Text;
    cmd.Parameters["@STUDENT_NO"].Value = ucDlDept.GetDeptSelectedList_Value();
    cmd.Parameters["@DEPARTMENT"].Value = ucDlDept.GetDeptSelectedList_Text();
    cmd.Parameters["@AUTHORISED"].Value = "";//RadioAuthList.SelectedValue;
    cmd.Parameters["@DEPTHEAD_NAME"].Value = headName.Text;
    cmd.Parameters["@AGE"].Value = "";//RadioAgeList.SelectedValue;
    cmd.Parameters["@DEPARTURE_DATE"].Value = Convert.ToDateTime(TextBoxDateFrom.Text);
    cmd.Parameters["@RETURN_DATE"].Value = Convert.ToDateTime(TextBoxDateTo.Text);
    cmd.Parameters["@TRIP_LENGTH"].Value = "No longer visible to users";
    cmd.Parameters["@AREA_VISITED"].Value = "global";
    cmd.Parameters["@COMMENTS"].Value = "Comments";
    //cmd.Parameters["@TI_SEQ_NO"].Value = "Comments";
    cmd.Parameters["@DATE_CREATED"].Value = Convert.ToDateTime(TextBoxDateFrom.Text);//RadioAreaList.SelectedValue;
    cmd.Parameters["@DATE_UPDATED"].Value = Convert.ToDateTime(TextBoxDateFrom.Text);
    cmd.Parameters["@USERNAME"].Value = "New";

    cmd.ExecuteNonQuery();
    TravelInsurancelog.Info("INSERT SUCCESS FOR PERSON_ID=[" + IDNo.Text+"]");
    }

    catch (SystemException ex)
    {
    TravelInsurancelog.Error("DoInsertNewInsuranceApplication SQL ERROR", ex);
    Console.WriteLine(ex.Message);
    }
    }

    I have the above code I am getting  'ORA-00936: missing expression' error 

    Thursday, July 30, 2015 4:49 AM

All replies

  • User269602965 posted

    Proper punctuation for BIND variables in Oracle  (no AT symbol, Oracle uses COLON)

    and of course the ORDER of the bind variable must match the order of their use in INSERT

    You can reduce the amount of escape punctuation by enclosing the SQL statement in XML then calling the value

    Example:

    using System;
    using System.Xml.Linq;
    
      public static void updateUnitsActiveFlag(decimal decQuantity, string strDescription, DateTime dateDateClosed)
      {
    	// Insert Quantity into new row Units table
    	string OraConnStr = ConfigurationManager.ConnectionStrings["OraConnStr"].ConnectionString;
    	try
    	{
    	  var SQL = System.Xml.Linq.XElement.Parse("<SQL> INSERT INTO {YOURSCHEMANAME}.UNITS(UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) VALUES(UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)</SQL>");
    	  using (OracleConnection conn = new OracleConnection(OraConnStr))
    	  {
    		using (OracleCommand cmd = new OracleCommand(SQL.Value, conn))
    		{
    		  cmd.Parameters.Clear();
    		  cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input);
    		  cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input);
    		  cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input);
    		  conn.Open();
    		  cmd.ExecuteNonQuery();
    		}
    	  }
    	}
    	catch (Exception ex)
    	{
    	  AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb");
    	}
    } 

    Imports System.Xml.Linq.XElement
    
      Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal, ByVal strDescription As String, ByVal dateDateClosed As DateTime)
        ' Insert Quantity into new row Units table'
        Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
        Try
          Dim SQL =
          <SQL>
          INSERT INTO {YOURSCHEMANAME}.UNITS
            (UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY) 
          VALUES 
            (UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)
          </SQL>
          Using conn As New OracleConnection(OraConnStr)
            Using cmd As New OracleCommand(SQL.Value, conn)
              cmd.Parameters.Clear()
              cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
              cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input)
              cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
              conn.Open()
              cmd.ExecuteNonQuery()
            End Using
          End Using
        Catch ex As Exception
          AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
        End Try
    End Sub

    Thursday, July 30, 2015 8:23 AM
  • User-297332451 posted

    How can I get the value of the sequence column after an insert in Oracle table

    I have the following insert code for oracle database , I am using ADO,net , can't use stored procedure , whats the way to do it with inline query

    OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["DB_conn_TI"].ConnectionString);
    //int RecordId = "TI_SEQ.NEXTVAL";
    string cmdstr = "INSERT INTO TRAVEL_INSURANCE (" +
    " FIRSTNAME, " +
    " SURNAME, " +
    " STAFF_NO, " +
    " STUDENT_NO, " +
    " DEPARTMENT, " +
    " AUTHORISED, " +
    " DEPTHEAD_NAME, " +
    " AGE, " +
    " DEPARTURE_DATE, " +
    " RETURN_DATE, " +
    " TRIP_LENGTH, " +
    " AREA_VISITED, " +
    " COMMENTS, " +
    " TI_SEQ_NO," +
    " DATE_CREATED, " +
    " DATE_UPDATED, " +
    " USERNAME" +
    ")" +
    " values (" +
    " :FIRSTNAME, " +
    " :SURNAME, " +
    " :STAFF_NO, " +
    " :STUDENT_NO, " +
    " :DEPARTMENT, " +
    " :AUTHORISED, " +
    " :DEPTHEAD_NAME, " +
    " :AGE, " +
    " :DEPARTURE_DATE, " +
    " :RETURN_DATE, " +
    " :TRIP_LENGTH, " +
    " :AREA_VISITED, " +
    " :COMMENTS, " +
    " TI_SEQ.NEXTVAL," +
    " :DATE_CREATED, " +
    " :DATE_UPDATED, " +
    " :USERNAME " +
    ");

    conn.Open();
    OracleCommand cmd = conn.CreateCommand();
    cmd.CommandText = cmdstr;
    ucDeptsDropdownList ucDlDept = (ucDeptsDropdownList)FindControl("ucDeptsDropdownList");

    cmd.Parameters.Add(":FIRSTNAME", OracleDbType.Varchar2);
    cmd.Parameters.Add(":SURNAME", OracleDbType.Varchar2);
    cmd.Parameters.Add(":STAFF_NO", OracleDbType.Varchar2);
    cmd.Parameters.Add(":STUDENT_NO", OracleDbType.Varchar2);
    cmd.Parameters.Add(":DEPARTMENT", OracleDbType.Varchar2);
    cmd.Parameters.Add(":AUTHORISED", OracleDbType.Varchar2);
    cmd.Parameters.Add(":DEPTHEAD_NAME", OracleDbType.Varchar2);
    cmd.Parameters.Add(":AGE", OracleDbType.Varchar2);
    cmd.Parameters.Add(":DEPARTURE_DATE", OracleDbType.Date);
    cmd.Parameters.Add(":RETURN_DATE", OracleDbType.Date);
    cmd.Parameters.Add(":TRIP_LENGTH", OracleDbType.Varchar2);
    cmd.Parameters.Add(":AREA_VISITED", OracleDbType.Varchar2);
    cmd.Parameters.Add(":COMMENTS", OracleDbType.Varchar2);
    cmd.Parameters.Add(":DATE_CREATED", OracleDbType.Date);
    cmd.Parameters.Add(":DATE_UPDATED", OracleDbType.Date);
    cmd.Parameters.Add(":USERNAME", OracleDbType.Varchar2);
    cmd.Parameters.Add(":myOutputParameter", OracleDbType.Decimal);
    string fullname = name.Text.ToString();
    string[] names = fullname.Split(' ');
    cmd.Parameters[":FIRSTNAME"].Value = names[0];
    cmd.Parameters[":SURNAME"].Value = names[1];

    //if (true == true)
    //{
    cmd.Parameters[":STAFF_NO"].Value = IDNo.Text;
    cmd.Parameters[":STUDENT_NO"].Value = IDNo.Text;
    //}
    //else
    //{
    // cmd.Parameters[":STAFF_NO"].Value = IDNo.Text;
    // cmd.Parameters[":STUDENT_NO"].Value = IDNo.Text;
    //}
    cmd.Parameters[":DEPARTMENT"].Value = ucDlDept.GetDeptSelectedList_Text();
    cmd.Parameters[":AUTHORISED"].Value = "";
    cmd.Parameters[":DEPTHEAD_NAME"].Value = headName.Text;
    cmd.Parameters[":AGE"].Value = "";
    cmd.Parameters[":DEPARTURE_DATE"].Value = Convert.ToDateTime(TextBoxDateFrom.Text);
    cmd.Parameters[":RETURN_DATE"].Value = Convert.ToDateTime(TextBoxDateTo.Text);
    cmd.Parameters[":TRIP_LENGTH"].Value = "No longer visible to users";
    cmd.Parameters[":AREA_VISITED"].Value = "global";
    cmd.Parameters[":COMMENTS"].Value = "Comments";
    cmd.Parameters[":DATE_CREATED"].Value = DateTime.Now;
    cmd.Parameters[":DATE_UPDATED"].Value = DateTime.Now;
    cmd.Parameters[":USERNAME"].Value = "New";
    cmd.ExecuteNonQuery();

    Friday, July 31, 2015 12:16 PM
  • User269602965 posted

    SELECT TI_SEQ.CURRVAL FROM DUAL;

    Friday, July 31, 2015 5:52 PM