TreeView control checked based on if records exist

[Ryan]

OK, here's my setup. I have a treeview control that is populated with
records from a Product table, and it allows "checking". This is used for my
automatic notification system. Say a particular Product is sold, any users
who have that boxed checked will get a notification. So my (shortened)
database layout is as such:

Product Table
ProductID (PK)
ProductName

User Table (the users of the program)
UserID (PK)
UserName

User_mm_Product Table (keeps track of what users want to me notified of
sales for which products)
UserProductID (PK)
UserID (FK)
ProductID (FK)

So what I have been trying to do is populate a datatable that contains
ProductID, ProductName (this is the only field that shows in the TreeView),
UserID, and UserProductID. It shows one record for each record in the
Product Table (Left outter join). Then I can cycle through each of these
records one at a time - if the item is checked and UserID already exists, do
nothing - if the item is unchecked and UserID is Null, do nothing. If the
item is checked and UserID is Null, I need to INSERT a record into
User_mm_Product . If the item is unchecked and UserID is not null, I need to
delete the record for the current UserProductID.

Anyways, I keep getting a constraint error, probably because its trying to
populate UserID and UserProductID's with Null values when I .Fill the
datatable. Just looking for suggestions if their's an easier way to do this
or if I'm on the right track. Below is the stored procedure I created to
populate the datatable.

Thanks,
Ryan

SELECT P.ProductName, P.ProductID, U.UserID, U.UserProductID

FROM Product AS P LEFT OUTER JOIN

(SELECT ProductID, UserID, UserProductID

FROM User_mm_Product

WHERE (UserID = @UserID)) AS U ON P.ProductID = U.ProductID

[Cor Ligthert [MVP]]

Ryan,

If you want to use a datatable to update, forget than a select procedure
with a join.

Just select two tables and set them in a dataset. If the dataset has already
its relations in the dataset, than you can use in version 2005 the designer
to create a strongly typed dataset with the wizard for that. The than
created dataset has its constrains and relations.

Without that you can have a look at this example.

http://www.vb-tips.com/default.aspx?...e-a16566bd6c9f

Just guessing but I hope it helps,

Cor

"Ryan" <Ty****@newsgro ups.nospamschre ef in bericht
news:e5******** ******@TK2MSFTN GP04.phx.gbl...

OK, here's my setup. I have a treeview control that is populated with
records from a Product table, and it allows "checking". This is used for
my automatic notification system. Say a particular Product is sold, any
users who have that boxed checked will get a notification. So my
(shortened) database layout is as such:

Product Table
ProductID (PK)
ProductName

User Table (the users of the program)
UserID (PK)
UserName

User_mm_Product Table (keeps track of what users want to me notified of
sales for which products)
UserProductID (PK)
UserID (FK)
ProductID (FK)

So what I have been trying to do is populate a datatable that contains
ProductID, ProductName (this is the only field that shows in the
TreeView), UserID, and UserProductID. It shows one record for each record
in the Product Table (Left outter join). Then I can cycle through each of
these records one at a time - if the item is checked and UserID already
exists, do nothing - if the item is unchecked and UserID is Null, do
nothing. If the item is checked and UserID is Null, I need to INSERT a
record into User_mm_Product . If the item is unchecked and UserID is not
null, I need to delete the record for the current UserProductID.

Anyways, I keep getting a constraint error, probably because its trying to
populate UserID and UserProductID's with Null values when I .Fill the
datatable. Just looking for suggestions if their's an easier way to do
this or if I'm on the right track. Below is the stored procedure I
created to populate the datatable.

Thanks,
Ryan

SELECT P.ProductName, P.ProductID, U.UserID, U.UserProductID

FROM Product AS P LEFT OUTER JOIN

(SELECT ProductID, UserID, UserProductID

FROM User_mm_Product

WHERE (UserID = @UserID)) AS U ON P.ProductID = U.ProductID

[Linda Liu [MSFT]]

Hi Ryan,

Thank you for posting.

I don't think you need to put the two tables Product and User_mm_Product
into one table using join selection.

Instead, you could create a dataset and add two datatables into it. You
may add a data relation between the two datatables in the dataset. Then you
could use the data relation to see whether one record in the parent table
has corresponding records in child table.

Below is the steps of my solution.
1. Create a DataSet(e.g DataSet1) and pull the two tables(Product and
User_mm_Product ) from Server Explorer to the DataSet designer, which will
add two DataTables(Prod uct and User_Product) and two
TableAdapters(P roductTableAdap ter and User_mm_Product TableAdapter).

