Dynamic Datagrid not showing up

[nickvans]

Hi everyone,

Thanks in advance for trying to help me out.

I have a SQLDataSource which is running a dynamically generated SQL2005 stored procedure. (That is, the stored procedure creates a varchar string and ends with an exec(@string) command.) The datasource is used by a gridview to display the result. The stored procedure gets a number of parameters which set bit values for "use query criteria 1" , "use query criteria 2" etc which are all tied to Visual Studio check boxes. (The result is-- will be-- that the user can select one or more check boxes and filter which records are returned.)

The stored procedure runs perfectly when I execute it on the database side, as well as when I click "Test Query" in the SQLDataSource configuration wizard. The columns are set properly, and everything looks to be fine right up until when I build it.

My problem is the gridview just doesn't show up. No header, no data-- nothing.

I admit I'm very perplexed. Does anyone have any idea what could cause this?

Other potentially useful info:

I don't get any build errors or SQL exceptions.
If instead of ending the stored procedure with exec(@string) I use SELECT @string, and copy/paste the output into the Select statement of the datasource, it runs fine.

Also, stepping through the .cs file shows that the SqlDataSource_S elected() is never called. Any idea why this would be?

Thanks in advance for your help!

[Frinavale]

Could you please post your code for the SQLDataSource so that we can see what's going on?

Thanks,

-Frinny

[nickvans]

Sure thing Frinny.

The SQLDataSource uses this code:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Nick
-- Create date: 2/16/2009
-- Description:	Recieves user input from front end and
-- returns a list of tasks, their due dates, completion
-- dates etc.
-- =============================================
CREATE PROCEDURE [dbo].[xx_ReportCustom3_List]
	@USE_DEFAULT int = 1, --1 = use default, 0 = use current selection
	@USER_BEMSID int = 1801478,
	@DAYS_FWD int = 0, -- how far forward to look. 0 = infinite
	@DAYS_BACK int= 0, -- how far backward to look. 0 = infinite
	@PAST_DUE int= 0, -- display items with status of past due.
	@COMPLETED_ON_TIME int= 0, -- display items with status of completed on time
	@OPEN int= 0, -- display items with status of open
	@MM int= 0, -- display items with class of MM
	@CPI int= 0, -- display items with class of CPI
	@TSR int= 0, -- display items with class of CPI
	@DELIV int= 0  , -- display items with class of Deliv
-- note: for MM, CPI, TSR and DELIV 1 = Filter by, 0 = don't filter
	@ASSIGNED_TO_BEMS int = NULL, -- display items assigned to this user
	@SPECIFIED_EFFECTIVITY varchar(max) = NULL, -- display items associated with this effectivity
	@COMPLETED_LATE int = 0 -- display items with status of completed late
AS
BEGIN

DECLARE @TIMESTAMP datetime
SET @TIMESTAMP = getdate()

DECLARE @STR_SELECT varchar(max)
DECLARE @STR_CLASS varchar(max)

-- Some data checking/fixing from front end
IF @SPECIFIED_EFFECTIVITY = ''
	SET @SPECIFIED_EFFECTIVITY = NULL

DECLARE @ALL_ASSIGNEES int -- 1 = show all, 0 = only assigned_to
IF @ASSIGNED_TO_BEMS = 0
	SET @ALL_ASSIGNEES = 1
ELSE 
	SET @ALL_ASSIGNEES = 0

DECLARE	@ALL_EFFECTIVITIES int -- 1 = show all, 0 = only specified effectivity
IF @SPECIFIED_EFFECTIVITY IS NULL
	SET @ALL_EFFECTIVITIES = 1
ELSE
	SET @ALL_EFFECTIVITIES = 0


DECLARE @USE_WHERE_CLAUSE int
IF 
(
	@DAYS_FWD = 0 
	and @DAYS_BACK = 0
	and @PAST_DUE = 0 
	and @COMPLETED_LATE = 0 
	and @OPEN = 0  
	and @COMPLETED_ON_TIME = 0 
	and @MM = 0  
	and @CPI = 0  
	and @TSR = 0  
	and @DELIV = 0   
	and (@ASSIGNED_TO_BEMS = 0 or @ALL_ASSIGNEES = 1)
	and (@SPECIFIED_EFFECTIVITY = NULL or @ALL_EFFECTIVITIES = 1)
)
SET @USE_WHERE_CLAUSE = 0
ELSE 
SET @USE_WHERE_CLAUSE = 1
--SELECT @USE_WHERE_CLAUSE


