locked
Unable to insert into oracle database - ORA-00911 error RRS feed

  • Question

  • User-567785390 posted

    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



    Monday, July 26, 2010 11:33 AM

All replies

  • User1224194097 posted

    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


    Tuesday, July 27, 2010 12:36 AM
  • User-567785390 posted

    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




    Tuesday, July 27, 2010 5:30 AM
  • User-537372776 posted

    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;
    Friday, March 16, 2012 5:05 AM
  • User269602965 posted

    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
      

    Sunday, March 18, 2012 9:22 PM
  • User-1407477457 posted

    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.

    Sunday, March 18, 2012 9:31 PM