Data is not inserting oracle table using ODP.NET through asp.net

Question

I am inserting record in Oracle table using ODP.NET Oracle.DataAccess.Client throug Asp.net c#. The oracle table is tblGroup and GROUPNAME varchar2,GROUPDETAILS varchar2 is column name but data is not inserting.I am using following code

 public void insertRecord(string StudentgroupName, string groupDetails) 
            {
                using (OracleConnection con = GetConnection())
                {

                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = con; //assigning connection to command
                    cmd.CommandType = CommandType.Text; //representing type of command
                    cmd.CommandText = "INSERT INTO tblGroup (GROUPNAME,GROUPDETAILS) values(@GROUPNAME,@GROUPDETAILS)";

                    //adding parameters with value
                    cmd.Parameters.Add("@GROUPNAME", StudentgroupName);
                    cmd.Parameters.Add("@GROUPDETAILS", groupDetails);

                    con.Open(); //opening connection
                    cmd.ExecuteNonQuery();


                }
            }

and got following error

Oracle.DataAccess.Client.OracleException ORA-00936: missing expression at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at SchoolsManagementSystem.DAL.insertRecord(String StudentgroupName, String groupDetails) in d:\c#\SchoolsManagementSystem\SchoolsManagementSystem\App_Code\DAL.cs:line 85 at SchoolsManagementSystem.Group.btnSubmit_Click(Object sender, EventArgs e) in d:\c#\SchoolsManagementSystem\SchoolsManagementSystem\Group.aspx.cs:line 26

Guide me How can I solve my problem

mrjoltcola · Accepted Answer · 2014-06-29 19:27:18Z

3

In your SQL statement, use a colon prefix to indicate bind parameter, not @:

INSERT INTO t (COL1) values(:param1)

Using the @ symbol works ok for SQL Server, for not for Oracle.

Also, your bind statement needs to just use the name "GROUPNAME", no prefix character @ or : needed in the parameters.Add() call.

Like so:

cmd.CommandText =
   "INSERT INTO tblGroup (GROUPNAME,GROUPDETAILS) values(:GROUPNAME, :GROUPDETAILS)";

cmd.Parameters.Add("GROUPNAME", StudentgroupName);
cmd.Parameters.Add("GROUPDETAILS", groupDetails);

edited Jun 29, 2014 at 19:27

answered Jun 29, 2014 at 19:19

mrjoltcola

20.9k1 gold badge62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Steve Over a year ago

Interesting note on prefix not needed. I have no Oracle to test, but it raise an error or the prefix is simple ignored by the engine?

mrjoltcola Over a year ago

The Oracle SQL parser uses the colon to indicate bind parameters, whether in SQL, PL/SQL or host languages. The ADO driver may or may not complain, but Oracle will. The @ isn't a valid character for an identifier or a bind variable, so possibly you'll get a parse error. I just know it fails, because I have a few database tools that I sell that work on Oracle and SQL Server and I have to use conditional code between the two.

Steve Over a year ago

Thank you for your kindness, mine was just curiosity.

Collectives™ on Stack Overflow

Data is not inserting oracle table using ODP.NET through asp.net

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related