SQL Server subqueries Populate the DataSet Times 500 Internal error resolution

The operating environment is visual Studio 2010, and the database is SQL Server 2008.

Execute the following SQL statement

SELECT Count (CollectionID) Collectionnumber,Count(LName) plantnumber    from (SELECTDISTINCT Subsiteid, Subsitetitle, CollectionID, LName, CName                from where lname='Agave sisalana'Group  by Subsiteid, Subsitetitle

Running normally in Enterprise Manager, you can query the results, but in the program you always prompt for 500 internal errors. The following is the C # code:

1 New SqlConnection (strconn); 2 New SqlCommand (SQL, conn); 3 Conn. Open (); 4 New SqlDataAdapter (cmd); 5 " DS ");  // there 's an error. 6 Conn. Close ();

No exception is caught on code try catch.

Workaround: Simply add an alias to the inner subquery.

SELECT  Count (Q.collectionid) Collectionnumber,Count(q.lname) plantnumber     from (SELECTDISTINCT  Subsiteid, Subsitetitle, CollectionID, LName, CName             fromwhere LName=  'Agave sisalana') qGroup by Subsiteid, Subsitetitle

SQL Server subqueries Populate the DataSet Times 500 Internal error resolution