We help IT Professionals succeed at work.

ORA-01036: illegal variable name/number

graham_ball
graham_ball asked
on
Medium Priority
16,568 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:
PAQed with points refunded (125)

GranMod
Community Support Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*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.