ObjectDataSource Question

[Frank]

Hello All,

I am coding with VS.NET 2005 and attempting to execute a somewhat
complicated sql query.

I have created a DataLayer and an objectDataSource, bound to a DetailsView;

This work no problem if I configure the datasource of the DeatilsView to
execute
'SELECT Account
FROM DistributionList
WHERE ListName = ?
AND Broker = ?


If however I attempt to execute:

SELECT Account
FROM DistributionList
WHERE ListName = ?
AND Broker = ?
AND Account Not In (SELECT Account
FROM DistributionList
WHERE ListName = ?
AND Broker = ? )

I get an error message that values were not supplied for one or more
variables. Anybody have any suggestions please?

 

[Guest]

?'s are a bad way of doing parameters, you will now need to pass 4 parameters
the two you are probably passing currently and then those two again for the
sub query.
If this is SQL or Oracle, you should use named paremeters and it makes
everything easier to read.

Ciaran O'Donnell

 

[Frank]

Hey Ciaran,

Thanks for your reply. I agree that using '?' as parameters is not the best
idea, but with the ObjectDataSource in VS.NET 2005, sometimes when I try to
use:

SELECT Account

FROM DistributionList

WHERE ListName=@ListName

AND Broker=@Broker

the response from the ObjectDataSource designer is that it cannot generate
the statements because of an error near '@'. Kind of a wierd thing, it
doesn't happen all the time, but right now, it won't let me use:

SELECT Account

FROM DistributionList

WHERE ListName = @ListName

AND Broker = @Broker

AND Account Not In (SELECT Account

FROM DistributionList

WHERE ListName = @ListName

AND Broker = @Broker )

I guess I could create a custom datalayer user control and bind to it, but I
was hoping for an explaination of what is happening with the
ObjectDataSource