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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

  
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:

Expand|Select|Wrap|Line Numbers

                <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:

Expand|Select|Wrap|Line Numbers

 <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.

Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

re: Dynamic Datagrid not showing up

Similar ASP.NET bytes

/bytes/development

/bytes/it

/bytes/about