2. Right-click the User_mm_Product TableAdapter and select Configure
command. In the configuration wizard, add the where clause "where UserID =
@userid" to the select sql statement. Press Finish button.

3. Add a TreeView control(e.g treeView1) and two buttons(e.g button1 and
button2) on a form.

4. Fill the two datatables and populate the treeView1 with the data in the
DataTable Product in the handler for the button1 click event. Insert or
delete records in DataTable User_mm_Product according to the check status
of treenodes in treeView1.

The following is a sample code in the form.

DataSet1 dataset = new DataSet1();

// fill the two datatables and populate the treeView1
private void button1_Click(o bject sender, EventArgs e)
{
DataSet1TableAd apters.ProductT ableAdapter productAdapter = new
DataSet1TableAd apters.ProductT ableAdapter();
productAdapter. Fill(dataset.Pr oduct);

DataSet1TableAd apters.User_Pro ductTableAdapte r
userproductAdap ter = new DataSet1TableAd apters.User_Pro ductTableAdapte r();
userproductAdap ter.Fill(datase t.User_Product, 1);

DataRelation relation = new DataRelation("r elation1",
dataset.Product .Columns["ProductID"],
dataset.User_Pr oduct.Columns["ProductID"]);
dataset.Relatio ns.Add(relation );

ConstructTreeVi ew();
}
// method to populate treeView1
private void ConstructTreeVi ew()
{
this.treeView1. CheckBoxes = true;

TreeNode node;
for (int i = 0; i < dataset.Product .Rows.Count; i++)
{
node = new TreeNode();
node.Text = dataset.Product[i]["ProductID"].ToString() + "
" + dataset.Product[i]["ProductNam e"].ToString();
// assign the value of the ProductID field to the node's Tag
node.Tag = dataset.Product[i]["ProductID"];

if
(dataset.Produc t.Rows[i].GetChildRows(" relation1").Len gth 0)
{
node.Checked = true;
}
else
{
node.Checked = false;
}
this.treeView1. Nodes.Add(node) ;
}
}
// insert or delete data in User_mm_Product table according to
check status in the treeView1
private void button2_Click(o bject sender, EventArgs e)
{
TreeNode node;
for (int i = 0; i < this.treeView1. Nodes.Count; i++)
{
node = this.treeView1. Nodes[i];
if (node.Checked)
{
if
(this.dataset.P roduct.FindByPr oductID(Convert .ToInt32(node.T ag)).GetChildRo w
s("relation1"). Length == 0)
{
// add your code to insert a new record in
User_mm_Product
}
}
else
{
if
(this.dataset.P roduct.FindByPr oductID(Convert .ToInt32(node.T ag)).GetChildRo w
s("relation1"). Length 0)
{
// add your code to delete the record in
Use_mm_Product
}
}
}
}

Hope this helps.
If you have anything unclear, please don't hesitate to let me know.
Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== =============== ===============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

With newsgroups, MSDN subscribers enjoy unlimited, free support as opposed
to the limited number of phone-based technical support incidents. Complex
issues or server-down situations are not recommended for the newsgroups.
Issues of this nature are best handled working with a Microsoft Support
Engineer using one of your phone-based incidents.

=============== =============== =============== =============== =============== =
=============== =============== ===============
This posting is provided "AS IS" with no warranties, and confers no rights.

[Cor Ligthert [MVP]]

Linda,

I assume this was by accident but your code does not compile in by instance
Visual Basic Express.

:-)

Cor

"Linda Liu [MSFT]" <v-****@online.mic rosoft.comschre ef in bericht
news:VW******** ******@TK2MSFTN GXA01.phx.gbl.. .

Hi Ryan,

Thank you for posting.

I don't think you need to put the two tables Product and User_mm_Product
into one table using join selection.

Instead, you could create a dataset and add two datatables into it. You
may add a data relation between the two datatables in the dataset. Then
you
could use the data relation to see whether one record in the parent table
has corresponding records in child table.

Below is the steps of my solution.
1. Create a DataSet(e.g DataSet1) and pull the two tables(Product and
User_mm_Product ) from Server Explorer to the DataSet designer, which will
add two DataTables(Prod uct and User_Product) and two
TableAdapters(P roductTableAdap ter and User_mm_Product TableAdapter).

