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?
 
GranModConnect With a Mentor Commented:
PAQed with points refunded (125)

GranMod
Community Support Moderator
0
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.