gridview filterexpression problem

[Curt]

I have a gridview object with a sqldatasource as its sourceid.
Everything was going so well until I came across this problem.

My SELECTCOMMAND for my sqldatasource is:
select alertid,subject,createdt,eventid,eventname, statuscd,trgid from
vwAlerts order by createdt desc

Now, based on a fiew input fields, I put together a WHERE clause for
the filterexpression. For example, this filterexpression works great:

createdt >= '1/1/2007' and createdt <= '1/31/2007'

Simple so far. I also have an input field which is actually a field
in a child table (emaildistribution). If they put anything in this
field, the filterexpression needs to do an IN clause like this:

createdt >= '1/1/2007' and createdt <= '1/31/2007' AND alertid
IN(select alertid from emaildistribution where emailaddress like
'%yahoo.com%'

When I run this, I get an "Missing operand after 'alertid' operator"
error. Doesn't matter if I use LIKE or = in the IN clause. Is there
any way to make this work? I hate to go back to the manual binding
method.

Thanks for any help!
--Curt

[verci]

Hi

Can you post yor aspx page code?
Regards

"Curt" <ad***********@sshomes.netwrote in message
news:11**********************@j27g2000cwj.googlegr oups.com...

>I have a gridview object with a sqldatasource as its sourceid.
Everything was going so well until I came across this problem.

My SELECTCOMMAND for my sqldatasource is:
select alertid,subject,createdt,eventid,eventname, statuscd,trgid from
vwAlerts order by createdt desc

Now, based on a fiew input fields, I put together a WHERE clause for
the filterexpression. For example, this filterexpression works great:

createdt >= '1/1/2007' and createdt <= '1/31/2007'

Simple so far. I also have an input field which is actually a field
in a child table (emaildistribution). If they put anything in this
field, the filterexpression needs to do an IN clause like this:

createdt >= '1/1/2007' and createdt <= '1/31/2007' AND alertid
IN(select alertid from emaildistribution where emailaddress like
'%yahoo.com%'

When I run this, I get an "Missing operand after 'alertid' operator"
error. Doesn't matter if I use LIKE or = in the IN clause. Is there
any way to make this work? I hate to go back to the manual binding
method.

Thanks for any help!
--Curt

[Eliyahu Goldin]

Curt,

The FilterExpression applies to the dataset after it gets loaded from the
database. It doesn't effect the select statement. Rather the select gets the
data inside the dataset and then the FilterExpression hides undesired rows.
Based on this understanding, putting another select in the IN operator
should not work since the select is intended to run against the database,
not against the dataset.

--
Eliyahu Goldin,
Software Developer & Consultant
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net
"Curt" <ad***********@sshomes.netwrote in message
news:11**********************@j27g2000cwj.googlegr oups.com...

>I have a gridview object with a sqldatasource as its sourceid.
Everything was going so well until I came across this problem.

My SELECTCOMMAND for my sqldatasource is:
select alertid,subject,createdt,eventid,eventname, statuscd,trgid from
vwAlerts order by createdt desc

Now, based on a fiew input fields, I put together a WHERE clause for
the filterexpression. For example, this filterexpression works great:

createdt >= '1/1/2007' and createdt <= '1/31/2007'

Simple so far. I also have an input field which is actually a field
in a child table (emaildistribution). If they put anything in this
field, the filterexpression needs to do an IN clause like this:

createdt >= '1/1/2007' and createdt <= '1/31/2007' AND alertid
IN(select alertid from emaildistribution where emailaddress like
'%yahoo.com%'

When I run this, I get an "Missing operand after 'alertid' operator"
error. Doesn't matter if I use LIKE or = in the IN clause. Is there
any way to make this work? I hate to go back to the manual binding
method.

Thanks for any help!
--Curt

[Curt]

Thanks for your replies.

I got it to work. Eliyahu your reply got me thinking...I never
considered changing the SELECT command if the user enters any
information in a certain field. Now I just change the select command
to include the IN clause...this works great.

Thanks !

-Curt