--Create table variable to store user inputs
DECLARE @var TABLE 
(	
	BEMSID int,
	USE_WHERE_CLAUSE int, -- 1 = filter, 0 = return all
	DAYS_FWD int, 
	DAYS_BACK int,
	PAST_DUE int,
	COMPLETED_LATE int,
	[OPEN] int,
	COMPLETED_ON_TIME int,
	MM int, 
	CPI int,
	TSR int,
	DELIV int, -- MM, CPI, TSR and DELIV 1 = Filter, 0 = don't filter
	ALL_ASSIGNEES int, -- 1 = show all, 0 = only assigned_to
	ASSIGNED_TO_BEMS int,
	ALL_EFFECTIVITIES int, -- 1 = show all, 0 = only specified effectivity
	SPECIFIED_EFFECTIVITY varchar(max) 
)



--SET USER PREFERENCES
-- if @USE_DEFAULT = 1: populate @var with defaults
-- if @USE_DEFAULT = 0: populate @var with passed in paramaters
IF @USE_DEFAULT = 1 
	BEGIN
		INSERT INTO @var 
		(
			BEMSID, 
			USE_WHERE_CLAUSE, 
			DAYS_FWD, 
			DAYS_BACK, 
			PAST_DUE, 
			COMPLETED_LATE, 
			[OPEN], 
			COMPLETED_ON_TIME,
			MM, 
			CPI, 
			TSR, 
			DELIV, 
			ALL_ASSIGNEES, 
			ASSIGNED_TO_BEMS, 
			ALL_EFFECTIVITIES, 
			SPECIFIED_EFFECTIVITY
		)
		SELECT 
			BEMSID, 
			@USE_WHERE_CLAUSE, 
			DAYS_FWD, 
			DAYS_BACK, 
			PAST_DUE, 
			COMPLETED_LATE, 
			[OPEN], 
			COMPLETED_ON_TIME,
			MM, 
			CPI, 
			TSR, 
			DELIV, 
			@ALL_ASSIGNEES, 
			ASSIGNED_TO_BEMS, 
			@ALL_EFFECTIVITIES, 
			SPECIFIED_EFFECTIVITY
		FROM dbo.xx_USER_CERT_SUMMARY_PREFERENCES
		WHERE BEMSID = @USER_BEMSID		
	END

ELSE

	INSERT INTO @var 
	(
		BEMSID, 
		USE_WHERE_CLAUSE, 
		DAYS_FWD, 
		DAYS_BACK, 
		PAST_DUE, 
		COMPLETED_LATE, 
		[OPEN], 
		COMPLETED_ON_TIME,
		MM, 
		CPI, 
		TSR, 
		DELIV, 
		ALL_ASSIGNEES, 
		ASSIGNED_TO_BEMS, 
		ALL_EFFECTIVITIES, 
		SPECIFIED_EFFECTIVITY
	)
	VALUES
	(
		@USER_BEMSID, 
		@USE_WHERE_CLAUSE, 
		@DAYS_FWD, 
		@DAYS_BACK, 
		@PAST_DUE, 
		@COMPLETED_LATE, 
		@OPEN, 
		@COMPLETED_ON_TIME,
		@MM, 
		@CPI, 
		@TSR, 
		@DELIV, 
		@ALL_ASSIGNEES, 
		@ASSIGNED_TO_BEMS, 
		@ALL_EFFECTIVITIES, 
		@SPECIFIED_EFFECTIVITY
	)
--SELECT * FROM @var

-- Build SELECT clause
-- The SELECT clause is fixed for this report, therefore no 'if' statements are required.

SET @STR_SELECT = 
'SELECT 
	CLASS,
	DUE_DATE,
	ASSIGNED_TO_NAME,
	TASK_REFERENCE,
	EFFECTIVITY,
	CASE 
		WHEN cast(' + '''' + cast(@TIMESTAMP as varchar(max)) + '''' + ' as varchar(max)) > DUE_DATE AND COMPLETION_DATE IS NULL THEN ''Past Due''
		' + ' WHEN cast(' + '''' + cast(@TIMESTAMP as varchar(max)) + '''' + ' as varchar(max)) < DUE_DATE AND COMPLETION_DATE IS NULL THEN ' + '''' + 'Open' + '''' +
		' WHEN COMPLETION_DATE < DUE_DATE THEN ' + '''' + 'Completed On Time' + '''' +
		' WHEN COMPLETION_DATE > DUE_DATE THEN ' + '''' + 'Completed Late' + '''' + 		
	' END as STATUS,
	COMPLETION_DATE
FROM
dbo.fn_NickManagerReport_List()'

-- Build WHERE clause