2. Right-click the User_mm_Product TableAdapter and select Configure
command. In the configuration wizard, add the where clause "where UserID =
@userid" to the select sql statement. Press Finish button.

3. Add a TreeView control(e.g treeView1) and two buttons(e.g button1 and
button2) on a form.

4. Fill the two datatables and populate the treeView1 with the data in
the
DataTable Product in the handler for the button1 click event. Insert or
delete records in DataTable User_mm_Product according to the check status
of treenodes in treeView1.

The following is a sample code in the form.

DataSet1 dataset = new DataSet1();

// fill the two datatables and populate the treeView1
private void button1_Click(o bject sender, EventArgs e)
{
DataSet1TableAd apters.ProductT ableAdapter productAdapter = new
DataSet1TableAd apters.ProductT ableAdapter();
productAdapter. Fill(dataset.Pr oduct);

DataSet1TableAd apters.User_Pro ductTableAdapte r
userproductAdap ter = new DataSet1TableAd apters.User_Pro ductTableAdapte r();
userproductAdap ter.Fill(datase t.User_Product, 1);

DataRelation relation = new DataRelation("r elation1",
dataset.Product .Columns["ProductID"],
dataset.User_Pr oduct.Columns["ProductID"]);
dataset.Relatio ns.Add(relation );

ConstructTreeVi ew();
}
// method to populate treeView1
private void ConstructTreeVi ew()
{
this.treeView1. CheckBoxes = true;

TreeNode node;
for (int i = 0; i < dataset.Product .Rows.Count; i++)
{
node = new TreeNode();
node.Text = dataset.Product[i]["ProductID"].ToString() + "
" + dataset.Product[i]["ProductNam e"].ToString();
// assign the value of the ProductID field to the node's Tag
node.Tag = dataset.Product[i]["ProductID"];

if
(dataset.Produc t.Rows[i].GetChildRows(" relation1").Len gth 0)
{
node.Checked = true;
}
else
{
node.Checked = false;
}
this.treeView1. Nodes.Add(node) ;
}
}
// insert or delete data in User_mm_Product table according to
check status in the treeView1
private void button2_Click(o bject sender, EventArgs e)
{
TreeNode node;
for (int i = 0; i < this.treeView1. Nodes.Count; i++)
{
node = this.treeView1. Nodes[i];
if (node.Checked)
{
if
(this.dataset.P roduct.FindByPr oductID(Convert .ToInt32(node.T ag)).GetChildRo w
s("relation1"). Length == 0)
{
// add your code to insert a new record in
User_mm_Product
}
}
else
{
if
(this.dataset.P roduct.FindByPr oductID(Convert .ToInt32(node.T ag)).GetChildRo w
s("relation1"). Length 0)
{
// add your code to delete the record in
Use_mm_Product
}
}
}
}

Hope this helps.
If you have anything unclear, please don't hesitate to let me know.
Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== =============== ===============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

With newsgroups, MSDN subscribers enjoy unlimited, free support as opposed
to the limited number of phone-based technical support incidents. Complex
issues or server-down situations are not recommended for the newsgroups.
Issues of this nature are best handled working with a Microsoft Support
Engineer using one of your phone-based incidents.

=============== =============== =============== =============== =============== =
=============== =============== ===============
This posting is provided "AS IS" with no warranties, and confers no
rights.

[Linda Liu [MSFT]]

Hi Cor,

Ok, I will provide a sample code written in VB.NET.

Dim dataset As DataSet1 = New DataSet1()

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim productAdapter As DataSet1TableAd apters.ProductT ableAdapter =
New DataSet1TableAd apters.ProductT ableAdapter()
productAdapter. Fill(dataset.Pr oduct)

Dim userproductAdap ter As
DataSet1TableAd apters.User_Pro ductTableAdapte r = New
DataSet1TableAd apters.User_Pro ductTableAdapte r()
userproductAdap ter.Fill(datase t.User_Product, 1)

Dim relation As DataRelation = New DataRelation("r elation1",
dataset.Product .Columns("Produ ctID"),
dataset.User_Pr oduct.Columns(" ProductID"))
dataset.Relatio ns.Add(relation )

PopulateTreeVie w()

End Sub

Private Sub PopulateTreeVie w()
TreeView1.Check Boxes = True

