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
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
Contributor
3532 Points
1348 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:
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();
Contributor
3532 Points
1348 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;