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:
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.
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 "IMAGEURL", "PRODUCTNAME", "MINHEIGHT", "MINWIDTH" FROM "PRODUCT" WHERE ("PRODUCTNAME" = :PRODUCT)"> <SelectParameters> <asp:ControlParameter ControlID="Label3" Name="PRODUCT" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:XXXXXX %>" ProviderName="<%$ ConnectionStrings:XXXXXX %>" SelectCommand="SELECT "IMAGEURL", "PRODUCTNAME", "MINHEIGHT", "MINWIDTH" FROM "PRODUCT" WHERE ("PRODUCTID" = :PRODUCT)"> <SelectParameters> <asp:ControlParameter ControlID="Label3" Name="PRODUCT" PropertyName="Text" Type="Decimal" /> </SelectParameters> </asp:SqlDataSource>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
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.