Dim node As TreeNode
For i As Integer = 0 To dataset.Product .Rows.Count - 1
node = New TreeNode()
node.Text = dataset.Product .Rows(i)("Produ ctID").ToString () & "
" & _
dataset.Product .Rows(i)("Produ ctName").ToStri ng()
node.Tag = dataset.Product .Rows(i)("Produ ctID")

If (dataset.Produc t.Rows(i).GetCh ildRows("relati on1").Length >
0) Then
node.Checked = True
Else
node.Checked = False
End If

TreeView1.Nodes .Add(node)
Next
End Sub

Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button2.Click
Dim node As TreeNode
For i As Integer = 0 To TreeView1.Nodes .Count - 1
node = TreeView1.Nodes (i)
If (node.Checked) Then
If (dataset.Produc t.FindByProduct ID(CType(node.T ag,
Int32)).GetChil dRows("relation 1").Length = 0) Then
' add your code to insert a new record into
User_mm_Product table
End If
Else
If (dataset.Produc t.FindByProduct ID(CType(node.T ag,
Int32)).GetChil dRows("relation 1").Length 0) Then
' add your code to delete the record in User_mm_Product
table
End If
End If
Next
MessageBox.Show (message)

End Sub

Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== =============== ===============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

With newsgroups, MSDN subscribers enjoy unlimited, free support as opposed
to the limited number of phone-based technical support incidents. Complex
issues or server-down situations are not recommended for the newsgroups.
Issues of this nature are best handled working with a Microsoft Support
Engineer using one of your phone-based incidents.

=============== =============== =============== =============== =============== =
=============== =============== ===============
This posting is provided "AS IS" with no warranties, and confers no rights.

[Cor Ligthert [MVP]]

Linda,

Even that this is codetranslator translated C# code, it this confirm the
standard of the newsgroup.

To take one what I mean.

>Dim userproductAdap ter As
DataSet1TableA dapters.User_Pr oductTableAdapt er = New
DataSet1TableA dapters.User_Pr oductTableAdapt er()

Dim userproductAdap ter As New DataSet1TableAd apters.User_Pro ductTableAdapte r

:-)

However there is (probably I did not check it) nothing wrong with the code
you are suplying.

Thanks,

Cor
"Linda Liu [MSFT]" <v-****@online.mic rosoft.comschre ef in bericht
news:Pj******** ******@TK2MSFTN GXA01.phx.gbl.. .

Hi Cor,

Ok, I will provide a sample code written in VB.NET.

Dim dataset As DataSet1 = New DataSet1()

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim productAdapter As DataSet1TableAd apters.ProductT ableAdapter =
New DataSet1TableAd apters.ProductT ableAdapter()
productAdapter. Fill(dataset.Pr oduct)

Dim userproductAdap ter As
DataSet1TableAd apters.User_Pro ductTableAdapte r = New
DataSet1TableAd apters.User_Pro ductTableAdapte r()
userproductAdap ter.Fill(datase t.User_Product, 1)

Dim relation As DataRelation = New DataRelation("r elation1",
dataset.Product .Columns("Produ ctID"),
dataset.User_Pr oduct.Columns(" ProductID"))
dataset.Relatio ns.Add(relation )

PopulateTreeVie w()

End Sub

Private Sub PopulateTreeVie w()
TreeView1.Check Boxes = True

Dim node As TreeNode
For i As Integer = 0 To dataset.Product .Rows.Count - 1
node = New TreeNode()
node.Text = dataset.Product .Rows(i)("Produ ctID").ToString () & "
" & _
dataset.Product .Rows(i)("Produ ctName").ToStri ng()
node.Tag = dataset.Product .Rows(i)("Produ ctID")

If (dataset.Produc t.Rows(i).GetCh ildRows("relati on1").Length >
0) Then
node.Checked = True
Else
node.Checked = False
End If

TreeView1.Nodes .Add(node)
Next
End Sub

Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button2.Click
Dim node As TreeNode
For i As Integer = 0 To TreeView1.Nodes .Count - 1
node = TreeView1.Nodes (i)
If (node.Checked) Then
If (dataset.Produc t.FindByProduct ID(CType(node.T ag,
Int32)).GetChil dRows("relation 1").Length = 0) Then
' add your code to insert a new record into
User_mm_Product table
End If
Else
If (dataset.Produc t.FindByProduct ID(CType(node.T ag,
Int32)).GetChil dRows("relation 1").Length 0) Then
' add your code to delete the record in User_mm_Product
table
End If
End If
Next
MessageBox.Show (message)

