OracleParameter.ParameterName must comply with Oracle "bind variable" syntax
rules: 1) lexical requirement is-- starts with a letter and then can be
letter, numbers, $, _, or # up to length 30 and 2) when used in query syntax
is preceded by a colon(:)
so, query should be "select from table where numcol = :val"
and then construction and filling of parameter should be:
Dim prm1 As New OracleParamet("val", OracleType.Int32)
prm1.Value = intMyVal
cmdRoles.Parameters.Add(prm1)
note: it doesn't seem to make any sense to use a databinding property on a
parameter whose value is supplied programaticaly. Also, you can save
yourself trouble by using the OracleType that is most directly related to
your actual program variable type (integer or System.Int32). The size of the
Oracle data column is of no importance in this case since you are just
comparing an integer value to ones found within the column, although you know
nothing in the column can exceed 999. But OracleType.Number actually
corresonds most closely to System.Decimal, not System.Int32. In any event,
type checking and/or type consistency will never substitute for bounds
checking.
"Mike" wrote:
I get this error when trying to use a paramter on a dynamic sql statement.
What am I doing wrong.
Exception Details: System.Data.OracleClient.OracleException: ORA-01036:
illegal variable name/number
Here is the code:
Dim strSQL As String
Dim intMyVal as integar = 5
strSQL = "SELECT * FROM TABLE WHERE(NUMBERCOL = @VAL)"
Dim oOracleConn As New OracleConnection(connstring)
If oOracleConn.State = ConnectionState.Closed Then
oOracleConn.Open()
End If
Dim cmdRoles As New OracleCommand(strSQL, oOracleConn)
cmdRoles.CommandType = CommandType.Text
Dim prm1 As New OracleParameter("@VAL", OracleType.Number)
prm1.Size = 3
prm1.Scale = 0
prm1.Value = intMyVal
prm1.Direction = ParameterDirection.Input
prm1.SourceVersion = DataRowVersion.Current
cmdRoles.Parameters.Add(prm1)
Dim dsNames As New DataSet
Dim da As New OracleDataAdapter(cmdRoles)
da.Fill(dsRoles) <-- fails here. when executing the command.
I do not how to make this work. The oracle column is NUMBER SIZE 3, SCALE 0.
Any help would be appreciated.
--
Mike Logan