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
Lannie
Contributor
3742 Points
730 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