ORA-01008 not all variables bound.

[Guest]

I get the following error when I submit a dataset (with
one modified row in one table )to a dataadapter.

ORA-01008 not all variables bound.
Cause: A SQL statement containing substitution variables
was executed without all variables bound. All substitution
variables must have a substituted value before the SQL
statement is executed

The update command of the dataadapter is set up this way
(I have stared at it for hours and see nothing wrong)

Me.comUpdate.CommandText = _
"UPDATE smpl_products " & _
"SET description = ESCRIPTION,
product_type_id = RODUCT_TYPE_ID, " & _
"quantity_per_unit = :QUANTITY_PER_UNIT,
unit_price = :UNIT_PRICE, " & _
"units_in_stock = :UNITS_IN_STOCK,
units_on_order = :UNITS_ON_ORDER, " & _
"reorder_level = :REORDER_LEVEL, " & _
"enabled = :ENABLED, created_by
= :CREATED_BY, modified_by = :MODIFIED_BY " & _
"WHERE (product_id = RODUCT_ID) AND
(modified_date = :MODIFIED_DATE)"
Me.comUpdate.Connection = Me.con
Me.comUpdate.Parameters.Add(New OracleParameter
("DESCRIPTION", OracleType.VarChar))
Me.comUpdate.Parameters.Add(New OracleParameter
("PRODUCT_TYPE_ID", OracleType.Raw))
Me.comUpdate.Parameters.Add(New OracleParameter
("QUANTITY_PER_UNIT", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "QUANTITY_PER_UNIT", DataRowVersion.Current,
Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("UNIT_PRICE", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(2,
Byte), "UNIT_PRICE", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("UNITS_IN_STOCK", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "UNITS_IN_STOCK", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("UNITS_ON_ORDER", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "UNITS_ON_ORDER", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("REORDER_LEVEL", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "REORDER_LEVEL", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("ENABLED", OracleType.VarChar, 1, "ENABLED"))
Me.comUpdate.Parameters.Add(New OracleParameter
("CREATED_BY", OracleType.VarChar, 20, "CREATED_BY"))
Me.comUpdate.Parameters.Add(New OracleParameter
("MODIFIED_BY", OracleType.VarChar, 20, "MODIFIED_BY"))
Me.comUpdate.Parameters.Add(New OracleParameter
("PRODUCT_ID", OracleType.Raw))
'Me.comUpdate.Parameters.Add(New OracleParameter
("MODIFIED_DATE", OracleType.DateTime, 0, "MODIFIED_DATE"))
Me.comUpdate.Parameters.Add(New OracleParameter
("MODIFIED_DATE", OracleType.DateTime, 0,
ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "MODIFIED_DATE", DataRowVersion.Original, Nothing))

I am using Microsoft Oracle .Net managed provider.
I can query using the same dataadapter with no problems.
I have checked that for each parameter I have an embeded
placeholder in the SQL

Any ideas? Anyone

 

[Cowboy \(Gregory A. Beamer\)]

The code would be a bit easier to read if you boudn the parameters outside
of your statement.

One thing I have found with using sprocs with Oracle (I realize this is not
a sproc), is you have to work differently with OleDb and OracleClient. With
OracleClient, you have to create an Output Paramter for the cursor Oracle
creates for output. For OleDb, you do not. This may be what is happening
with you, although I am not certain.

If you set up the parameters separately, you should be able to figure out
which parameter is either a) not bound, or b) null, both of which
conceivably could cause the error. If neither is the case, look at an output
parameter for the cursor, although that would seem strange for an update
statement.

I would personally move to a stored procedure, as it is both a) more secure
and b) easier to trap exceptions with.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Lyndon Hills]

I get the following error when I submit a dataset (with
one modified row in one table )to a dataadapter.

ORA-01008 not all variables bound.
Cause: A SQL statement containing substitution variables
was executed without all variables bound. All substitution
variables must have a substituted value before the SQL
statement is executed

The update command of the dataadapter is set up this way
(I have stared at it for hours and see nothing wrong)

Me.comUpdate.CommandText = _
"UPDATE smpl_products " & _
"SET description = ESCRIPTION,
product_type_id = RODUCT_TYPE_ID, " & _
"quantity_per_unit = :QUANTITY_PER_UNIT,
unit_price = :UNIT_PRICE, " & _
"units_in_stock = :UNITS_IN_STOCK,
units_on_order = :UNITS_ON_ORDER, " & _
"reorder_level = :REORDER_LEVEL, " & _
"enabled = :ENABLED, created_by
= :CREATED_BY, modified_by = :MODIFIED_BY " & _
"WHERE (product_id = RODUCT_ID) AND
(modified_date = :MODIFIED_DATE)"
Me.comUpdate.Connection = Me.con
Me.comUpdate.Parameters.Add(New OracleParameter
("DESCRIPTION", OracleType.VarChar))
Me.comUpdate.Parameters.Add(New OracleParameter
("PRODUCT_TYPE_ID", OracleType.Raw))
Me.comUpdate.Parameters.Add(New OracleParameter
("QUANTITY_PER_UNIT", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "QUANTITY_PER_UNIT", DataRowVersion.Current,
Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("UNIT_PRICE", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(2,
Byte), "UNIT_PRICE", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("UNITS_IN_STOCK", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "UNITS_IN_STOCK", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("UNITS_ON_ORDER", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "UNITS_ON_ORDER", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("REORDER_LEVEL", OracleType.Number, 0,
ParameterDirection.Input, False, CType(10, Byte), CType(0,
Byte), "REORDER_LEVEL", DataRowVersion.Current, Nothing))
Me.comUpdate.Parameters.Add(New OracleParameter
("ENABLED", OracleType.VarChar, 1, "ENABLED"))
Me.comUpdate.Parameters.Add(New OracleParameter
("CREATED_BY", OracleType.VarChar, 20, "CREATED_BY"))
Me.comUpdate.Parameters.Add(New OracleParameter
("MODIFIED_BY", OracleType.VarChar, 20, "MODIFIED_BY"))
Me.comUpdate.Parameters.Add(New OracleParameter
("PRODUCT_ID", OracleType.Raw))
'Me.comUpdate.Parameters.Add(New OracleParameter
("MODIFIED_DATE", OracleType.DateTime, 0, "MODIFIED_DATE"))
Me.comUpdate.Parameters.Add(New OracleParameter
("MODIFIED_DATE", OracleType.DateTime, 0,
ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "MODIFIED_DATE", DataRowVersion.Original, Nothing))

I am using Microsoft Oracle .Net managed provider.
I can query using the same dataadapter with no problems.
I have checked that for each parameter I have an embeded
placeholder in the SQL

Any ideas? Anyone

Well some of the parameters don't have directions. The raw data type
is a bit odd., for a product id. It won't be indexed (I think).

Rather than staring at it any more why not strip all but one of the
columns out and re-build it one at a time until it either works or
breaks?