sqldatasource filterexpression - Cannot find column

  • Thread starter Thread starter jobs
  • Start date Start date
J

jobs

Cannot find column [@phone_nbr].


Am I missing something here...

SelectCommand="SELECT [ssn], [phone_nbr], [Description], refid
FROM [Callers_vw] "
FilterExpression="phone_nbr like @phone_nbr + '%'"


<FilterParameters>
<asp:ControlParameter PropertyName="text" Name="phone_nbr"
ControlID="textbox1" Type="string" />
</FilterParameters>


btw, i tried FilterExpression="phone_nbr = @phone_nbr'"

and it resulted in the same error.

If I move the sql code out of the filterexpress and to select it
works, but I can't seem to get all rows returned if nothing is
filtered via the textbox.

Here's what I want to do. If nothing is in the textbox return all
rows, otherwise attempt to filter. For whaterver reason, I am learning
that vb.net's nothing does not equate to sql servers dbnull or isnull
logic.

Thanks for any help or information!
 
jobs,

In general, I'd recommend avoiding FilterExpressions and try to stick with
SelectParameters instead. You'll get both better performance and security.

Try again with a SelectParamenter and no filtering. Change your where
clause to either:
"...WHERE coalesce(phone_nbr,'') like @phone_nbr + '%' "
or
"...WHERE isnull(phone_nbr,'') like @phone_nbr + '%' "

(Coalesce() and Isnull() are pretty much the same in this context)

Filter expressions are their own little animal and the syntax is different
from SelectParameters on a SqlDataSource. The syntax for FilterExpressions
is based on the syntax used for DataView.RowFilter and is similar to
string.Format() style (i.e. String.Format("PhoneNumber like '{0}'", "1235%")
).

If you want to use a filter expression try one of these:
FilterExpression="phone_nbr like '{0}%'"
FilterExpression="isnull(phone_nbr,'') like '{0}%'"

Since filterExpressions use the format() style syntax the are less secure
than SelectParameters and are vulnerable to sql injection attacks. See this
for more info:
http://msdn2.microsoft.com/en-us/li...bcontrols.sqldatasource.filterparameters.aspx

hope this helps,
Jason Vermilllion
 
Back
Top