Parameter Information cannot be derived from SQL Statements with sub-select queries

I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?