Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
-
intrad80
- Posts: 5
- Joined: Fri 05 Oct 2007 14:11
Post
by intrad80 » Mon 23 Mar 2009 10:44
Hello, I recieve this exception when i try to update a row with a formview control. The oracle column E_PD_SURNAME is char(20). I use an oracle 10g database and vs2008 3.5 framework and oracle provider Version=5.0.22.0
Exception
Code: Select all
Devart.Data.Oracle.OracleException was unhandled by user code
Code=1036
ErrorCode=-2147467259
Message="ORA-01036 illegal variable name/number"
Offset=0
Source="Devart.Data.Oracle"
StackTrace:
en Devart.Data.Oracle.s.a(aq[] A_0, Byte[] A_1)
en Devart.Data.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
en Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
en Devart.Data.Oracle.Web.OracleDataSource.a.a(CommandBehavior A_0)
en System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
en Devart.Data.Oracle.OracleCommand.ExecuteReader(CommandBehavior behavior)
en Devart.Data.Oracle.Web.OracleDataSource.a.a(CommandBehavior A_0)
en System.Data.Common.DbCommand.ExecuteReader()
en Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
en System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation)
en System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues)
en System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback)
en System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation)
en System.Web.UI.WebControls.FormView.UpdateItem(Boolean causesValidation)
en _Default.Button2_Click(Object sender, EventArgs e) en E:\ceinsaDE\ASP.NET\GDCEcinfa\Default.aspx.vb:línea 13
en System.Web.UI.WebControls.Button.OnClick(EventArgs e)
en System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
en System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
en System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
en System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
en System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:
.ASPX Code
Last edited by
intrad80 on Mon 23 Mar 2009 16:20, edited 1 time in total.
-
Shalex
- Site Admin
- Posts: 9543
- Joined: Thu 14 Aug 2008 12:44
Post
by Shalex » Mon 23 Mar 2009 16:06
This error was generated because only one parameter is set in your code but our provider generates the whole set of parameters if at least one parameter is set. Please replace your code
Code: Select all
UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME ">
with the following piece of code
Code: Select all
UpdateCommand="UPDATE EMP_2008 SET E_PD_SURNAME = :E_PD_SURNAME WHERE ID_EMP_0000 = :ID_EMP_0000">
-
intrad80
- Posts: 5
- Joined: Fri 05 Oct 2007 14:11
Post
by intrad80 » Mon 23 Mar 2009 16:19
Thank you but I have the same error with the 2 parameters. My table have only 2 columns ID_EMP_0000 and E_PD_SURNAME and I put the 2 parameters in the UPDATECOMMAND.
Create table:
CREATE TABLE CEINSA.EMP_2008 (
E_PD_SURNAME CHAR(20),
ID_EMP_0000 LONG)
TABLESPACE CEINSAEX
STORAGE (
INITIAL 64K
MAXEXTENTS UNLIMITED
)
LOGGING;
-
intrad80
- Posts: 5
- Joined: Fri 05 Oct 2007 14:11
Post
by intrad80 » Tue 24 Mar 2009 10:48
Getting same error with gridview, any ideas?
-
Shalex
- Site Admin
- Posts: 9543
- Joined: Thu 14 Aug 2008 12:44
Post
by Shalex » Tue 24 Mar 2009 12:55
Please replace your code
with the following code
I.e., add to the name of your id parameter the Original_ prefix. This is a Microsoft issue. We cannot change it.
About the script posted above: if you set the type of your id column to LONG, it cannot be used in the WHERE clause. Please refer to the Oracle documentation.
-
intrad80
- Posts: 5
- Joined: Fri 05 Oct 2007 14:11
Post
by intrad80 » Tue 24 Mar 2009 14:04
Thank you!!. Works great with Original_ID_EMP_0000 parameter
!
-
histu
- Posts: 7
- Joined: Fri 09 Oct 2009 08:57
Post
by histu » Thu 21 Jan 2010 12:28
Hello Devart Team,
I have got the same problem.
When you said this:
This error was generated because only one parameter is set in your code but our provider generates the whole set of parameters if at least one parameter is set.
The whole set of parameters is the hole fields of the record, or the hole fields of the table? In my case selects only select some fields from the table without parameters.
When I see what sent to the database with the dbmonitor I see that the params are sent like strings and not like values.
Prepare: update iban_felhaszn_jog_fej set C_ELONEV = :C_ELONEV , C_NEV1 = :C_NEV1 where C_OPERNEV = :C_OPERNEV
It means that the parameters are handled like strings or just hide the values and change the parameter names with their value in the background?
-
histu
- Posts: 7
- Joined: Fri 09 Oct 2009 08:57
Post
by histu » Thu 21 Jan 2010 15:28
Hello
It seems the problem is solved. I forget to use Original_ pretag in the where clause.
-
Shalex
- Site Admin
- Posts: 9543
- Joined: Thu 14 Aug 2008 12:44
Post
by Shalex » Thu 21 Jan 2010 16:00
Devart OracleDataSource behaviour is like the one of Microsoft SqlDataSource. OracleDataSource uses Microsoft's parameter generation. So you need to adjust your query to the generated parameters collection. You can find this parameters collection using DbMonitor (choose your OracleConnection object in Object Tree > select your your query in the Events window > the View | Parameters menu of DbMonitor). Please refer to MSDN (e.g.,
http://msdn.microsoft.com/en-us/library/z72eefad.aspx ).