Calling all TreeView developers!!!

Discussion in 'ASP .Net' started by Dave, Jul 9, 2010.

  1. Dave

    Dave Guest

    Can I get someone's opinion on the below please. I’m getting an
    'Object reference not set to an instance of an object' on the below
    TreeView code. Everything appears to work fine until I include the
    Cursor content. Thanks in advance!


    ________________________________________
    TreeView
    ________________________________________

    using System.Collections;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Web;
    using System.Web.UI.WebControls;

    namespace Web
    {
    public class VORTEXDynamicMenu
    {
    public TreeNode RefreshTreeMenu(string PU)
    {

    SqlConnection con = new
    SqlConnection(ConfigurationManager.ConnectionStrings["VORTEXConnectionString"].ConnectionString);
    SqlCommand cmd = new SqlCommand("SORTreeView_SELECT",
    con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@PU", PU);
    cmd.Parameters.AddWithValue("@RequestedBy",
    HttpContext.Current.User.Identity.Name.ToString());
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    Hashtable ht = new Hashtable();

    string top = "-1";
    int b = dt.Rows.Count;

    foreach (DataRow dr in dt.Rows)
    {
    TreeNode mi = new TreeNode();
    mi.Text = dr["Title"].ToString();
    mi.Value = dr["SORID"].ToString();
    mi.ToolTip = dr["Observation"].ToString();
    if (top == "-1")
    top = mi.Value;
    ht.Add(mi.Value, mi);
    if ((int)dr["Parent"] != 0)
    {
    string s = dr["Parent"].ToString();

    ((TreeNode)ht[dr["Parent"].ToString()]).ChildNodes.Add(mi);
    }
    }
    return (TreeNode)ht[top];
    }
    }
    }


    ________________________________________
    SQL STRING
    ________________________________________

    PROCEDURE [OAKDBS28\SQA2008].[VORTEX].[dbo].[SORTreeView_SELECT]
    @PU VARCHAR(5)
    ,@RequestedBy VARCHAR(50)
    AS
    SET XACT_ABORT ON;
    SET NOCOUNT ON;
    SET LOCK_TIMEOUT 5000

    DECLARE @CRSRVAR CURSOR
    DECLARE @ProjNo VARCHAR(8)
    DECLARE @Parent INT

    DECLARE @ProjTable AS TABLE([ID] INT IDENTITY(1,1) NOT NULL
    ,[ProjNo] VARCHAR(8))

    DECLARE @Table AS TABLE([ID] INT IDENTITY(1,1) NOT NULL
    ,[SORID] INT PRIMARY KEY
    ,[Parent] INT
    ,[Title] varchar(50)
    ,[Observation] varchar(50))


    INSERT INTO @Table([SORID]
    ,[Parent]
    ,[Title])
    VALUES(1000000
    ,0
    ,'New SOR')

    INSERT INTO @ProjTable([ProjNo])
    SELECT sT.[ProjNo]
    FROM [dbo].[SORTable] AS sT
    INNER JOIN [Oakdbs06].[GFS].[dbo].[ProjectInfoAll] AS p ON
    sT.[ProjNo] = p.[Project_Number]
    INNER JOIN [Oakdbs06].[GFS].[dbo].[PA_PROJECTS_ALL] AS pA
    ON p.[Project_Number] = pA.[Project_Number]
    WHERE(p.[PU] = @PU)
    AND(pA.[Project_Status_Code] IN ('Active',
    'Pending_Close'))
    ORDER BY sT.[ProjNo]


    SET @CRSRVAR = CURSOR FOR
    SELECT [ProjNo]
    FROM @ProjTable

    OPEN @CRSRVAR
    FETCH NEXT FROM @CRSRVAR
    INTO @ProjNo

    WHILE(@@FETCH_STATUS = 0)
    BEGIN
    INSERT INTO @Table([SORID]
    ,[Parent]
    ,[Title])
    SELECT 1000000 + [ID]
    ,1
    ,@ProjNo
    FROM @ProjTable
    WHERE([ProjNo] = @ProjNo)

    SELECT @Parent = [ID]
    FROM @Table
    WHERE([Title] = @ProjNo)

    INSERT INTO @Table([SORID]
    ,[Parent]
    ,[Title]
    ,[Observation])
    SELECT [SORID]
    ,@Parent
    ,[Name]
    ,[Observation] = CASE WHEN LEN([Observation])
    >= 47 THEN SUBSTRING([Observation],1,47) + '...' ELSE [Observation]

    END
    FROM [dbo].[SORTable]
    WHERE ([ProjNo] = @ProjNo)
    ORDER BY [modBy]

    FETCH NEXT FROM @CRSRVAR
    INTO @ProjNo
    END
    CLOSE @CRSRVAR
    DEALLOCATE @CRSRVAR

    SELECT *
    FROM @Table
    ORDER BY [ID]

    ________________________________________
    Data Returned
    ________________________________________

    ID SORID Parent Description Observation
    1 1000000 0 New SOR NULL
    2 1000001 1 F5W56101 NULL
    3 2 2 Dave Burkett This SOR is setup exclusively for Testing. Hig...
    4 1000002 1 F5W82211 NULL
    5 3 4 Dave Burkett I gotta get this figured out. I'm tired of dra...
    Dave, Jul 9, 2010
    #1
    1. Advertising

  2. Dave

    Dave Guest

    A friend pointed out that my problem existed on:

    ((TreeNode)ht[dr["Parent"].ToString()]).ChildNodes.Add(mi);

    You want to add the current node to its parent.
    The problem is, the hashtable key is the SORID, not the index of the
    item – it is expecting “1000000” as a parent, but instead gets “1”.
    Since there is no hashtable record with index “1”, you are trying to
    add a child node to an entry that doesn’t exist.

    In my example below I need to maintain the SORID as my index. So the
    solution was to modify my Stored Procedure so that my Parent value
    referenced the SORID value instead of the ID. Works fine now.



    On Jul 9, 8:59 am, Dave <> wrote:
    > Can I get someone's opinion on the below please.  I’m getting an
    > 'Object reference not set to an instance of an object' on the below
    > TreeView code.  Everything appears to work fine until I include the
    > Cursor content.   Thanks in advance!
    >
    > ________________________________________
    > TreeView
    > ________________________________________
    >
    > using System.Collections;
    > using System.Configuration;
    > using System.Data;
    > using System.Data.SqlClient;
    > using System.Web;
    > using System.Web.UI.WebControls;
    >
    > namespace Web
    > {
    >     public class VORTEXDynamicMenu
    >     {
    >         public TreeNode RefreshTreeMenu(string PU)
    >         {
    >
    >             SqlConnection con = new
    > SqlConnection(ConfigurationManager.ConnectionStrings["VORTEXConnectionStrin­g"].ConnectionString);
    >             SqlCommand cmd = new SqlCommand("SORTreeView_SELECT",
    > con);
    >             cmd.CommandType = CommandType.StoredProcedure;
    >             cmd.Parameters.AddWithValue("@PU", PU);
    >             cmd.Parameters.AddWithValue("@RequestedBy",
    > HttpContext.Current.User.Identity.Name.ToString());
    >             SqlDataAdapter da = new SqlDataAdapter(cmd);
    >             DataTable dt = new DataTable();
    >             da.Fill(dt);
    >             Hashtable ht = new Hashtable();
    >
    >             string top = "-1";
    >             int b = dt.Rows.Count;
    >
    >             foreach (DataRow dr in dt.Rows)
    >             {
    >                 TreeNode mi = new TreeNode();
    >                 mi.Text = dr["Title"].ToString();
    >                 mi.Value = dr["SORID"].ToString();
    >                 mi.ToolTip = dr["Observation"].ToString();
    >                 if (top == "-1")
    >                     top = mi.Value;
    >                 ht.Add(mi.Value, mi);
    >                 if ((int)dr["Parent"] != 0)
    >                 {
    >                     string s = dr["Parent"].ToString();
    >
    > ((TreeNode)ht[dr["Parent"].ToString()]).ChildNodes.Add(mi);
    >                 }
    >             }
    >             return (TreeNode)ht[top];
    >         }
    >     }
    >
    > }
    >
    > ________________________________________
    > SQL STRING
    > ________________________________________
    >
    > PROCEDURE [OAKDBS28\SQA2008].[VORTEX].[dbo].[SORTreeView_SELECT]
    >       @PU VARCHAR(5)
    >       ,@RequestedBy VARCHAR(50)
    > AS
    >       SET XACT_ABORT ON;
    >       SET NOCOUNT ON;
    >       SET LOCK_TIMEOUT 5000
    >
    >       DECLARE @CRSRVAR CURSOR
    >       DECLARE @ProjNo VARCHAR(8)
    >       DECLARE @Parent INT
    >
    >       DECLARE @ProjTable AS TABLE([ID] INT IDENTITY(1,1) NOT NULL
    >             ,[ProjNo] VARCHAR(8))
    >
    >       DECLARE @Table AS TABLE([ID] INT IDENTITY(1,1) NOT NULL
    >             ,[SORID] INT PRIMARY KEY
    >             ,[Parent] INT
    >             ,[Title] varchar(50)
    >             ,[Observation] varchar(50))
    >
    >       INSERT INTO @Table([SORID]
    >             ,[Parent]
    >             ,[Title])
    >       VALUES(1000000
    >             ,0
    >             ,'New SOR')
    >
    >       INSERT INTO @ProjTable([ProjNo])
    >       SELECT sT.[ProjNo]
    >       FROM [dbo].[SORTable] AS sT
    >             INNER JOIN [Oakdbs06].[GFS].[dbo].[ProjectInfoAll] AS p ON
    > sT.[ProjNo] = p.[Project_Number]
    >             INNER JOIN [Oakdbs06].[GFS].[dbo].[PA_PROJECTS_ALL] AS pA
    > ON p.[Project_Number] = pA.[Project_Number]
    >       WHERE(p.[PU] = @PU)
    >             AND(pA.[Project_Status_Code] IN ('Active',
    > 'Pending_Close'))
    >       ORDER BY sT.[ProjNo]
    >
    >       SET @CRSRVAR = CURSOR FOR
    >             SELECT [ProjNo]
    >             FROM @ProjTable
    >
    >       OPEN @CRSRVAR
    >       FETCH NEXT FROM @CRSRVAR
    >       INTO @ProjNo
    >
    >       WHILE(@@FETCH_STATUS = 0)
    >             BEGIN
    >                   INSERT INTO @Table([SORID]
    >                         ,[Parent]
    >                         ,[Title])
    >                   SELECT 1000000 + [ID]
    >                         ,1
    >                         ,@ProjNo
    >                   FROM @ProjTable
    >                   WHERE([ProjNo] = @ProjNo)
    >
    >                   SELECT @Parent = [ID]
    >                   FROM @Table
    >                   WHERE([Title] = @ProjNo)
    >
    >                   INSERT INTO @Table([SORID]
    >                         ,[Parent]
    >                         ,[Title]
    >                         ,[Observation])
    >                   SELECT [SORID]
    >                         ,@Parent
    >                         ,[Name]
    >                         ,[Observation] = CASE WHEN LEN([Observation])>= 47 THEN SUBSTRING([Observation],1,47) + '...' ELSE [Observation]
    >
    > END
    >                   FROM [dbo].[SORTable]
    >                   WHERE ([ProjNo] = @ProjNo)
    >                   ORDER BY [modBy]
    >
    >                   FETCH NEXT FROM @CRSRVAR
    >                   INTO @ProjNo
    >             END
    >       CLOSE @CRSRVAR
    >       DEALLOCATE @CRSRVAR
    >
    >       SELECT *
    >       FROM @Table
    >       ORDER BY [ID]
    >
    > ________________________________________
    > Data Returned
    > ________________________________________
    >
    > ID      SORID   Parent  Description     Observation
    > 1       1000000 0       New SOR NULL
    > 2       1000001 1       F5W56101        NULL
    > 3       2       2       Dave Burkett    This SOR is setup exclusively for Testing.  Hig...
    > 4       1000002 1       F5W82211        NULL
    > 5       3       4       Dave Burkett    I gotta get this figured out.  I'm tired of dra...
    Dave, Jul 9, 2010
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    415
    The Abrasive Sponge
    Jan 2, 2005
  2. Replies:
    0
    Views:
    609
  3. nokidding
    Replies:
    7
    Views:
    536
    Andy Dingley
    Jan 24, 2008
  4. Steve Ferg
    Replies:
    10
    Views:
    578
    Steve Ferg
    May 20, 2009
  5. Isaac
    Replies:
    0
    Views:
    574
    Isaac
    Oct 22, 2010
Loading...

Share This Page