ASP.NET C# Missing Expression Error during Oracle insert

Question

May I start off by saying this is my 1st assigned database project in 10 years... and 1st time doing it in C#. I am "simply" trying to insert form data into an Oracle table using OleDB.

I keep getting "ORA-00936: missing expression". Below is my code... any idea what's missing?

public string getConnString()
        {
            //set the connection string from web config file
            return WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        }

        private void executeInsert(string EventType, string EventSubType, string DeptName, string EventDate, string Duration, string EventName, string EventAdd, string WardNo, string Program, string NumAtt, string StTime, string EndTime, string MngName, string RecKeeper)
        {
            OleDbConnection conn = new OleDbConnection(getConnString());
            string sql = "INSERT INTO APPS.CLV_EVENT_TRACK (EVENTTYPE, EVENTSUBTYPE, DEPTNAME, EVENTDATE, DURATION, EVENTNAME, EVENTADD, WARDNO, PROGRAM, NUMATT, STARTTIME, ENDTIME, MNGNAME, RECORDKEEPER) VALUES "
                + "(@EventType, @EventSubType, @DeptName, TO_DATE(@EventDate, 'Month dd, YYYY'), @Duration, @EventName, @EventAdd, @WardNo, @Program, @NumAtt, TO_DATE(@StTime, 'HH:MI:SS PM'), TO_DATE(@EndTime, 'HH:MI:SS PM'), @MngName, @RecKeeper)";

            try
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbParameter[] param = new OleDbParameter[14];
                param[0] = new OleDbParameter("@EventType", OleDbType.VarChar, 25);
                param[1] = new OleDbParameter("@EventSubType", OleDbType.VarChar, 80);
                param[2] = new OleDbParameter("@DeptName", OleDbType.VarChar, 240);
                param[3] = new OleDbParameter("@EventDate", OleDbType.Date);
                param[4] = new OleDbParameter("@Duration", OleDbType.Numeric);
                param[5] = new OleDbParameter("@EventName", OleDbType.VarChar, 80);
                param[6] = new OleDbParameter("@EventAdd", OleDbType.VarChar, 150);
                param[7] = new OleDbParameter("@WardNo", OleDbType.VarChar, 25);
                param[8] = new OleDbParameter("@Program", OleDbType.VarChar, 150);
                param[9] = new OleDbParameter("@NumAtt", OleDbType.Numeric);
                param[10] = new OleDbParameter("@StTime", OleDbType.Date);
                param[11] = new OleDbParameter("@EndTime", OleDbType.Date);
                param[12] = new OleDbParameter("@MngName", OleDbType.VarChar, 150);
                param[13] = new OleDbParameter("@RecKeeper", OleDbType.VarChar, 150);

                param[0].Value = EventType;
                param[1].Value = EventSubType;
                param[2].Value = DeptName;
                param[3].Value = EventDate;
                param[4].Value = Duration;
                param[5].Value = EventName;
                param[6].Value = EventAdd;
                param[7].Value = WardNo;
                param[8].Value = Program;
                param[9].Value = NumAtt;
                param[10].Value = StTime;
                param[11].Value = EndTime;
                param[12].Value = MngName;
                param[13].Value = RecKeeper;

                for (int i = 0; i < param.Length; i++)
                {
                    cmd.Parameters.Add(param[i]);
                }

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.ExecuteNonQuery();
            }

            catch (Exception ex) { throw ex; }

            finally
            {
                conn.Close();
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            var start = DateTime.Parse(txtStTime.Text);
            var end = DateTime.Parse(txtEndTime.Text);

            TimeSpan duration = end.Subtract(start);
            string meetDuration = duration.TotalMinutes.ToString();

            executeInsert(rbEventType.SelectedItem.Text, ddVolType.SelectedItem.Text,
                txtDept.Text, txtEventDate.Text, meetDuration, txtEventName.Text,
                txtEventAdd.Text, ddWard.SelectedItem.Value, txtSBPlan.Text, txtNumVol.Text,
                txtStTime.Text, txtEndTime.Text, txtEventMgr.Text, txtRecording.Text);
        }

UPDATE to code:

