Subquery in SqlDataSource failing

microsoft.public.dotnet.framework.adonet

14 Feb 2007 6:04 AM

Morris Neuman

Hi,

I have a query in SqlDataSource that has a subquery. I have it working in
AccessDataSource but get error when running it in SqlDataSource. Since I was
told that the .net query builder only allows simple queries, I have ignored
any error messages from the query builder and test it by running the
application.

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)

I get error as folows:
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'.

Any help would be appreciated.

--
Thanks
Morris

14 Feb 2007 6:44 AM

Cor Ligthert [MVP]

Morris,

Did you run this query in the query analizer while those columns exist in
the database?

Cor

Show quote

"Morris Neuman" <Morris@online.nospam> schreef in bericht
news:A69DA3EF-E7A0-4EAF-8EBF-E73225DCBA9D@microsoft.com...
> Hi,
>
> I have a query in SqlDataSource that has a subquery. I have it working in
> AccessDataSource but get error when running it in SqlDataSource. Since I
> was
> told that the .net query builder only allows simple queries, I have
> ignored
> any error messages from the query builder and test it by running the
> application.
>
> 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)
>
>
> I get error as folows:
> 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'.
>
> Any help would be appreciated.
> --
> Thanks
> Morris

14 Feb 2007 7:50 AM

WenYuan Wang

Hi Morris,
Sorry for late reply. I have tested it on my machine. But, unfortunately, I
cannot reproduced the issue without the actual data and scenario.

>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'.

At first, according these error messages, it seems like the Table(Message)
hasn't included columns(Account, Status, BoxNumber). Would you please check
it?
Additionally, in generally, for such SQL query command issue, we suggest
you may execute this Query Command on your SQL Sever side directly by
Microsoft SQL Sever Management Studio (SQL 2005) or Microsoft Enterprise
Manager (SQL 2000). After that, 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
assist you.

Hope this helps
Sincerely.
Wen Yuan

14 Feb 2007 9:52 AM

Garik

Hi Morris,

Remove square brackets ([]) from your subqueries.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET

Show quote

"Morris Neuman" wrote:

> Hi,
>
> I have a query in SqlDataSource that has a subquery. I have it working in
> AccessDataSource but get error when running it in SqlDataSource. Since I was
> told that the .net query builder only allows simple queries, I have ignored
> any error messages from the query builder and test it by running the
> application.
>
> 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)
>
>
> I get error as folows:
> 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'.
>
> Any help would be appreciated.
> --
> Thanks
> Morris

14 Feb 2007 4:17 PM

Morris Neuman

It works!

Removed the square brackets and the query with the subquery works
wonderfully. Never would have thought to remove the square brackets as my
understanding is that they define the fields.

Thanks so much.

--
Thanks
Morris

Show quote

"Garik" wrote:

> Hi Morris,
>
> Remove square brackets ([]) from your subqueries.
>
> --
> Regards,
> Garik Melkonyan
> MCP, MCAD, MCSD .NET
>
>
> "Morris Neuman" wrote:
>
> > Hi,
> >
> > I have a query in SqlDataSource that has a subquery. I have it working in
> > AccessDataSource but get error when running it in SqlDataSource. Since I was
> > told that the .net query builder only allows simple queries, I have ignored
> > any error messages from the query builder and test it by running the
> > application.
> >
> > 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)
> >
> >
> > I get error as folows:
> > 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'.
> >
> > Any help would be appreciated.
> > --
> > Thanks
> > Morris

15 Feb 2007 12:04 PM

WenYuan Wang

Hi Morris,

Greatly appreciate for Garik's help. I'm so sorry I haven't figure it out
in my first reply.

In SQL, [Message.Status] will be recognized as a column name rather than
TableName.ColumnName.
For this reason, SQL server will throw an exception said there is no column
named as "Message.Status". For defining the fields, we can use
[Message].[Status], but [Message.Status] is invalidate.

Have a great day.
Best regards,
Wen Yuan