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_Selected() 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 AutoGenerateColumns = "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_NickTempManagerReportCustom3_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_ReportCustom3_List" when I made it a CREATE procedure to make sure it executed properly after I added some comments.

xx_NickTempManagerReportCustom3_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....hopefully 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!

[NeoPa]

When you've done that I'll see what I can find that might explain the strange goings on. I must admit that I'm still only using MS-SQL 2K though, so there may be differences. Let's see what we have when we have it.

PS. Obviously don't lose what you have already. Do the debugging in a copy. It's often a single, simple change that's required after the problem is found and understood.

[nickvans]

Well, I did what NeoPa suggested and created a dummy stored procedure that was far simpler. I discovered two things.

1) For some reason, setting acceptable default values on the SQLServer side caused Visual Studio get angry and not display the gridview. As I said in my first post, the default values were acceptable and running the stored procedure with those default values caused the procedure to run correctly, so I'm at a bit of a loss.

2) If the stored procedure expects an argument that isn't specified when running a funky procedure like mine (that is, creating a string then executing it) it seems Visual Studio will ignore it completely. I discovered that if I don't specify a certain value (assigned_to_BEMSID) then the gridview just doesn't show up.

I have a bit of debugging to do yet. If I change values on my front end and say click my run button, the gridview doesn't refresh. If however I save those changed values as defaults (which runs a stored procedure to save them to a table on the SQL Server side) then run using default values, it does update.

EDIT: Turns out I'm tired and didn't have a control set for that variable in the SqlDataSource. I'm going to go home now.

In any case, thank you both very much for your help! I really appreciate the time you took trying to help a newb web designer.

Cheers!

[NeoPa]

Sleeping on it, is another very handy technique to help solve various problems. This is often associated with arcane magic, as the power to fix even the most convoluted problems is quite surprising ;)

Good luck and see what a fresh brain brings you tomorrow.