Calling all TreeView developers!!!

Prev: connect to SQL Server DB from VWD express
Next: creating ActiveXobject in mozilla, invoking shell commands.

From: Dave on 9 Jul 2010 09:59

---

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

From: Dave on 9 Jul 2010 14:58

---

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 <Dave.Burk...(a)Jacobs.com> 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...

 | 
Pages: 1
Prev: connect to SQL Server DB from VWD express
Next: creating ActiveXobject in mozilla, invoking shell commands.