Unable to insert into oracle database - ORA-00911 error

Dear all,

I am trying to insert values into oracle database using visual studio 2010 express edition. Please find the below code I used:

protected void Button1_Click1(object sender, EventArgs e)
    {
        try
        {
            OracleConnection con = new OracleConnection();
            con.ConnectionString = "User ID=mpcst;Password=mpcst;Data Source=localhost/orcl";
            string commandtext = "Insert into mpcst.metadata(BOUNDARY,THEME,SOCIOECO,LAYERNAME,PROJECT,MAPSCALE,LAYERENTITY,MAPBASESYSTEM,MAPPROJECTION,MAPDATUM,MAPMAJORAXIS,MAPMINORAXIS,MAPELLIPTICITY,POSITIONACCURACY,THEMEACCURACY,ELEVATIONACCURACY,OBJECTTYPE,CATEGORY,LAYERTYPE,LAYERGIS,LAYERDESC,LAYERCITATION,OPERATINGSYS,LAYERCLASSIFICATION,LAYERCLASSDESC,LAYERSPREAD,PROCPOSITIONACCURACY,PROCTHEMEACCURACY,PROCELEVATIONACCURACY,SURVEYYEAR,MAPPINGYEAR,DIGITIZEYEAR,AGENCYNAME,FOCALPERSON,ADDRESS,PHONE,FAX,EMAIL,WEBSITE,COLLABORATEAGENCY,COST) values (?BOUNDARY,?THEME,?SOCIOECO,?LAYERNAME,?PROJECT,?MAPSCALE,?LAYERENTITY,?MAPBASESYSTEM,?MAPPROJECTION,?MAPDATUM,?MAPMAJORAXIS,?MAPMINORAXIS,?MAPELLIPTICITY,?POSITIONACCURACY,?THEMEACCURACY,?ELEVATIONACCURACY,?OBJECTTYPE,?CATEGORY,?LAYERTYPE,?LAYERGIS,?LAYERDESC,?LAYERCITATION,?OPERATINGSYS,?LAYERCLASSIFICATION,?LAYERCLASSDESC,?LAYERSPREAD,?PROCPOSITIONACCURACY,?PROCTHEMEACCURACY,?PROCELEVATIONACCURACY,?SURVEYYEAR,?MAPPINGYEAR,?DIGITIZEYEAR,?AGENCYNAME,?FOCALPERSON,?ADDRESS,?PHONE,?FAX,?EMAIL,?WEBSITE,?COLLABORATEAGENCY,?COST)";
            OracleCommand cmd = new OracleCommand(commandtext, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("?BOUNDARY", OracleDbType.Varchar2).Value = DropDownList1.SelectedValue.ToString();
            cmd.Parameters.Add("?THEME", OracleDbType.Varchar2).Value = DropDownList2.SelectedValue.ToString();
            cmd.Parameters.Add("?SOCIOECO", OracleDbType.Varchar2).Value = DropDownList3.SelectedValue.ToString();
            cmd.Parameters.Add("?LAYERNAME", OracleDbType.Varchar2).Value = TextBox1.Text;
            cmd.Parameters.Add("?PROJECT", OracleDbType.Varchar2).Value = TextBox4.Text;
            cmd.Parameters.Add("?MAPSCALE", OracleDbType.Varchar2).Value = TextBox3.Text;
            cmd.Parameters.Add("?LAYERENTITY", OracleDbType.Varchar2).Value = TextBox5.Text;
            cmd.Parameters.Add("?MAPBASESYSTEM", OracleDbType.Varchar2).Value = TextBox6.Text;
            cmd.Parameters.Add("?MAPPROJECTION", OracleDbType.Varchar2).Value = TextBox7.Text;
            cmd.Parameters.Add("?MAPDATUM", OracleDbType.Varchar2).Value = TextBox8.Text;
            cmd.Parameters.Add("?MAPMAJORAXIS", OracleDbType.Varchar2).Value = TextBox9.Text;
            cmd.Parameters.Add("?MAPMINORAXIS", OracleDbType.Varchar2).Value = TextBox10.Text;
            cmd.Parameters.Add("?MAPELLIPTICITY", OracleDbType.Varchar2).Value = TextBox11.Text;
            cmd.Parameters.Add("?POSITIONACCURACY", OracleDbType.Varchar2).Value = TextBox12.Text;
            cmd.Parameters.Add("?THEMEACCURACY", OracleDbType.Varchar2).Value = TextBox13.Text;
            cmd.Parameters.Add("?ELEVATIONACCURACY", OracleDbType.Varchar2).Value = TextBox14.Text;
            cmd.Parameters.Add("?OBJECTTYPE", OracleDbType.Varchar2).Value = TextBox15.Text;
            cmd.Parameters.Add("?CATEGORY", OracleDbType.Varchar2).Value = TextBox16.Text;
            cmd.Parameters.Add("?LAYERTYPE", OracleDbType.Varchar2).Value = TextBox17.Text;
            cmd.Parameters.Add("?LAYERGIS", OracleDbType.Varchar2).Value = TextBox18.Text;
            cmd.Parameters.Add("?LAYERDESC", OracleDbType.Varchar2).Value = TextBox19.Text;
            cmd.Parameters.Add("?LAYERCITATION", OracleDbType.Varchar2).Value = TextBox20.Text;
            cmd.Parameters.Add("?OPERATINGSYS", OracleDbType.Varchar2).Value = TextBox21.Text;
            cmd.Parameters.Add("?LAYERCLASSIFICATION", OracleDbType.Varchar2).Value = TextBox22.Text;
            cmd.Parameters.Add("?LAYERCLASSDESC", OracleDbType.Varchar2).Value = TextBox23.Text;
            cmd.Parameters.Add("?LAYERSPREAD", OracleDbType.Varchar2).Value = TextBox24.Text;
            cmd.Parameters.Add("?PROCPOSITIONACCURACY", OracleDbType.Varchar2).Value = TextBox25.Text;
            cmd.Parameters.Add("?PROCTHEMEACCURACY", OracleDbType.Varchar2).Value = TextBox26.Text;
            cmd.Parameters.Add("?PROCELEVATIONACCURACY", OracleDbType.Varchar2).Value = TextBox27.Text;
            cmd.Parameters.Add("?SURVEYYEAR", OracleDbType.Varchar2).Value = TextBox28.Text;
            cmd.Parameters.Add("?MAPPINGYEAR", OracleDbType.Varchar2).Value = TextBox29.Text;
            cmd.Parameters.Add("?DIGITIZEYEAR", OracleDbType.Varchar2).Value = TextBox30.Text;
            cmd.Parameters.Add("?AGENCYNAME", OracleDbType.Varchar2).Value = TextBox31.Text;
            cmd.Parameters.Add("?FOCALPERSON", OracleDbType.Varchar2).Value = TextBox32.Text;
            cmd.Parameters.Add("?ADDRESS", OracleDbType.Varchar2).Value = TextBox33.Text;
            cmd.Parameters.Add("?PHONE", OracleDbType.Varchar2).Value = TextBox34.Text;
            cmd.Parameters.Add("?FAX", OracleDbType.Varchar2).Value = TextBox35.Text;
            cmd.Parameters.Add("?EMAIL", OracleDbType.Varchar2).Value = TextBox36.Text;
            cmd.Parameters.Add("?WEBSITE", OracleDbType.Varchar2).Value = TextBox37.Text;
            cmd.Parameters.Add("?COLLABORATEAGENCY", OracleDbType.Varchar2).Value = TextBox38.Text;
            cmd.Parameters.Add("?COST", OracleDbType.Varchar2).Value = TextBox39.Text;
            con.Open();
            int result = cmd.ExecuteNonQuery();
            Label1.Text = "Data Saved";
            con.Close();
        }
        catch (Exception ex)
        {
            Label1.Text = ex.ToString();
        }


    }

Am getting the error:

Oracle.DataAccess.Client.OracleException ORA-00911:
invalid character at
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode,
OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object
src, String procedure) at
Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode,
OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx*
pOpoSqlValCtx, Object src) at
Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at
_Default.Button1_Click1(Object sender, EventArgs e) in c:\Documents and
Settings\Sharat\My Documents\Visual Studio

