RepID-- PhoneUPS
---- ----
3---- 3
RepID-- PhoneUPS
---- ----
4---- 0
RepID-- PhoneUPS
---- ----
5---- 2
No more results.
(9 row(s) returned)
@.RETURN_VALUE = 0
All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)
Cheers,
AndrewCan you create a UNION query in the stored procedure, rather then seperate resultsets?
Failing that, you can manually add each row of each resultset to the Items collection of the Datagrid.|||Thanks for the tip. I've been working on my SQL procedure trying to incorporate the UNION in the SELECT statement. I am having problems with the logic of working it in, though. Here is the original SQL procedure I've created:
/*BEGIN Procedure */
ALTER PROCEDURE Rep_Status_Array
AS
/* Build array or ID numbers to cycle through */
DECLARE RepIDs_cursor CURSOR
FOR SELECT UserID
FROM TBL_Users
WHERE (TBL_Users.DepartmentID > 0)
OPEN RepIDs_cursor
DECLARE @.userID int
FETCH NEXT FROM RepIDs_cursor INTO @.userID
/* Begin WHILE loop to collect data for each ID */
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT
RepID=@.userID,
PhoneUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 11) AND (SalesmanID = @.userID)),
LOTUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),
CVR=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),
FETCH NEXT FROM RepIDs_cursor INTO @.userID
END
/* END WHILE loop */
CLOSE RepIDs_cursor
DEALLOCATE RepIDs_cursor
/* END Procedure */
The problem I'm having with this is that each time through the WHILE loop creates a new table in the dataset that is returned by the procedure. Any suggestions?|||Is it even possible to use UNION or UNION ALL within a WHILE loop?|||To the best of my knowledge, it is not possible to use the UNION statement in conjunction with a WHILE loop.
I'm going to go with Douglas' second recommendation and manually create a table within my page, adding each row one at a time.|||Another alternative:
Create a temporary table, and then INSERT all selected rows into the temp table, and then at the end of the SP,
SELECT * FROM #temp
(with any required ORDER BY).|||Thanks for the help, Douglas, much appreciated!
Andrew
