sqldatasource filterexpression - Cannot find column

[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!

 

[Guest]

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