ORA-01036: illegal variable name/number

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
graham_ballAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RejojohnyCommented:
>>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?


0
graham_ballAuthor 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.
0
RejojohnyCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

graham_ballAuthor 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>
0
RejojohnyCommented:
just guessing .. is it because u have not specified a type for the updte parameter
<asp:Parameter Name="sam_account_name" type="string"/>
0
graham_ballAuthor Commented:
No.
I've string, empty and no parameter at all.
0
RejojohnyCommented:
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
0
graham_ballAuthor 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 ?
0
graham_ballAuthor Commented:
The daft thing is that I can run this in Toad with no problem at all.
0
graham_ballAuthor 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.
0
GranModCommented:
PAQed with points refunded (125)

GranMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.