Calling all TreeView developers!!!

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

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]
Click to expand...
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...
 
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.



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 issetup exclusively for Testing.  Hig...
4       1000002 1       F5W82211        NULL
5       3       4       Dave Burkett    I gotta getthis figured out.  I'm tired of dra...
Click to expand...
 
Back
Top