Blog

Getting ORA-00936: missing expression error in ADO.net insertRSS

3 replies

Last post Jul 31, 2015 05:52 PM by Lannie

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
ROHITJGC
Member

1 Points

181 Posts

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

Jul 30, 2015 04:50 AM|ROHITJGC|LINK

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

Lannie

Contributor

3402 Points

1320 Posts

Re: Getting ORA-00936: missing expression error in ADO.net insert

Jul 30, 2015 08:23 AM|Lannie|LINK

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

- Reply
ROHITJGC
Member

1 Points

181 Posts

Re: Getting ORA-00936: missing expression error in ADO.net insert

Jul 31, 2015 12:16 PM|ROHITJGC|LINK

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();
- Reply
Lannie
Contributor

3402 Points

1320 Posts

Re: Getting ORA-00936: missing expression error in ADO.net insert

Jul 31, 2015 05:52 PM|Lannie|LINK

SELECT TI_SEQ.CURRVAL FROM DUAL;

‹ Previous Thread|Next Thread ›