We help IT Professionals succeed at work.

ORA-01036: illegal variable name/number

graham_ball
graham_ball asked
on
16,591 Views
Last Modified: 2011-08-18
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

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_{0}" SelectCommand="SELECT * FROM t_users"
        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
Comment
Watch Question

>>UPDATE t_users SET sam_account_name = :sam_account_name WHERE sam_account_name = :sam_account_name
This SQL really does not make much sense ..

it would mean something like this ..
UPDATE t_users SET sam_account_name = 'm'
WHERE sam_account_name = 'm'

how would that work?


Author

Commented:
>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.


It's not supposed to make sense, I'm trying to solve a problem.
also, i assume when u have just
UPDATE t_users SET sam_account_name = :sam_account_name WHERE sam_account_name = :sam_account_name
as ur update query .. u have also changed ur update parameter list to just have that parameter

Author

Commented:
Yes - no joy there.

    <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_{0}" SelectCommand="SELECT * FROM t_users"
        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" />
        </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>
just guessing .. is it because u have not specified a type for the updte parameter
<asp:Parameter Name="sam_account_name" type="string"/>

Author

Commented:
No.
I've string, empty and no parameter at all.
have a look at this link .. maybe one of these might be similar to ur issue .. like the space after the parameter name ..
http://objectsharp.com/Blogs/bruce/archive/2004/09/17/883.aspx

Author

Commented:
Yes, I've seen that one before.
But my dataname is nowhere near 32 chars and there isn't a space at the end, or are you seeing something I'm not ?

Author

Commented:
The daft thing is that I can run this in Toad with no problem at all.

Author

Commented:
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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.