public string getConnString()
        {
            //set the connection string from web config file
            return WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        }

        private void executeInsert(string EventType, string EventSubType, string DeptName, string EventDate, string Duration, string EventName, string EventAdd, string WardNo, string Program, string NumAtt, string StTime, string EndTime, string MngName, string RecKeeper)
        {

            OleDbConnection conn = new OleDbConnection(getConnString());
            string sql = "INSERT INTO APPS.CLV_EVENT_TRACK (EVENTTYPE, EVENTSUBTYPE, DEPTNAME, EVENTDATE, DURATION, EVENTNAME, EVENTADD, WARDNO, PROGRAM, NUMATT, STARTTIME, ENDTIME, MNGNAME, RECORDKEEPER) VALUES "
                + "(@EventType, @EventSubType, @DeptName, TO_DATE(@EventDate, 'Month dd, YYYY'), @Duration, @EventName, @EventAdd, @WardNo, @Program, @NumAtt, TO_DATE(@StTime, 'HH:MI:SS PM'), TO_DATE(@EndTime, 'HH:MI:SS PM'), @MngName, @RecKeeper)";

            try
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;

                    cmd.Parameters.AddRange(new OleDbParameter[]
                    {
                        new OleDbParameter("@EventType", EventType),
                        new OleDbParameter("@EventSubType", EventSubType),
                        new OleDbParameter("@DeptName", DeptName),
                        new OleDbParameter("@EventDate", EventDate),
                        new OleDbParameter("@Duration", Duration),
                        new OleDbParameter("@EventName", EventName),
                        new OleDbParameter("@EventAdd", EventAdd),
                        new OleDbParameter("@WardNo", WardNo),
                        new OleDbParameter("@Program", Program),
                        new OleDbParameter("@NumAtt", NumAtt),
                        new OleDbParameter("@StTime", StTime),
                        new OleDbParameter("@EndTime", EndTime),
                        new OleDbParameter("@MngName", MngName),
                        new OleDbParameter("@RecKeeper", RecKeeper)
                    });

                    cmd.ExecuteNonQuery();
                }
            }

            catch (Exception ex) { throw ex; }

            finally
            {
                conn.Close();
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {

            var start = DateTime.Parse(txtStTime.Text);
            var end = DateTime.Parse(txtEndTime.Text);

            TimeSpan duration = end.Subtract(start);
            string meetDuration = duration.TotalMinutes.ToString();

            executeInsert(rbEventType.SelectedItem.Text, ddVolType.SelectedItem.Text,
                txtDept.Text, txtEventDate.Text, meetDuration, txtEventName.Text,
                txtEventAdd.Text, ddWard.SelectedItem.Value, txtSBPlan.Text, txtNumVol.Text,
                txtStTime.Text, txtEndTime.Text, txtEventMgr.Text, txtRecording.Text);
        }

also shouldn't OleDbParameter[] param = new OleDbParameter[14]; actually be OleDbParameter[] param = new OleDbParameter[13]; Since the array is 0 based..? — MethodMan
– MethodMan, Commented Sep 19, 2012 at 19:05
Have you tried executing that SQL statement directly in the Oracle DB Tools (i.e. not via C#). I assume the issue is your SQL statement is incorrect. — Dylan Smith
– Dylan Smith, Commented Sep 19, 2012 at 19:09
You can greatly reduce the amount of code by using some different parameters construction. i.e: myCommand.Parameters.Add("@name", value); . The use of a micro ORM like PetaPoco should avoid you a lot of database related code hassle. Have you tried to remove the TO§DATE() conversions ? — Larry
– Larry, Commented Sep 19, 2012 at 19:09
Jane: I get that error in my catch where I throw my exception. DJ... if I decrease the OleDbParameter to 13 then I'll receive an index out of range error since the max index would be less than the list itself. — Psykopup
– Psykopup, Commented Sep 19, 2012 at 19:10

Psykopup · Accepted Answer · 2012-09-19 22:07:58Z

What a lesson to learn. Apparently an ORACLE parameter has to be presented as a "?"

So, my final code... that actually successfully inserts a record is:

public string getConnString()
        {
            //set the connection string from web config file
            return WebConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        }

        private void executeInsert()    
        {
            OleDbConnection conn = new OleDbConnection(getConnString());
            string sql = "INSERT INTO APPS.CLV_EVENT_TRACK (EVENTTYPE, EVENTSUBTYPE, DEPTNAME, EVENTDATE, DURATION, EVENTNAME, EVENTADD, WARDNO, PROGRAM, NUMATT, STARTTIME, ENDTIME, MNGNAME, RECORDKEEPER) ";
            sql += "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            try
            {
                var start = DateTime.Parse(txtStTime.Text);
                var end = DateTime.Parse(txtEndTime.Text);

                TimeSpan duration = end.Subtract(start);
                string meetDuration = duration.TotalMinutes.ToString();

                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = rbEventType.SelectedItem.Text;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = ddVolType.SelectedItem.Text;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtDept.Text;
                    cmd.Parameters.Add("?", OleDbType.Date).Value = txtEventDate.Text;
                    cmd.Parameters.Add("?", OleDbType.Numeric).Value = meetDuration;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtEventName.Text;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtEventAdd.Text;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = ddWard.SelectedItem.Value;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtSBPlan.Text;
                    cmd.Parameters.Add("?", OleDbType.Numeric).Value = txtNumVol.Text;
                    cmd.Parameters.Add("?", OleDbType.Date).Value = txtStTime.Text;
                    cmd.Parameters.Add("?", OleDbType.Date).Value = txtEndTime.Text;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtEventMgr.Text;
                    cmd.Parameters.Add("?", OleDbType.VarChar).Value = txtRecording.Text;

                    cmd.ExecuteNonQuery();
                }
            }

            catch (Exception ex) { throw ex; }

            finally
            {
                conn.Close();
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            executeInsert();
        }

Dang, OleDb question marks! how did I not think of it sooner? Congrats! By the way, why dont you use the Oracle native drivers (OracleClient namespace)? Is the use of OleDb required by some multiple database compatability requirements?
I saw that when I was choosing a driver however, according to Visual Studio 2010, it's been deprecated.

Collectives™ on Stack Overflow

ASP.NET C# Missing Expression Error during Oracle insert

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related