Illegal Character error for ASP.NET/Oracle

Question

I'm trying to make this ASP/SQL statement to work but I keep getting ORA-01036: Illegal Character error. So, I'm assuming that I am missing an escape character for my SQL Syntax or something along those lines.

I converted this code from my C# code which works and coding it to ASP.

    <asp:SqlDataSource ID="EmployeeSqlDataSource" runat="server"
       ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
       ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
       OnSelecting="EmployeeSqlDataSource_Selecting" 
       SelectCommand="SELECT a.vax_user_id, b.person_id, b.email_id, b.last_nm,  b.first_nm, b.parent_org_unit_cd, a.acceptance_date, a.emp_status_cd
       FROM inet_own.fitness_rwl a, inet_own.employee_info_ldap_snap b
       WHERE a.vax_user_id = b.vax_user_id 
       AND a.emp_status_cd like 'A'
       AND UPPER(b.last_nm) LIKE UPPER('%:lastName%')
       ORDER BY b.parent_org_unit_cd, a.acceptance_date
      ">

     <SelectParameters>
        <asp:ControlParameter ControlID="radTbLastName" Name="lastName" />
     </SelectParameters>
</asp:SqlDataSource>

Sodved · Accepted Answer · 2011-10-04 15:23:26Z

1

Is :lastName meant to be a bind variable? I don't know ASP, but neither perl or java or C (oci) would like that. Those languages do not allow bind variables within string literals (quotes), Instead you would have to write it as

 AND UPPER(b.last_nm) LIKE UPPER( '%' || :lastName || '%' )

Unfortunately I don't see how this could cause the invalid character error, but maybe its confusing ASP so what is actually being passed to oracle is messed up. Just a guess.

answered Oct 4, 2011 at 15:23

Sodved

8,6272 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ReiRei Over a year ago

It's supposed to be. The SelectParameters is linked to a Telerik Text Box that captures the text/name from the user then uses that as a variable to replace the :lastName inside the SQL Statement with the text value of the text box.

ReiRei Over a year ago

Surprised that your suggestion actually worked. I had the C# code behind and it didn't like the % before so I had to make a string variable adding the % together with the user input then pass that variable along.

Collectives™ on Stack Overflow

Illegal Character error for ASP.NET/Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related