-- There are three groups of items to query by.
-- 1) Independent variables: One value found in one column only. Uses 'and'
-- 2) Status variables: Multiple status types found in the same column. Uses 'or'
-- 3) Task variables: Multiple task types found in the same column. Uses 'or'

DECLARE @QUERY_FLAG_1 bit -- query by days fwd, days back, effectivity, assigned to
SET @QUERY_FLAG_1 = 0

DECLARE @QUERY_FLAG_2 bit -- query by status
SET @QUERY_FLAG_2 = 0

DECLARE @QUERY_FLAG_3 bit -- query by Task
SET @QUERY_FLAG_3 = 0

DECLARE @STR_WHERE_1 varchar(max) -- holds 'and' statements
DECLARE @STR_WHERE_2 varchar(max) -- holds Status statements
DECLARE @STR_WHERE_3 varchar(max) -- holds Task statements
DECLARE @STR_WHERE_FINAL varchar(max) -- combines statements


--Initialize strings
SET @STR_WHERE_FINAL = ''
SET @STR_WHERE_1 = ''
SET @STR_WHERE_2 = ''
SET @STR_WHERE_3 = ''

--SELECT DAYS_FWD FROM @var
IF (SELECT DAYS_FWD FROM @var) <> 0 -- note 0 is ignore criteria else number of days
	BEGIN
		SET @QUERY_FLAG_1 = 1
		SET @STR_WHERE_1 = @STR_WHERE_1 + ' DUE_DATE <= CAST(' + '''' + CONVERT(varchar(max),@TIMESTAMP + (SELECT DAYS_FWD FROM @var), 120) + '''' + ' as datetime) and '
	END

--SELECT DAYS_BACK FROM @var
IF (SELECT DAYS_BACK FROM @var) <> 0  -- note 0 is ignore criteria else number of days
	BEGIN
		SET @QUERY_FLAG_1 = 1
		SET @STR_WHERE_1 = @STR_WHERE_1 + ' DUE_DATE >= CAST(' + '''' + CONVERT(varchar(max),@TIMESTAMP - (SELECT DAYS_BACK FROM @var), 120) + '''' + ' as datetime) and '
	END

-- Add ASSIGNEE filter
IF (SELECT ALL_ASSIGNEES FROM @var) <> 1 and (SELECT ASSIGNED_TO_BEMS FROM @var) <> 0 --IS NOT NULL
	BEGIN
		SET @QUERY_FLAG_1 = 1
		SET @STR_WHERE_1 = @STR_WHERE_1 + 'ASSIGNED_TO_BEMS = ' + CONVERT(varchar(max), (SELECT ASSIGNED_TO_BEMS FROM @var)) + ' and '
	END

-- Add EFFECTIVITY filter
IF (SELECT ALL_EFFECTIVITIES FROM @var) <> 1 and (SELECT SPECIFIED_EFFECTIVITY FROM @var) IS NOT NULL
	BEGIN	
		SET @QUERY_FLAG_1 = 1
		SET @STR_WHERE_1 = @STR_WHERE_1 + 'EFFECTIVITY = ' + '''' + (SELECT SPECIFIED_EFFECTIVITY FROM @var) + '''' + ' and '
	END