Could anyone tell me where the problem is.

I thank all in advance,
Sharat.
2010\WebSites\MetadataAdmin\Default.aspx.cs:line 70

Re: Unable to insert into oracle database - ORA-00911 error

Go to line number 70 and see what you are passing as the parameter value.

It looks like you are not escaping single quote or having some special character in there.

if you have a single quote, replace that single quote with two single quotes in your TextBox.

txtValue.Text.Replace("'","'')

That should work I guess, but replace for string values and see if it clears the error message

Re: Unable to insert into oracle database - ORA-00911 error

Thank you for reply I also tried by replacing that single quote with two single quotes in your TextBox. txtValue.Text.Replace("'","'') but still am getting the error.

Please find the below code I tried:

protected void Button1_Click1(object sender, EventArgs e)
    {
        try
        {
            OracleConnection con = new OracleConnection();
            con.ConnectionString = "User ID=mpcst;Password=mpcst;Data Source=192.168.1.10/orcl";
            //string connstr = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=mpcst;Password=mpcst;Data Source=192.168.1.10/orcl";
            string commandtext = "Insert into mpcst.metadata(BOUNDARY,THEME,SOCIOECO,LAYERNAME,PROJECT,MAPSCALE,LAYERENTITY,MAPBASESYSTEM,MAPPROJECTION,MAPDATUM,MAPMAJORAXIS,MAPMINORAXIS,MAPELLIPTICITY,POSITIONACCURACY,THEMEACCURACY,ELEVATIONACCURACY,OBJECTTYPE,CATEGORY,LAYERTYPE,LAYERGIS,LAYERDESC,LAYERCITATION,OPERATINGSYS,LAYERCLASSIFICATION,LAYERCLASSDESC,LAYERSPREAD,PROCPOSITIONACCURACY,PROCTHEMEACCURACY,PROCELEVATIONACCURACY,SURVEYYEAR,MAPPINGYEAR,DIGITIZEYEAR,AGENCYNAME,FOCALPERSON,ADDRESS,PHONE,FAX,EMAIL,WEBSITE,COLLABORATEAGENCY,COST) values (:BOUNDARY,:THEME,:SOCIOECO,:LAYERNAME,:PROJECT,:MAPSCALE,:LAYERENTITY,:MAPBASESYSTEM,:MAPPROJECTION,:MAPDATUM,:MAPMAJORAXIS,:MAPMINORAXIS,:MAPELLIPTICITY,:POSITIONACCURACY,:THEMEACCURACY,:ELEVATIONACCURACY,:OBJECTTYPE,:CATEGORY,:LAYERTYPE,:LAYERGIS,:LAYERDESC,:LAYERCITATION,:OPERATINGSYS,:LAYERCLASSIFICATION,:LAYERCLASSDESC,:LAYERSPREAD,:PROCPOSITIONACCURACY,:PROCTHEMEACCURACY,:PROCELEVATIONACCURACY,:SURVEYYEAR,:MAPPINGYEAR,:DIGITIZEYEAR,:AGENCYNAME,:FOCALPERSON,:ADDRESS,:PHONE,:FAX,:EMAIL,:WEBSITE,:COLLABORATEAGENCY,:COST)";
           

            OracleCommand cmd = new OracleCommand(commandtext, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(":BOUNDARY", OracleDbType.Varchar2).Value = DropDownList1.SelectedValue.ToString();
            cmd.Parameters.Add(":THEME", OracleDbType.Varchar2).Value = DropDownList2.SelectedValue.ToString();
            cmd.Parameters.Add(":SOCIOECO", OracleDbType.Varchar2).Value = DropDownList3.SelectedValue.ToString();
            cmd.Parameters.Add(":LAYERNAME", OracleDbType.Varchar2).Value = TextBox1.Text;
            cmd.Parameters.Add(":PROJECT", OracleDbType.Varchar2).Value = TextBox4.Text;
            cmd.Parameters.Add(":MAPSCALE", OracleDbType.Varchar2).Value = TextBox3.Text;
            cmd.Parameters.Add(":LAYERENTITY", OracleDbType.Varchar2).Value = TextBox5.Text;
            cmd.Parameters.Add(":MAPBASESYSTEM", OracleDbType.Varchar2).Value = TextBox6.Text;
            cmd.Parameters.Add(":MAPPROJECTION", OracleDbType.Varchar2).Value = TextBox7.Text;
            cmd.Parameters.Add(":MAPDATUM", OracleDbType.Varchar2).Value = TextBox8.Text;
            cmd.Parameters.Add(":MAPMAJORAXIS", OracleDbType.Varchar2).Value = TextBox9.Text;
            cmd.Parameters.Add(":MAPMINORAXIS", OracleDbType.Varchar2).Value = TextBox10.Text;
            cmd.Parameters.Add(":MAPELLIPTICITY", OracleDbType.Varchar2).Value = TextBox11.Text;
            cmd.Parameters.Add(":POSITIONACCURACY", OracleDbType.Varchar2).Value = TextBox12.Text;
            cmd.Parameters.Add(":THEMEACCURACY", OracleDbType.Varchar2).Value = TextBox13.Text;
            cmd.Parameters.Add(":ELEVATIONACCURACY", OracleDbType.Varchar2).Value = TextBox14.Text;
            cmd.Parameters.Add(":OBJECTTYPE", OracleDbType.Varchar2).Value = TextBox15.Text;
            cmd.Parameters.Add(":CATEGORY", OracleDbType.Varchar2).Value = TextBox16.Text;
            cmd.Parameters.Add(":LAYERTYPE", OracleDbType.Varchar2).Value = TextBox17.Text;
            cmd.Parameters.Add(":LAYERGIS", OracleDbType.Varchar2).Value = TextBox18.Text;
            cmd.Parameters.Add(":LAYERDESC", OracleDbType.Varchar2).Value = TextBox19.Text;
            cmd.Parameters.Add(":LAYERCITATION", OracleDbType.Varchar2).Value = TextBox20.Text;
            cmd.Parameters.Add(":LAYERCLASSIFICATION", OracleDbType.Varchar2).Value = TextBox22.Text;
            cmd.Parameters.Add(":LAYERCLASSDESC", OracleDbType.Varchar2).Value = TextBox23.Text;
            cmd.Parameters.Add(":LAYERSPREAD", OracleDbType.Varchar2).Value = TextBox24.Text;
            cmd.Parameters.Add(":PROCPOSITIONACCURACY", OracleDbType.Varchar2).Value = TextBox25.Text;
            cmd.Parameters.Add(":PROCTHEMEACCURACY", OracleDbType.Varchar2).Value = TextBox26.Text;
            cmd.Parameters.Add(":PROCELEVATIONACCURACY", OracleDbType.Varchar2).Value = TextBox27.Text;
            cmd.Parameters.Add(":SURVEYYEAR", OracleDbType.Varchar2).Value = TextBox28.Text;
            cmd.Parameters.Add(":MAPPINGYEAR", OracleDbType.Varchar2).Value = TextBox29.Text;
            cmd.Parameters.Add(":DIGITIZEYEAR", OracleDbType.Varchar2).Value = TextBox30.Text;
            cmd.Parameters.Add(":AGENCYNAME", OracleDbType.Varchar2).Value = TextBox31.Text;
            cmd.Parameters.Add(":FOCALPERSON", OracleDbType.Varchar2).Value = TextBox32.Text;
            cmd.Parameters.Add(":ADDRESS", OracleDbType.Varchar2).Value = TextBox33.Text;
            cmd.Parameters.Add(":PHONE", OracleDbType.Varchar2).Value = TextBox34.Text;
            cmd.Parameters.Add(":FAX", OracleDbType.Varchar2).Value = TextBox35.Text;
            cmd.Parameters.Add(":EMAIL", OracleDbType.Varchar2).Value = TextBox36.Text;
            cmd.Parameters.Add(":WEBSITE", OracleDbType.Varchar2).Value = TextBox37.Text;
            cmd.Parameters.Add(":COLLABORATEAGENCY", OracleDbType.Varchar2).Value = TextBox38.Text;
            cmd.Parameters.Add(":COST", OracleDbType.Varchar2).Value = TextBox39.Text;
            con.Open();
            cmd.ExecuteNonQuery();
            Label1.Text = "Data Saved";
            con.Close();
        }
        catch (Exception ex)
        {
            Label1.Text = ex.ToString();
        }

Still am getting the error:

Oracle.DataAccess.Client.OracleException ORA-01008: not all variables bound at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at _Default.Button1_Click1(Object sender, EventArgs e) in c:\Documents and Settings\Sharat\My Documents\Visual Studio 2010\WebSites\MetadataAdmin\Default.aspx.cs:line 68

Am unable find the error in the code. Could anyone help me for fixing this error.

Thanks & Regards,

Sharat

Re: Unable to insert into oracle database - ORA-00911 error

Hello.

Concerning the second exception (ORA-01008), in case you did not find out the answear by now, you missed OPERATINGSYS, between LAYERCITATION and LAYERCLASSIFICATION.

Be sure to add the parameters in the right order. Something like:

cmd.Parameters.Add(":LAYERCITATION", OracleDbType.Varchar2).Value = TextBox20.Text;
cmd.Parameters.Add("OPERATINGSYS", OracleDbType.Varchar2).Value = TextBox21.Text; //":" are not mandatory in the parameter name
cmd.Parameters.Add(":LAYERCLASSIFICATION", OracleDbType.Varchar2).Value = TextBox22.Text;

Re: Unable to insert into oracle database - ORA-00911 error

Get rid of colons in add parm

string commandtext = "Insert into mpcst.metadata(BOUNDARY) values (:BOUNDARY)";

cmd.Parameters.Add("BOUNDARY", OracleDbType.Varchar2).Value = DropDownList1.SelectedValue.ToString();

use the COLON only to signify the BIND variable in the Insert statement VAlues section

example

Imports System.Xml.Linq.XElement
  
    Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal)
      ' Insert Quantity into new row Units table'
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("{YourOraConnStrName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
        INSERT INTO {YOURSCHEMANAME}.UNITS
          (UNITS_SEQ, QUANTITY, DATE_CLOSED)
        VALUES
          (UNIT_SEQ.NextVal, :BindVarQuantity, :DateClosed)
        </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("DateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
' you exception handling here
      End Try
  End Sub

Re: Unable to insert into oracle database - ORA-00911 error

sansan

It looks like you are not escaping single quote or having some special character in there.

if you have a single quote, replace that single quote with two single quotes in your TextBox.

txtValue.Text.Replace("'","'')

That should work I guess, but replace for string values and see if it clears the error message

Not true when you are using query parameters such as the OP is doing. For the OP, to troubleshoot the problem, start commenting out all lines until you are adding just a single field. If that works, start uncommenting them one at a time until you find which one causes the error.