Problems with string parameters RSS

• • Reply
  

  sdprest

  Member

  35 Points

  22 Posts

  Problems with string parameters

  Nov 11, 2011 10:29 PM|LINK

  I have an sqldatasource in my asp.net site that has a parameter it gets from a control on the page.  When I set the query on a  number data column in the DB the datasource works perfect.  However when I write the query on a string (char or varchar) column in the DB it doesn't work.  I made sure all the parameters are correct in the datasource.  This is for an Oracle 11g database -- Below is my code:

  <asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:XXXXXX %>"
  ProviderName="<%$ ConnectionStrings:XXXXXX %>"
                                          
  SelectCommand="SELECT &quot;IMAGEURL&quot;, &quot;PRODUCTNAME&quot;, &quot;MINHEIGHT&quot;, &quot;MINWIDTH&quot;
  FROM &quot;PRODUCT&quot;
  WHERE (&quot;PRODUCTNAME&quot; = :PRODUCT)">
                                          
  <SelectParameters>
  <asp:ControlParameter ControlID="Label3" Name="PRODUCT" PropertyName="Text" Type="String" />
  </SelectParameters>
  </asp:SqlDataSource>

  The previous is for the string column that doesn't show any items.  Below is the decimal or number column that does shot items.

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:XXXXXX %>" 
  ProviderName="<%$ ConnectionStrings:XXXXXX %>" 
                                          
  SelectCommand="SELECT &quot;IMAGEURL&quot;, &quot;PRODUCTNAME&quot;, &quot;MINHEIGHT&quot;, &quot;MINWIDTH&quot; 
  FROM &quot;PRODUCT&quot; 
  WHERE (&quot;PRODUCTID&quot; = :PRODUCT)">
                                          
  <SelectParameters>
  <asp:ControlParameter ControlID="Label3" Name="PRODUCT" PropertyName="Text" Type="Decimal" />
  </SelectParameters>
  </asp:SqlDataSource>

• • Reply
  

  Prashant Kum...

  Star

  12346 Points

  1993 Posts

  Re: Problems with string parameters

  Nov 12, 2011 04:15 AM|LINK

  In Oracle, string comparison is case sensitive. Make sure that case of the parameter being passed in matches the data in the database.

  Alternatively, modify the query to make it case-insensitive

  SELECT IMAGEURL, PRODUCTNAME, MINHEIGHT, MINWIDTH
  FROM PRODUCT
  WHERE UPPER(PRODUCTNAME) = UPPER(:PRODUCT)

  Prashant
  • Edited by Prashant Kumar on Nov 12, 2011 04:16 AM
• • Reply
  

  sdprest

  Member

  35 Points

  22 Posts

  Re: Problems with string parameters

  Nov 12, 2011 04:35 PM|LINK

  I know Oracle is case sensitive and I think it is a problem with the actual parameter setting.  If I rewrite the query to read

  WHERE (PRODUCTNAME = 'Adobe')

  then the code works just fine.  I did try your code above and it didn't work either.  Its something to do with :PRODUCT not working properly.  Again it works fine on number columns just not string columns.

  I do have the ASP.NET scripts from Oracle installed to work with Visual Studio 2010.