-- Add STATUS filter
SET @STR_WHERE_2 = @STR_WHERE_2 + '('
IF (SELECT PAST_DUE FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_2 = 1
		SET @STR_WHERE_2 = @STR_WHERE_2 + ' STATUS = ' + '''' + 'Past Due' + '''' + ' or '
	END

IF (SELECT [OPEN] FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_2 = 1
		SET @STR_WHERE_2 = @STR_WHERE_2 + ' STATUS = ' + '''' + 'Open' + '''' + ' or '
	END

IF (SELECT COMPLETED_ON_TIME FROM @var) = 1 
	BEGIN
		SET @QUERY_FLAG_2 = 1
		SET @STR_WHERE_2 = @STR_WHERE_2 + ' STATUS = ' + '''' + 'Completed On Time' + '''' + ' or '
	END

IF (SELECT COMPLETED_LATE FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_2 = 1
		SET @STR_WHERE_2 = @STR_WHERE_2 + ' STATUS = ' + '''' + 'Completed Late' + '''' + ' or '
	END
-- Clean up status filter
IF @QUERY_FLAG_2 = 1 AND RIGHT(@STR_WHERE_2,3) = 'or '
	BEGIN
		SET @STR_WHERE_2 = LEFT(@STR_WHERE_2,len(@STR_WHERE_2)-3)	
	END
SET @STR_WHERE_2 = @STR_WHERE_2 + ' ) and '




-- Add CLASS filter
SET @STR_WHERE_3 = @STR_WHERE_3 + '('
IF (SELECT MM FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_3 = 1
		SET @STR_WHERE_3 = @STR_WHERE_3 + ' CLASS = ' + '''' + 'MM' + '''' + ' or '
	END

IF (SELECT CPI FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_3 = 1
		SET @STR_WHERE_3 = @STR_WHERE_3 + ' CLASS = ' + '''' + 'CPI' + '''' + ' or '
	END

IF (SELECT TSR FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_3 = 1
		SET @STR_WHERE_3 = @STR_WHERE_3 + ' CLASS = ' + '''' + 'TSR' + '''' + ' or '
	END

IF (SELECT DELIV FROM @var) = 1
	BEGIN
		SET @QUERY_FLAG_3 = 1
		SET @STR_WHERE_3 = @STR_WHERE_3 + ' CLASS = ' + '''' + 'Deliv' + '''' + ' or '
	END
--Clean up CLASS filter
IF @QUERY_FLAG_3 = 1 AND RIGHT(@STR_WHERE_3,3) = 'or '
	BEGIN
		SET @STR_WHERE_3 = LEFT(@STR_WHERE_3,len(@STR_WHERE_3)-3)
	END
SET @STR_WHERE_3 = @STR_WHERE_3 + ') and '

-- Create and clean up final WHERE clause string
IF @QUERY_FLAG_1 = 1 OR @QUERY_FLAG_2 = 1 OR @QUERY_FLAG_3 = 1
	BEGIN
		SET @STR_WHERE_FINAL = ' WHERE ' + @STR_WHERE_1 + @STR_WHERE_2 + @STR_WHERE_3
		-- Clean up final WHERE string
		SET @STR_WHERE_FINAL = LEFT(@STR_WHERE_FINAL,len(@STR_WHERE_FINAL)-3)
	END

--Debugging
	--SELECT * FROM @var
--	SELECT @STR_WHERE_1 as ONE
--	SELECT @STR_WHERE_2 as TWO
--	SELECT @STR_WHERE_3 as THREE
--	SELECT @STR_WHERE_FINAL as WHERE_CLAUSE
--	SELECT (@STR_SELECT + @STR_WHERE_FINAL + ' ORDER BY DUE_DATE')as full_text

--Execute procedure 
EXEC(@STR_SELECT + @STR_WHERE_FINAL + ' ORDER BY DUE_DATE')
--sp_EXECUTESQL[@STR_SELECT + @STR_WHERE_FINAL + ' ORDER BY DUE_DATE'])

END

Thee table valued function specified is this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_NickManagerReport_List] 
(     

)
RETURNS @table TABLE
(
      [CLASS] VARCHAR(MAX),
      ASSIGNED_TO_BEMS INT,
      ASSIGNED_TO_NAME VARCHAR(MAX),
      MANAGER_BEMS INT,
      MANAGER_NAME VARCHAR(MAX),
      DUE_DATE DATETIME,
      COMPLETION_DATE DATETIME,
      CHANGE_NUMBER VARCHAR(MAX),
      [GROUP_SUBGROUP] VARCHAR(MAX),
      EFFECTIVITY VARCHAR(50),
      [CPI_NUMBER] VARCHAR(MAX),
      [STATUS] VARCHAR(MAX),
      [SYSTEM] VARCHAR(MAX),
      CP_NUM VARCHAR(50),
      CP_REV VARCHAR(50),
      DELIVERABLE_DESCRIPTION VARCHAR(MAX),
      [DELIVERABLE_NUMBER] VARCHAR(MAX),
      TASK_REFERENCE VARCHAR(MAX)
)
AS
BEGIN
	DECLARE @TIMESTAMP DATETIME
	SET @TIMESTAMP  = GETDATE()
	
	INSERT @table
	SELECT 
		  [CLASS],
		  ASSIGNED_TO_BEMS,
		  ASSIGNED_TO_NAME,
		  MANAGER_BEMS,
		  MANAGER_NAME,
		  DUE_DATE,
		  COMPLETION_DATE,
		  CHANGE_NUMBER + ' - ' + CHANGE_TITLE AS [CHANGE_NUMBER],
		  [GROUP] + ' - ' + SUBGROUP AS [GROUP_SUBGROUP],
		  EFFECTIVITY,
		  cast(CP_NUM as varchar(MAX)) + '-' + cast(CPI_NUMBER as varchar(MAX)) AS [CPI_NUMBER],
		  CASE 	 
			WHEN @TIMESTAMP > DUE_DATE AND COMPLETION_DATE IS NULL THEN 'Past Due'
			WHEN @TIMESTAMP < DUE_DATE AND COMPLETION_DATE IS NULL THEN 'Open'
			WHEN GETDATE() > DUE_DATE AND COMPLETION_DATE IS NULL THEN 'Past Due'
			WHEN GETDATE() < DUE_DATE AND COMPLETION_DATE IS NULL THEN 'Open'
			WHEN COMPLETION_DATE < DUE_DATE THEN 'Completed On Time'
			WHEN COMPLETION_DATE > DUE_DATE THEN 'Completed Late'             
		  END as STATUS,
		  SYSTEM + ' - ' + SUBSYSTEM AS [SYSTEM_SUBSYSTEM],
		  CP_NUM,
		  CP_REV,
		  DELIVERABLE_DESCRIPTION,
		  cast(CP_NUM as varchar(MAX)) + '-' + cast(DELIVERABLE_NUMBER as varchar(MAX))
			AS [DELIVERABLE_NUMBER],
		  TASK_REFERENCE
	FROM 
		  dbo.xx_NICK_TEMP_TABLE
RETURN
END

And the table the function pulls from is:

USE [CPF_test]
GO
/****** Object:  Table [dbo].[xx_NICK_TEMP_TABLE]    Script Date: 03/09/2009 09:47:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[xx_NICK_TEMP_TABLE](
	[CLASS] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ASSIGNED_TO_BEMS] [int] NULL,
	[ASSIGNED_TO_NAME] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DUE_DATE] [datetime] NULL,
	[COMPLETION_DATE] [datetime] NULL,
	[CHANGE_NUMBER] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CHANGE_TITLE] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GROUP] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SUBGROUP] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EFFECTIVITY] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MANAGER_BEMS] [int] NULL,
	[MANAGER_NAME] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CPI_NUMBER] [int] NULL,
	[SYSTEM] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SUBSYSTEM] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CP_NUM] [int] NULL,
	[CP_REV] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DELIVERABLE_DESCRIPTION] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DELIVERABLE_NUMBER] [int] NULL,
	[TASK_REFERENCE] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Which you can add data to this table by executing:

DECLARE @CLASS varchar(max)
DECLARE @ASSIGNED_TO_BEMS int
DECLARE @ASSIGNED_TO_NAME varchar(max)
DECLARE @DUE_DATE datetime
DECLARE @COMPLETION_DATE datetime
DECLARE @CHANGE_NUMBER varchar(max)
DECLARE @CHANGE_TITLE varchar(max)
DECLARE @GROUP varchar(max)
DECLARE @SUBGROUP varchar(max)
DECLARE @EFFECTIVITY varchar(max)
DECLARE @MANAGER_BEMS int
DECLARE @MANAGER_NAME varchar(max)
DECLARE @CPI_NUMBER varchar(max)
DECLARE @STATUS varchar(max)
DECLARE @SYSTEM varchar(max)
DECLARE @SUBSYSTEM varchar(max)
DECLARE @CP_NUM int
DECLARE @CP_REV varchar(max)
DECLARE @DELIVERABLE_DESCRIPTION varchar(max)
DECLARE @DELIVERABLE_NUMBER int
DECLARE @TASK_REFERENCE varchar(max)

--OTHER PARAMATERS
DECLARE @TIMESTAMP datetime

-- USER PARAMATER VARIABLES
DECLARE @CLASS_TYPE int
DECLARE @STATUS_TYPE int
DECLARE @ValueOne int
DECLARE @ValueTwo int
DECLARE @int1 int
DECLARE @int2 int


----------------------BEGIN USER PARAMATERS----------------------------
SET @ASSIGNED_TO_BEMS = 1631350--1801478 -- Romo: 1631350
SET @CLASS_TYPE = 3 -- 1=MM, 2=CPI, 3=TSR, 4=Deliv
SET @STATUS_TYPE = 4 -- 1= open, 2= past due, 3=completed late, 4=completed on time

SET @ValueOne = 1
SET @ValueTwo = 1

SET @CP_NUM = 1234
SET @CP_REV = 'A'
SET @EFFECTIVITY = 'test2'
SET @CHANGE_NUMBER = 'Change number is not really a number'
SET @CHANGE_TITLE = 'MMChangeTitle'
SET @GROUP = 'GroupSomething'
SET @SUBGROUP = 'SubgroupOrOther'
SET @CPI_NUMBER = 1
SET @SYSTEM = 'SystemTheWhatie'
SET @SUBSYSTEM = 'SubsystemChupathingie'
SET @DELIVERABLE_NUMBER = 1
SET @DELIVERABLE_DESCRIPTION = 'Deliverable blah blah deliverable'


---------------------- END USER PARAMATERS ----------------------------


----------------------BEGIN CALCULATED PARAMATERS----------------------------
SET @TIMESTAMP = getdate()
SET @ASSIGNED_TO_NAME = (SELECT [NAME] FROM dbo.CAPTO_USER WHERE BEMSID = @ASSIGNED_TO_BEMS)
SET @MANAGER_BEMS = (SELECT MANAGER_BEMSID FROM dbo.CAPTO_USER WHERE BEMSID = @ASSIGNED_TO_BEMS)
SET @MANAGER_NAME = (
						SELECT m.[NAME] 
						FROM dbo.CAPTO_USER cp 
						JOIN dbo.CAPTO_USER m 
						ON cp.MANAGER_BEMSID = m.BEMSID
						WHERE cp.BEMSID= @ASSIGNED_TO_BEMS
					)

-- changes variance for due/completion dates
SET @int1 = @ValueOne * RAND() + 1
SET @int2 = @ValueTwo * RAND() + 1


----------------------BEGIN CASE PARAMATERS----------------------------
--SET STATUS_TYPE PARAMATERS
IF @STATUS_TYPE = 1 -- open
	BEGIN
		SET @DUE_DATE = @TIMESTAMP + @int1
		SET @STATUS = 'Open'
	END
IF @STATUS_TYPE = 2 -- past due
	BEGIN
		SET @DUE_DATE = @TIMESTAMP - @int1
		SET @STATUS = 'Past Due'
	END
IF @STATUS_TYPE = 3 -- completed late
	BEGIN
		SET @DUE_DATE = @TIMESTAMP - @int1
		SET @COMPLETION_DATE = @TIMESTAMP + @int2
		SET @STATUS = 'Completed Late'
	END
IF @STATUS_TYPE = 4 -- completed on time
	BEGIN
		SET @DUE_DATE = @TIMESTAMP- @int1
		SET @COMPLETION_DATE = @TIMESTAMP - @int1 - @int2
		SET @STATUS = 'Completed On Time'
	END

--SET CLASS_TYPE_PARAMATERS
IF @CLASS_TYPE = 1 -- MM
	BEGIN
		SET @CLASS = 'MM'
		SET @TASK_REFERENCE = 'CHANGE_NUMBER, CHANGE_TITLE, GROUP - SUBGROUP'
	END
IF @CLASS_TYPE = 2 -- CPI
	BEGIN
		SET @CLASS = 'CPI'
		SET @TASK_REFERENCE = 'CP_NUM-CPI_NUM, System - Subsystem'
	END
IF @CLASS_TYPE = 3 -- TSR
	BEGIN
		SET @CLASS = 'TSR'
		SET @TASK_REFERENCE = 'CP & CP_REV, SYSTEM - SUBSYSTEM'
	END
IF @CLASS_TYPE = 4 -- Deliv
	BEGIN
		SET @CLASS = 'Deliv'
		SET @TASK_REFERENCE = 'CP_NUM-DELIVERABLE_NUMBER, DELIVERABLE_DESCRIPTION'	
	END
---------------------- END CASE PARAMATERS ----------------------------

---------------------- BEGIN INSERT STATEMENTS ----------------------------

IF @CLASS_TYPE = 1 -- MM
	BEGIN
			INSERT INTO dbo.xx_NICK_TEMP_TABLE 
			(
				CLASS
				,ASSIGNED_TO_BEMS
				,ASSIGNED_TO_NAME
				,DUE_DATE
				,COMPLETION_DATE
				,CHANGE_NUMBER
				,CHANGE_TITLE
				,[GROUP]
				,SUBGROUP
				,EFFECTIVITY
				,MANAGER_BEMS
				,MANAGER_NAME
--				,STATUS
				,TASK_REFERENCE
			)

			VALUES
			(
				@CLASS
				,@ASSIGNED_TO_BEMS
				,@ASSIGNED_TO_NAME
				,@DUE_DATE --DUE DATE 
				,@COMPLETION_DATE --COMPLETED DATE
				,@CHANGE_NUMBER
				,@CHANGE_TITLE
				,@GROUP
				,@SUBGROUP
				,@EFFECTIVITY
				,@MANAGER_BEMS
				,@MANAGER_NAME
				--,@STATUS
				,@TASK_REFERENCE
			)
	END

IF @CLASS_TYPE = 2 -- CPI
	BEGIN
			INSERT INTO dbo.xx_NICK_TEMP_TABLE 
			(
				CLASS
				,ASSIGNED_TO_BEMS
				,ASSIGNED_TO_NAME
				,DUE_DATE
				,COMPLETION_DATE
				,SYSTEM
				,SUBSYSTEM
				,EFFECTIVITY
				,MANAGER_BEMS
				,MANAGER_NAME
				--,STATUS
				,TASK_REFERENCE
			)

			VALUES
			(
				@CLASS
				,@ASSIGNED_TO_BEMS
				,@ASSIGNED_TO_NAME
				,@DUE_DATE --DUE DATE 
				,@COMPLETION_DATE --COMPLETED DATE
				,@SYSTEM
				,@SUBSYSTEM
				,@EFFECTIVITY
				,@MANAGER_BEMS
				,@MANAGER_NAME
				--,@STATUS
				,@TASK_REFERENCE
			)
	END

IF @CLASS_TYPE = 3 -- TSR
	BEGIN
			INSERT INTO dbo.xx_NICK_TEMP_TABLE 
			(
				CLASS
				,ASSIGNED_TO_BEMS
				,ASSIGNED_TO_NAME
				,DUE_DATE
				,COMPLETION_DATE --COMPLETED DATE
				,CP_NUM
				,CP_REV
				,SYSTEM
				,SUBSYSTEM
				,EFFECTIVITY
				,MANAGER_BEMS
				,MANAGER_NAME
				--,STATUS
				,TASK_REFERENCE
			)

			VALUES
			(
				@CLASS
				,@ASSIGNED_TO_BEMS
				,@ASSIGNED_TO_NAME
				,@DUE_DATE --DUE DATE 
				,@COMPLETION_DATE --COMPLETED DATE
				,@CP_NUM
				,@CP_REV
				,@SYSTEM
				,@SUBSYSTEM
				,@EFFECTIVITY
				,@MANAGER_BEMS
				,@MANAGER_NAME
				--,@STATUS
				,@TASK_REFERENCE
			)
	END

IF @CLASS_TYPE = 4 -- Deliverable
	BEGIN
			INSERT INTO dbo.xx_NICK_TEMP_TABLE 
			(
				CLASS
				,ASSIGNED_TO_BEMS
				,ASSIGNED_TO_NAME
				,DUE_DATE
				,COMPLETION_DATE --COMPLETED DATE
				,CP_NUM
				,DELIVERABLE_NUMBER
				,DELIVERABLE_DESCRIPTION
				,EFFECTIVITY
				,MANAGER_BEMS
				,MANAGER_NAME
				--,STATUS
				,TASK_REFERENCE
			)

			VALUES
			(
				@CLASS
				,@ASSIGNED_TO_BEMS
				,@ASSIGNED_TO_NAME
				,@DUE_DATE --DUE DATE 
				,@COMPLETION_DATE --COMPLETED DATE
				,@CP_NUM
				,@DELIVERABLE_NUMBER
				,@DELIVERABLE_DESCRIPTION
				,@EFFECTIVITY
				,@MANAGER_BEMS
				,@MANAGER_NAME
				--,@STATUS
				,@TASK_REFERENCE
			)
	END

There is a section in that last that lets you change parameters if you wish to see specific records.

Thanks again for helping!

[Frinavale]

This wasn't what I was expecting.
Didn't you say that you tested your stored procedure and it worked fine?
Or is your stored procedure not working?

I was expecting to see C# code...

[nickvans]

Oh, I'm sorry. I misunderstood what you were asking for. Yes, the SQL code executes properly. I thought you were wanting to re-create the datasource.

Here is the code for the sqlDataSource:

               <asp:SqlDataSource ID="SqlDataSourceRunCustomReport" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:CPF_TestConnectionString %>" 
                    SelectCommand="xx_NickTempManagerReportCustom3_List" 
                    SelectCommandType="StoredProcedure" 
                    onselecting="SqlDataSourceRunCustomReport_Selecting" 
                    ProviderName="<%$ ConnectionStrings:CPF_TestConnectionString.ProviderName %>">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="RadioButtonUseDefault" Name="USE_DEFAULT" 
                            PropertyName="Checked" Type="Int32" />
                        <asp:ControlParameter ControlID="TextBox8" Name="USER_BEMSID" 
                            PropertyName="Text" Type="Int32" />
                        <asp:ControlParameter ControlID="ddl_Fwd" Name="DAYS_FWD" 
                            PropertyName="SelectedValue" Type="Int32" />
                        <asp:ControlParameter ControlID="ddl_Back" Name="DAYS_BACK" 
                            PropertyName="SelectedValue" Type="Int32" />
                        <asp:ControlParameter ControlID="cb_PastDue" Name="PAST_DUE" 
                            PropertyName="Checked" Type="Int32" />
                        <asp:ControlParameter ControlID="cb_CompletedOnTime" Name="COMPLETED_ON_TIME" 
                            PropertyName="Checked" Type="Int32" />
                        <asp:ControlParameter ControlID="cb_Open" Name="OPEN" PropertyName="Checked" 
                            Type="Int32" />
                        <asp:ControlParameter ControlID="cb_MM" Name="MM" PropertyName="Checked" 
                            Type="Int32" />
                        <asp:ControlParameter ControlID="cb_CPI" Name="CPI" PropertyName="Checked" 
                            Type="Int32" />
                        <asp:ControlParameter ControlID="cb_TSR" Name="TSR" PropertyName="Checked" 
                            Type="Int32" />
                        <asp:ControlParameter ControlID="cb_Deliv" Name="DELIV" PropertyName="Checked" 
                            Type="Int32" />
                        <asp:ControlParameter ControlID="tb_ASSIGNEE" Name="ASSIGNED_TO_BEMS" 
                            PropertyName="Text" Type="Int32" />
                        <asp:ControlParameter ControlID="tb_Effectivity" Name="SPECIFIED_EFFECTIVITY" 
                            PropertyName="Text" Type="String" />
                        <asp:ControlParameter ControlID="cb_CompletedLate" Name="COMPLETED_LATE" 
                            PropertyName="Checked" Type="Int32" />
                    </SelectParameters>
                </asp:SqlDataSource>

[Frinavale]

One more thing, please post the Only the code for GridView4 ....
Do you have AutoGenerateCol umns = "false"??

[nickvans]

Here is the code for GridView4:

<asp:GridView ID="GridView4" runat="server" 
                    DataSourceID="SqlDataSourceRunCustomReport" 
                    ondatabound="GridView4_DataBound">
                </asp:GridView>

I've tried with both auto-generate columns true and false.

[Frinavale]

In all of that SQL script I did not see a stored procedure named "xx_NickTempMan agerReportCusto m3_List"....... .....


What stored procedure are you supposed to be calling?


P.S. please post code in [code] tags, not [quote] tags ;)

[nickvans]

I renamed the procedure to "xx_ReportCusto m3_List" when I made it a CREATE procedure to make sure it executed properly after I added some comments.

xx_NickTempMana gerReportCustom 3_List is the correct stored procedure.

PS: sorry about using quote rather than code! I'll revise the post accordingly.

Edit: Frinavale made it code rather than quotes for me already. Thanks!

[Frinavale]

I was hoping that you were doing something wrong in your asp or c# code, but I don't see anything wrong.

You're going to have to excuse me for my ignorance here because I haven't actually created a stored procedure since first year college and haven't created one quite as complicated as yours. Check out this article and see if it helps you....hopefull y someone with more stored procedure experience can help you.

Are you absolutely sure that your stored procedure returns data?

[nickvans]

Well, thanks for trying to help. I'm sure the stored procedure runs correctly and returns data. Whats more, the SQLDataSource returns data when I go through the wizard and click "Test". The darn gridview just won't show up. I'm stumped.

Thanks for trying!

If anyone has any ideas, I'm all ears (err-- eyes).

[Frinavale]

I'm going to have to set up MSSql Server tonight and give this a try later because I'm curious as to why this isn't working as well.

[nickvans]

I'd certainly appreciate it, Frinavale!

[NeoPa]

Nick.

I think what you need to do here before anyone else is likely to jump in and help, is to reduce your real world application into a much smaller testbed application.

It's very hard to work on someone else's code - especially with no access to the system they're working on. 350+ lines of (just the SQL) code is too much to work with, especially as the great majority of it is red herring info.

As I say above, I suggest you strip out all non-essential (to illustrating the bug) code and, after ensuring it still exhibits the same problem, post that with an explanation if necessary. This will reduce the work required to help you to a level which our volunteer helpers are more likely to find acceptable.

This is a standard debugging technique, which I would recommend in most cases anyway. Even in smaller, less complicated scenarios, helpers will appreciate that you've gone to the trouble to reduce how much you need them to do to help you. Don't be surprised if you sometimes find you no longer need to post the question though, as the answer will become clear to you anyway.

[nickvans]

Thanks for the RE: NeoPa. I'll give it a shot and see if I can replicate it with a simpler scenario. I'll post my findings.

Thanks!