Subquery too complex, other options for GridView label
value?

I have a simple query running a simple gridview, but require a complex subquery
that joins to multiple tables with several filters to determine a value in each
row. 

If the subquery is too complex to really be a subquery, do I have other options?
Can I use the OnRowDatabound event to fire some VB code to retrieve a value from
a complex query and assign it to a TextBox in each row?Sub
GridView_RowDataBound(ByVal sender AsObject, ByVal e As
System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView.RowDataBound

If e.Row.RowType = DataControlRowType.DataRow Then

Dim Z As Label = CType(e.Row.FindControl("Z"), Label)

Dim SQLString As String = "SELECT SUM(X) etc etc FROM Table WHERE
lotsofstuff..."

??? how do I get the value of X from SQL into Label Z?

EndIf

EndSub

It is not really advisable to do this way, because if your data has some 100
rows , you end up in making 101 connections [including the main dataset] . I
would rather suggest you to write a stored procedure that joins this complex
query or write a user defind function in SQL server to get your data for each
row and you just data bind in front end.

Luckily it will always be very short, a department list never more than 20
records max.

I'm really not sure how to approach the problem. In the gridview I want a
complete listing of each of those departments, but in one cell I want to display
a summary of hours related to that department. The tricky part lies in that the
subquery data would come from 3 different tables where the critical WHERE/HAVING
statements reside, requiring those fields to be part of the SELECT statement,
which it doesn't seem to want to accept.

I guess I need to brush up on stored procedures.

Yep... even with 20 rows in the Grid, still it is not a good programming
practice to call database on RowdataBound event. Here is the sample sp I can
think of for you 

 

Select col1, col2, col2, dbo.MySummaryFunction(col5, col6) AS SummryData From
dbo.MyTable

 

Where as your function looks like 

CREATE FUNCTION dbo.MySummaryFunction
(
  @lat1 float,
  @lon1 float
  
)
RETURNS varchar(8000)

BEGIN   

SET    @ReturnValue = -- your complex query with lots lots of WHERE/HAVING
claues 

return @ReturnValue 
 

END

You have posted to the Access forum, so assuming that you are in fact using
Access, you may have to go with the solution you first thought of - that is
firing a select for each row.  It's not ideal, but Access doesn't support stored
procedures, or multple statements passed as a batch.

A possible, better alternative would be to return the two separate queries as
two separate datatables in one dataset, then use the relations between the two
to select the appropriate data for the label from the 2nd datatable for each row
in the RowDataBound event.  That way you aren't constantly querying the
database.  It's a similar approach to the one you would use for nested databound
controls.