ASP.NET

Unable to insert into oracle database - ORA-00911 error RSS

5 replies

Last post Mar 19, 2012 01:31 AM by Dan Bracuk

Unable to insert into oracle database - ORA-00911 error

Jul 26, 2010 03:33 PM|LINK

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

- Reply
sansan

All-Star

53942 Points

8147 Posts

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

Jul 27, 2010 04:36 AM|LINK

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

Santhosh

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

Jul 27, 2010 09:30 AM|LINK

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

- Reply
OlteanuBogda...

Member

2 Points

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

Mar 16, 2012 09:05 AM|LINK
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;
```
- Edited by olteanubogdan on Mar 16, 2012 09:06 AM

Lannie

Contributor

3724 Points

726 Posts

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

Mar 19, 2012 01:22 AM|LINK

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

- Reply
Dan Bracuk

Contributor

3970 Points

1096 Posts

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

Mar 19, 2012 01:31 AM|LINK

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.

‹ Previous Thread|Next Thread ›

Unable to insert into oracle database - ORA-00911 error RSS

5 replies

sharat80

Unable to insert into oracle database - ORA-00911 error

sansan

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

sharat80

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

OlteanuBogda...

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

Lannie

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

Dan Bracuk

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

sansan

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft