Last post May 12, 2011 08:53 PM by Decker Dong - MSFT
May 09, 2011 12:11 PM|hahnj|LINK
I've been using the following tutorial in an attempt to return a table with information from 2 tables.
However when I enter a query with subqueries into the TableAdapter Configuration Wizard I receieve errors. The SQL I'm using is:
SELECT UserID, UserName, FirstName, LastName, Password, Phone, Email, PrivilegeID, PlantID, (SELECT PrivilegeNumber FROM Privilege WHERE Privilege.PrivilegeID = [User].PrivilegeID) AS PrivilegeNumber
But I receive the following errors:
Error in SELECT clause: expression near 'SELECT'.
Error in SELECT clause: expression near 'FROM'.
Missing FROM clause.
Unable to parse query text.
I wanted to use subqueries so that the insert/update/delete commands are automatically generated. I also cannot use stored procedures. Thank you for any help
May 09, 2011 04:28 PM|akhhttar|LINK
You can use Join to get data from different table
SELECT UserID, UserName, FirstName, LastName, Password, Phone, Email, U.PrivilegeID, PlantID,P.PrivilegeNumber
From [User] U
INNER JOIN Privilege P ON U.PrivilegeID = P.PrivilegeID
Hope it helps!
May 10, 2011 12:01 PM|hahnj|LINK
Thank you, but I was hoping to let the wizard autogenerate the insert/update/delete statements. I was hoping to find out how to get the subqueries to work with the wizard, as the SQL I have in my first post should be valid. I can even get the wizard to
execute the statement in the query builder even though it displays the errors I listed, but I can't complete it to autogenerate the remaining statements.
May 10, 2011 09:50 PM|decker dong - msft|LINK
A Wizard, I think, it's using SqlCommandBuilder, which means it can auto generate simple select, insert or delete query sql statements. To those which are complicated (like inner join, two or more table's interchange), I think you should write your own sql
query statements manually in the Wizard.
May 11, 2011 08:28 AM|hahnj|LINK
The SQL statement I have wasn't generated by the wizard. As you mentioned it doesn't really create subqueries and such. The problem I have is the statement I wrote isn't being accepted by the wizard due to the errors listed.
May 11, 2011 09:25 PM|decker dong - msft|LINK
Have you tried what
akhhttar says? It should work properly.
And anyway, You should rewrite your insert, delete as well as update sql statement for the foreign tables.
May 12, 2011 08:20 AM|hahnj|LINK
If you use joins in the wizard it won't automatically generate the insert/update/delete statements.
May 12, 2011 08:53 PM|decker dong - msft|LINK
Yes, just as what I've mentioned above——
The Wizard is calling SqlCommandBuilder to build insert, delete and update sql statements for only sole table. So for multiple tables or complicated situation. You'd better write yourself.