Subquery in AccessDataSource Query Builder Failing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi,

I tried running the query with a comma after the first sub query command
ExprN. However that did not help and I got the same error.

Not sure what is wrong with the syntax.

Help!
--
Thanks
Morris


Morris Neuman said:
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.
Click to expand...
 
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
 
¤ 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)
 
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.
 
Hi Paul,

I tried suggestion by Paul, however that did not work. Please see my reply
to his response.

I hope you can find a way for the sub query to work. I await your research
results and suggestions.
 
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
 
¤ 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)
 
Hi,

OK I got the query working with AccessDataSource. It was as you and Paul
said, ignore the query builder error and run the app. ExprN and ExprS are
just variable names for results of the subquery. The problem was that I
needed the literals N and S in quote ("N"< "S"). So AccessDataSource works -
Thanks.

New related problem -
I now need the same query to run using SqlDataSource. I modified the query
slightly and tried the same thing by ignoring the query builder. However I
get a different error:
Invalid column name 'Mailboxes.BoxNumber'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Invalid column name 'Mailboxes.BoxNumber'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.

My sql query is
SELECT Mailboxes.[BoxRowID], 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.[KeyID] AS Expr1,
MailboxUserLoginXref.BoxNumber AS Expr2, (SELECT COUNT([Message.Status]) FROM
Message WHERE ([Mailboxes.BoxNumber] = [Message.Account] AND [Message.Status]
= 'N') GROUP BY [Message.Account]) AS ExprN, (SELECT COUNT([Message.Status])
FROM Message WHERE ([Mailboxes.BoxNumber] = [Message.Account] AND
[Message.Status] = 'S') GROUP BY [Message.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.[BoxRowID], 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.[KeyID],
MailboxUserLoginXref.BoxNumber HAVING (MailboxUserLoginXref.UserLogin =
@UserLogin)

As you see I used single quotes for the literals 'N' and 'S' and change the
way the parameter is requested.

Once again, any help is very much appreciated.
--
Thanks
Morris


"WenYuan Wang" said:
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
Click to expand...
 
Hi Paul,

You hit the nail on the head - I had to put the N and S in quotes ("N", "S")
as they are constant values that I am selecting. Once I made the change, it
worked for AccessDataSource.

I now need the same query to work for SqlDataSource. Please see my response
to WenYuan Wang for a description of the problem with SqlDataSource.

Thanks you very much for your help with Access. Any help with Sql will be
much appreciated.

Look forward to hearing from you.
 
Hi Morris,
Thanks for your reply.

I'm so glad to hear you resolve the issue.
Invalid column name 'Mailboxes.BoxNumber'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Invalid column name 'Mailboxes.BoxNumber'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Invalid column name 'Message.Account'.
Invalid column name 'Message.Status'.
Click to expand...

According these error messages, it seems like the Table(Message) hasn't
included columns(Account, Status, BoxNumber). Would you please check it?
Additonally, we suggest you may execute this Query Command on your SQL
Sever directly by Microsoft SQL Sever Management Studio (SQL 2005) or
Microsoft Enterprise Manager (SQL 2000), you can get more detailed
information for you issue.

Please feel free to reply me if there is anything unclear. I'm very glad to
work with you.

Hope this helps!
Sincerely.
Wen Yuan
 
Back
Top