Subquery in SqlDataSource failing

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

Guest

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

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

Cor

Morris Neuman said:
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.
Click to expand...
 
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'.
Click to expand...

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

Remove square brackets ([]) from your subqueries.

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


Morris Neuman said:
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.
Click to expand...
 
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


Garik said:
Hi Morris,

Remove square brackets ([]) from your subqueries.

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


Morris Neuman said:
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.
Click to expand...
Click to expand...
 
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
 
Back
Top