End Sub

Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== =============== ===============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

With newsgroups, MSDN subscribers enjoy unlimited, free support as opposed
to the limited number of phone-based technical support incidents. Complex
issues or server-down situations are not recommended for the newsgroups.
Issues of this nature are best handled working with a Microsoft Support
Engineer using one of your phone-based incidents.

=============== =============== =============== =============== =============== =
=============== =============== ===============
This posting is provided "AS IS" with no warranties, and confers no
rights.

[Ryan]

Thanks for the code, Linda. I think I can simplify it by creating the
dataset using the designer during design-time, but this makes a lot of
sense. I've probably been getting carried away creating views and stored
procedures on the SQL backend where I could do the same just by taking a
look at the data existing in the dataset.

Ryan

"Linda Liu [MSFT]" <v-****@online.mic rosoft.comwrote in message
news:Pj******** ******@TK2MSFTN GXA01.phx.gbl.. .

Hi Cor,

Ok, I will provide a sample code written in VB.NET.

Dim dataset As DataSet1 = New DataSet1()

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim productAdapter As DataSet1TableAd apters.ProductT ableAdapter =
New DataSet1TableAd apters.ProductT ableAdapter()
productAdapter. Fill(dataset.Pr oduct)

Dim userproductAdap ter As
DataSet1TableAd apters.User_Pro ductTableAdapte r = New
DataSet1TableAd apters.User_Pro ductTableAdapte r()
userproductAdap ter.Fill(datase t.User_Product, 1)

Dim relation As DataRelation = New DataRelation("r elation1",
dataset.Product .Columns("Produ ctID"),
dataset.User_Pr oduct.Columns(" ProductID"))
dataset.Relatio ns.Add(relation )

PopulateTreeVie w()

End Sub

Private Sub PopulateTreeVie w()
TreeView1.Check Boxes = True

Dim node As TreeNode
For i As Integer = 0 To dataset.Product .Rows.Count - 1
node = New TreeNode()
node.Text = dataset.Product .Rows(i)("Produ ctID").ToString () & "
" & _
dataset.Product .Rows(i)("Produ ctName").ToStri ng()
node.Tag = dataset.Product .Rows(i)("Produ ctID")

If (dataset.Produc t.Rows(i).GetCh ildRows("relati on1").Length >
0) Then
node.Checked = True
Else
node.Checked = False
End If

TreeView1.Nodes .Add(node)
Next
End Sub

Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button2.Click
Dim node As TreeNode
For i As Integer = 0 To TreeView1.Nodes .Count - 1
node = TreeView1.Nodes (i)
If (node.Checked) Then
If (dataset.Produc t.FindByProduct ID(CType(node.T ag,
Int32)).GetChil dRows("relation 1").Length = 0) Then
' add your code to insert a new record into
User_mm_Product table
End If
Else
If (dataset.Produc t.FindByProduct ID(CType(node.T ag,
Int32)).GetChil dRows("relation 1").Length 0) Then
' add your code to delete the record in User_mm_Product
table
End If
End If
Next
MessageBox.Show (message)

End Sub

Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== =============== ===============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

With newsgroups, MSDN subscribers enjoy unlimited, free support as opposed
to the limited number of phone-based technical support incidents. Complex
issues or server-down situations are not recommended for the newsgroups.
Issues of this nature are best handled working with a Microsoft Support
Engineer using one of your phone-based incidents.

=============== =============== =============== =============== =============== =
=============== =============== ===============
This posting is provided "AS IS" with no warranties, and confers no
rights.

[Linda Liu [MSFT]]

Hi Ryan,

Thank you for your update.

Yes, you could simplify it by creating the DataSet and the data relation
using the designer during design-time.

I think it is a easier way to make use of DataSet and data relation in your
case.

If you have any other questions, please don't hesitate to contact us. It's
always our pleasure to be of assistance.

Have a nice day!
Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =============== =============== =
=============== =============== ===============
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

With newsgroups, MSDN subscribers enjoy unlimited, free support as opposed
to the limited number of phone-based technical support incidents. Complex
issues or server-down situations are not recommended for the newsgroups.
Issues of this nature are best handled working with a Microsoft Support
Engineer using one of your phone-based incidents.

=============== =============== =============== =============== =============== =
=============== =============== ===============
This posting is provided "AS IS" with no warranties, and confers no rights.