"Morris Neuman" wrote:

> I have a query with a sub query that works fine in MS Access.  However I get
> an error when trying the same query in .Net AccessDataSource Query Builder. 
>
> I get the error when I place open parenthesis at the start of the sub query
> (Select statement and then the close parenthesis at the end of the sub query
> GROUP BY [Account]). 
>
> The error I receive is:
> Error in SELECT clause: expression near 'SELECT'.
> Error in SELECT clause: expression near 'FROM'.
> Missing FROM clause.
> Unable to parse query text.
>
>
> The query is as follows:
> --------Start Query-----
> SELECT     Mailboxes.[Key], Mailboxes.BoxNumber, Mailboxes.LastName,
> Mailboxes.PhoneNumber, Mailboxes.DirectoryListing, Mailboxes.[Password],
>                       Mailboxes.FirstName, Mailboxes.BoxStatus,
> MailboxStatusXref.BoxStatusDescription, Mailboxes.Flags,
> Mailboxes.MaxNewMessages,
>                       Mailboxes.MaxSavedMessages,
> Mailboxes.MaxMessageLength, Mailboxes.MaxMessageAge,
> Mailboxes.GreetingFileID, Mailboxes.NameFileID,
>                       Mailboxes.NotificationID, Mailboxes.NewMessageCount,
> Mailboxes.SavedMessageCount, Mailboxes.NoAnswerRetryTime,
>                       Mailboxes.NoOwnerRetryTime, Mailboxes.BusyRetryTime,
> Mailboxes.NoAnswerRetryCount, Mailboxes.NoOwnerRetryCount,
>                       Mailboxes.BusyRetryCount, Mailboxes.NotifyDelay,
> Mailboxes.ConfirmTime, Mailboxes.ProficiencyLevel, Mailboxes.Gender,
>                       Mailboxes.AlarmTriggerCount, Mailboxes.EMAILAddress,
> Mailboxes.MiddleName, Mailboxes.OutOfOfficeFileID,
> MailboxUserLoginXref.UserLogin,
>                       MailboxStatusXref.BoxStatusNumber,
> MailboxUserLoginXref.[Key] AS Expr1, MailboxUserLoginXref.BoxNumber AS Expr2,
>                           (SELECT     COUNT([Status])
>                             FROM          Message
>                             WHERE      ([Mailboxes.BoxNumber] =
> [Message.Account] AND [Status] = N)
>                             GROUP BY [Account]) AS ExprN
>                           (SELECT     COUNT([Status])
>                             FROM          Message
>                             WHERE      ([Mailboxes.BoxNumber] =
> [Message.Account] AND [Status] = S)
>                             GROUP BY [Account]) AS ExprS
> FROM         (((Mailboxes INNER JOIN
>                       MailboxUserLoginXref ON Mailboxes.BoxNumber =
> MailboxUserLoginXref.BoxNumber) INNER JOIN
>                       MailboxStatusXref ON Mailboxes.BoxStatus =
> MailboxStatusXref.BoxStatusNumber) LEFT OUTER JOIN
>                       Message ON Mailboxes.BoxNumber = Message.Account)
> GROUP BY Mailboxes.[Key], Mailboxes.BoxNumber, Mailboxes.LastName,
> Mailboxes.PhoneNumber, Mailboxes.DirectoryListing, Mailboxes.[Password],
>                       Mailboxes.FirstName, Mailboxes.BoxStatus,
> MailboxStatusXref.BoxStatusDescription, Mailboxes.Flags,
> Mailboxes.MaxNewMessages,
>                       Mailboxes.MaxSavedMessages,
> Mailboxes.MaxMessageLength, Mailboxes.MaxMessageAge,
> Mailboxes.GreetingFileID, Mailboxes.NameFileID,
>                       Mailboxes.NotificationID, Mailboxes.NewMessageCount,
> Mailboxes.SavedMessageCount, Mailboxes.NoAnswerRetryTime,
>                       Mailboxes.NoOwnerRetryTime, Mailboxes.BusyRetryTime,
> Mailboxes.NoAnswerRetryCount, Mailboxes.NoOwnerRetryCount,
>                       Mailboxes.BusyRetryCount, Mailboxes.NotifyDelay,
> Mailboxes.ConfirmTime, Mailboxes.ProficiencyLevel, Mailboxes.Gender,
>                       Mailboxes.AlarmTriggerCount, Mailboxes.EMAILAddress,
> Mailboxes.MiddleName, Mailboxes.OutOfOfficeFileID,
> MailboxUserLoginXref.UserLogin,
>                       MailboxStatusXref.BoxStatusNumber,
> MailboxUserLoginXref.[Key], MailboxUserLoginXref.BoxNumber
> HAVING      (MailboxUserLoginXref.UserLogin = ?)
>
> --------End Query-----
>
>
> Any help will be appreciated.
>
> --
> Thanks
> Morris

