<div id=intelliTxt style="PADDING-RIGHT: 5px; PADDING-LEFT: 5px">Hi,
I'm using formdetails to add/update data.
On one page, it is working fine, on another I keep getting the error 1036 (ORA-01036: illegal variable name/number) when I try to update a record.
I've taken the update statement down to as basic a statement as possible in order to identify the problem and have used sqlmonitor to see what is being passed to
Oracle.
Below is what I get in the trace.
I just don't see what the problem can be.
sam_account_name is varchar(30)
-----------------------------------
Timestamp: 09:18:53.816
UPDATE t_users SET sam_account_name = :sam_account_name WHERE sam_account_name = :sam_account_name
sam_account_name = 'm'
Execution time: 0 ms
<div id=intelliTxt>Found it !!
After so damn long.
Turns out it was the ConflictDetection parameter.
It was set to CompareAllValues. I changed it to OverwriteChanges and it all works.
Given that the parameter list was the same as the values I was updating, I can't see why this should have proved such a problem.
But that's MS, I guess.</div>
OK - thought of a way round this, but I need a little help.
What I'm doing is catching the rowupdating event, updating the database in code behind, which I know works, then cancelling the update using e.cancel.
This is fine, but leaves the row in edit mode (showing the update/cancel links), rather than putting the row back in display mode and refreshing the grid.
Can someone tell me how to do that ( get the row back into display and refresh the grid)?
OK, went back to basics and recreated the page from scratch (not using copy & paste from the old one) and..... it worked.
There must have been some corruption of the old page somewhere.
So, I formatted the grid a bit to make it look better; hid some fields etc. and got on with some testing.
I then found that certain fields were being wiped out during an update.
It turns out that it was the invisible fields !!! If a field is not visible, gridview doesn't update it; actually no. If that were the case, it would be OK, but it actually nulls the field out!.
I tried removing the fields from the update statement and update parameter list and guess what - I got the ORA-1036 error again. Yet everything matched up.
I then tried to refer to the field with the original_ prefix, after making sure it was defined (
OldValuesParameterFormatString="original_{0}" )
and again got the ORA-1036 error.
Is this flaky or what ?
Of course, it's not likely to get fixed as I can't see anyway of feeding this back to MS except via these forums and it's quite apparent that no-one from MS ever comes here.
Really makes you glad you did the right thing and paid for it, doesn't it?
Just in case anyone else has this problem, it turns out that OldValuesParameterFormatString="original_{0}" was the problem.
Removing it allowed me to remove the fields that the gridview was blanking out and enable updating to work ok.
For some reason I couldn't remove one field that wasn't being updated. It has a valid value in it, so it's not a problem. But I just can't see why I could remove some fields, but not others.
Also, how on Earth can I refer to old values when defining the OldValuesParameterFormatString stops the gridview working ?
<div id=intelliTxt>Hi </div> <div> </div> <div>I have the similar problem. Update command works fine in TOAD but when used the same in Gridview it gives ORA-01036: illegal variable name/number.</div> <div> </div> <div>What do you mean by ConflictDetection
parameter - where to set that to OverwriteChanges ????</div> <div> </div> <div>And also did you get to solve this </div> <div> </div> <div>This is fine, but leaves the row in edit mode (showing the update/cancel links), rather than putting the row
back in display mode and refreshing the grid.</div> <div> </div> <div>How to put back to display mode.</div> <div> </div> <div>Any help is highly appreciated.</div> <div> </div> <div>Thanks in advnace</div> <div>Sri </div> <div> </div> <div> </div>
<div> </div> <div> </div> <div> </div> <div> </div> <div>Found it !!
After so damn long.
Turns out it was the ConflictDetection parameter.
It was set to CompareAllValues. I changed it to OverwriteChanges and it all works.
Given that the parameter list was the same as the values I was updating, I can't see why this should have proved such a problem.
But that's MS, I guess.</div>
I searched and searched for a suitable methed to cancel the edit. The only solution I managed to come up with was refresh the page with a Server.Transfer("page.aspx"). If there were any page/control settings which were set in the page I put them into session
variables which I checked in the page load.
Not the preferred method, but it is working for me. If there is a better method; I am all ears!
Wombleuk
Member
134 Points
34 Posts
1036 (ORA-01036: illegal variable name/number)
Mar 23, 2006 10:45 AM|LINK
I'm using formdetails to add/update data.
On one page, it is working fine, on another I keep getting the error 1036 (ORA-01036: illegal variable name/number) when I try to update a record.
I've taken the update statement down to as basic a statement as possible in order to identify the problem and have used sqlmonitor to see what is being passed to Oracle.
Below is what I get in the trace.
I just don't see what the problem can be.
sam_account_name is varchar(30)
-----------------------------------
Timestamp: 09:18:53.816
UPDATE t_users SET sam_account_name = :sam_account_name WHERE sam_account_name = :sam_account_name
sam_account_name = 'm'
Execution time: 0 ms
Runtime error occurred: 1036 (ORA-01036: illegal variable name/number)
-----------------------------------
this is the asp definition
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:Tempus %>" DeleteCommand="DELETE FROM t_users WHERE user_id = :original_user_id "
InsertCommand="INSERT INTO t_users (user_id, sam_account_name, username, department_id, company_id, role_id) VALUES (seq_t_users.nextval, :sam_account_name, :username, :department_id, :company_id, :role_id)"
OldValuesParameterFormatString="original
UpdateCommand="UPDATE t_users SET sam_account_name = :sam_account_name WHERE sam_account_name = :sam_account_name "
ProviderName="<%$ ConnectionStrings:Tempus.ProviderName %>">
<DeleteParameters>
<asp:Parameter Name="original_user_id" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="sam_account_name" />
<asp:Parameter Name="username" />
<asp:Parameter Name="department_id" />
<asp:Parameter Name="company_id" />
<asp:Parameter Name="role_id" />
<asp:Parameter Name="original_user_id" Type="Int32" />
<asp:ProfileParameter Name="userID" PropertyName="userID" Type="Decimal" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="sam_account_name" Type="Empty" />
<asp:Parameter Name="username" Type="Empty" />
<asp:Parameter Name="department_id" Type="Empty" />
<asp:Parameter Name="company_id" Type="Empty" />
<asp:Parameter Name="role_id" Type="Empty" />
<asp:ProfileParameter Name="userID" PropertyName="userID" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>
thx
Graham</div>
Wombleuk
Member
134 Points
34 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Mar 23, 2006 01:41 PM|LINK
After so damn long.
Turns out it was the ConflictDetection parameter.
It was set to CompareAllValues. I changed it to OverwriteChanges and it all works.
Given that the parameter list was the same as the values I was updating, I can't see why this should have proved such a problem.
But that's MS, I guess.</div>
Wombleuk
Member
134 Points
34 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Mar 24, 2006 07:46 AM|LINK
Well, that seemed to solve the problems I had with formsview, but I cannot get this to work with gridview.
I have now hardcoded the update statement and still get the error !
Does anyone have any idea what's wrong (works fine in Toad) ?
This is driving me nuts.
thx
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="OverwriteChanges" ConnectionString="<%$ ConnectionStrings:Tempus %>" DeleteCommand='DELETE FROM T_TIMESHEET WHERE TIMESHEET_ID = :original_TIMESHEET_ID' OldValuesParameterFormatString="original_{0}" SelectCommand='SELECT * FROM T_TIMESHEET WHERE (ENTRY_DATE >= :ENTRY_DATE and ENTRY_DATE < :ENTRY_DATE + 7) ORDER BY ENTRY_DATE,CREATION_TIMESTAMP' UpdateCommand="UPDATE T_TIMESHEET SET TASK_ID = 1 WHERE TIMESHEET_ID = 28" ProviderName="<%$ ConnectionStrings:Tempus.ProviderName %>" InsertCommand='INSERT INTO T_TIMESHEET (TIMESHEET_ID, USER_ID, ENTRY_DATE, TASK_ID, REFERENCE_ITEM, DURATION, COMMENTS, CREATION_TIMESTAMP, UPDATE_TIMESTAMP, REPORTED) VALUES (seq_t_timesheet.nextval, :USER_ID, :ENTRY_DATE, :TASK_ID, :REFERENCE_ITEM, :DURATION, :COMMENTS, sysdate, sysdate, :REPORTED)'> <DeleteParameters> <asp:Parameter Name="original_TIMESHEET_ID"/> </DeleteParameters> <UpdateParameters> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="Calendar1" Name="ENTRY_DATE" PropertyName="SelectedDate" Type="DateTime" /> </SelectParameters> <InsertParameters> <asp:ProfileParameter Name="userID" PropertyName="userID" Type="Decimal" /> <asp:Parameter Name="ENTRY_DATE" Type="DateTime" /> <asp:Parameter Name="TASK_ID" Type="Decimal" /> <asp:Parameter Name="REFERENCE_ITEM" Type="String" /> <asp:Parameter Name="DURATION" Type="Decimal" /> <asp:Parameter Name="COMMENTS" Type="String" /> <asp:Parameter Name="REPORTED" Type="String" /> </InsertParameters> </asp:SqlDataSource>----------------------------------
Timestamp: 08:42:19.149
UPDATE T_TIMESHEET SET TASK_ID = 1 WHERE TIMESHEET_ID = 28
Execution time: 0 ms
Runtime error occurred: 1036 (ORA-01036: illegal variable name/number)
Wombleuk
Member
134 Points
34 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Mar 24, 2006 03:50 PM|LINK
OK - thought of a way round this, but I need a little help.
What I'm doing is catching the rowupdating event, updating the database in code behind, which I know works, then cancelling the update using e.cancel.
This is fine, but leaves the row in edit mode (showing the update/cancel links), rather than putting the row back in display mode and refreshing the grid.
Can someone tell me how to do that ( get the row back into display and refresh the grid)?
thx very much
Graham
Wombleuk
Member
134 Points
34 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Mar 27, 2006 09:55 AM|LINK
That doesn't work, because you can't force a page refresh from the server.
This is really proving to be a showstopper.
Doesn't anyone from MS ever read these posts ?
Wombleuk
Member
134 Points
34 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Mar 30, 2006 12:05 PM|LINK
OK, went back to basics and recreated the page from scratch (not using copy & paste from the old one) and..... it worked.
There must have been some corruption of the old page somewhere.
So, I formatted the grid a bit to make it look better; hid some fields etc. and got on with some testing.
I then found that certain fields were being wiped out during an update.
It turns out that it was the invisible fields !!! If a field is not visible, gridview doesn't update it; actually no. If that were the case, it would be OK, but it actually nulls the field out!.
I tried removing the fields from the update statement and update parameter list and guess what - I got the ORA-1036 error again. Yet everything matched up.
I then tried to refer to the field with the original_ prefix, after making sure it was defined (
OldValuesParameterFormatString="original_{0}" ) and again got the ORA-1036 error.
Is this flaky or what ?
Of course, it's not likely to get fixed as I can't see anyway of feeding this back to MS except via these forums and it's quite apparent that no-one from MS ever comes here.
Really makes you glad you did the right thing and paid for it, doesn't it?
Wombleuk
Member
134 Points
34 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Mar 31, 2006 09:33 AM|LINK
Just in case anyone else has this problem, it turns out that OldValuesParameterFormatString="original_{0}" was the problem.
Removing it allowed me to remove the fields that the gridview was blanking out and enable updating to work ok.
For some reason I couldn't remove one field that wasn't being updated. It has a valid value in it, so it's not a problem. But I just can't see why I could remove some fields, but not others.
Also, how on Earth can I refer to old values when defining the OldValuesParameterFormatString stops the gridview working ?
vidyashan
Member
55 Points
11 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Apr 03, 2006 07:01 PM|LINK
After so damn long.
Turns out it was the ConflictDetection parameter.
It was set to CompareAllValues. I changed it to OverwriteChanges and it all works.
Given that the parameter list was the same as the values I was updating, I can't see why this should have proved such a problem.
But that's MS, I guess.</div>
vidyashan
Member
55 Points
11 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Apr 03, 2006 07:55 PM|LINK
Hi
Atlast found how to set Conflict parameter - but even after setting to Overwritechanges and setting
OldValuesParameterFormatString="original_{0}"
The exception doesn't go away - it still says ORA-01036:illegal variable name/number.
Please let me know how to overcome this problem.
Sri
matt1234
Member
70 Points
15 Posts
Re: 1036 (ORA-01036: illegal variable name/number)
Apr 03, 2006 08:18 PM|LINK
I searched and searched for a suitable methed to cancel the edit. The only solution I managed to come up with was refresh the page with a Server.Transfer("page.aspx"). If there were any page/control settings which were set in the page I put them into session variables which I checked in the page load.
Not the preferred method, but it is working for me. If there is a better method; I am all ears!