> Hi Morris,
>
> I have reproduced your issue and need to perform more research on this
> issue . We will reply here as soon as possible.
> If you have any more concerns on it, please feel free to post here.
>
> Thanks for your understanding!
> Best regards,
> Wen Yuan
>
>

> Hi Morris,
> Thanks for your waiting.
>
> In fact, I agree with Paul. The Query Builder in VS is not smart enough to
> handle Sub-Query command. You can ignore error message and run the
> application. The root cause of the issue in your first post is missing the
> comma after the first sub query command "ExprN". But after you added it,
> the Query Command is fine. I have tested it on my machine, but the error
> message still appeared in my Query Builder. I ignored the message and
> continued to execute Query. I can really get the data from Access data
> file. It works fine.
> About why do you still get the error message ("No value given for one or
> more required parameters.") from application.
> It seems like you must have forgotten to pass the value to parameters in
> Query.
>
> Additional, I noticed something strange in your Sub Query command.
> (SELECT COUNT([Status]) FROM Message WHERE ([Mailboxes.BoxNumber] =
> [Message.Account] AND [Status] = N) GROUP BY [Account]) AS ExprN,
> (SELECT     COUNT([Status]) FROM Message WHERE ([Mailboxes.BoxNumber] =
> [Message.Account] AND [Status] = S) AS ExprS.
>
> I'm confused with the letter "N" and "S". Is this a parameter? As far as I
> know, in ADO.net connecting Access database, the parameter must be assigned
> as "?". The AccessDataSouce can't recognize other parameters. I think this
> maybe the root cause of your issue. The letter "N" and "S" should be
> changed to "?".
>
> It's very easy to prove it.
> Please replace all parameters in your Query and run it with application,
> then you will not get any error message from your application. This means
> the query command is fine.
> Then you can change "N" and "S" to "?", add parameters again, then run the
> application.
>
> Please try the method as above and let me know if this works for you.
> Hope this helps.
> Sincerely,
> Wen Yuan
>
>

"Paul Clement" wrote:

> On Wed, 7 Feb 2007 17:33:00 -0800, Morris Neuman <Morris@online.nospam> wrote:
>
> ¤ I have a query with a sub query that works fine in MS Access.  However I get
> ¤ an error when trying the same query in .Net AccessDataSource Query Builder. 
> ¤
> ¤ I get the error when I place open parenthesis at the start of the sub query
> ¤ (Select statement and then the close parenthesis at the end of the sub query
> ¤ GROUP BY [Account]). 
>
> The designer doesn't seem to be able to parse anything but relatively simple Access queries.
> However, if you save the query anyway it should function properly when you run the application -
> provided it doesn't contain any user-defined or restricted VBA functions,
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>

"Paul Clement" wrote:

> On Thu, 8 Feb 2007 11:01:00 -0800, Morris Neuman <Morris@online.nospam> wrote:
>
> ¤ Hi Paul,
> ¤
> ¤ Did as you suggested.  Ignored the parse error, saved query and ran the
> ¤ website.
> ¤
> ¤ Unfortunately it did not work.  I now get error:
> ¤ No value given for one or more required parameters.
> ¤
> ¤ I am willing to try any other suggestions.
>
> I don't see where N and S are defined in the query string. Currently they are being evaluated as
> variables and not string literals (if that is what you intend):
>
> [Message.Account] AND [Status] = N
>
> [Message.Account] AND [Status] = S
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>