TreeView control checked based on if records exist

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

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...5e-a16566bd6c9f

Just guessing but I hope it helps,

Cor

"Ryan" <Tyveil@newsgroups.nospam> schreef in bericht
news:e5cpPYEoGHA.1592@TK2MSFTNGP04.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
>
>

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(Product and User_Product) and two
TableAdapters(ProductTableAdapter and User_mm_ProductTableAdapter).

2. Right-click the User_mm_ProductTableAdapter 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(object sender, EventArgs e)
{
DataSet1TableAdapters.ProductTableAdapter productAdapter = new
DataSet1TableAdapters.ProductTableAdapter();
productAdapter.Fill(dataset.Product);

DataSet1TableAdapters.User_ProductTableAdapter
userproductAdapter = new DataSet1TableAdapters.User_ProductTableAdapter();
userproductAdapter.Fill(dataset.User_Product, 1);

DataRelation relation = new DataRelation("relation1",
dataset.Product.Columns["ProductID"],
dataset.User_Product.Columns["ProductID"]);
dataset.Relations.Add(relation);

ConstructTreeView();
}
// method to populate treeView1
private void ConstructTreeView()
{
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]["ProductName"].ToString();
// assign the value of the ProductID field to the node's Tag
node.Tag = dataset.Product[i]["ProductID"];

if
(dataset.Product.Rows[i].GetChildRows("relation1").Length > 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(object 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.Product.FindByProductID(Convert.ToInt32(node.Tag)).GetChildRow
s("relation1").Length == 0)
{
// add your code to insert a new record in
User_mm_Product
}
}
else
{
if
(this.dataset.Product.FindByProductID(Convert.ToInt32(node.Tag)).GetChildRow
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/subscript...ault.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,

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

:-)

Cor

"Linda Liu [MSFT]" <v-lliu@online.microsoft.com> schreef in bericht
news:VWG54LLoGHA.2028@TK2MSFTNGXA01.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(Product and User_Product) and two
> TableAdapters(ProductTableAdapter and User_mm_ProductTableAdapter).
>
> 2. Right-click the User_mm_ProductTableAdapter 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(object sender, EventArgs e)
> {
> DataSet1TableAdapters.ProductTableAdapter productAdapter = new
> DataSet1TableAdapters.ProductTableAdapter();
> productAdapter.Fill(dataset.Product);
>
> DataSet1TableAdapters.User_ProductTableAdapter
> userproductAdapter = new DataSet1TableAdapters.User_ProductTableAdapter();
> userproductAdapter.Fill(dataset.User_Product, 1);
>
> DataRelation relation = new DataRelation("relation1",
> dataset.Product.Columns["ProductID"],
> dataset.User_Product.Columns["ProductID"]);
> dataset.Relations.Add(relation);
>
> ConstructTreeView();
> }
> // method to populate treeView1
> private void ConstructTreeView()
> {
> 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]["ProductName"].ToString();
> // assign the value of the ProductID field to the node's Tag
> node.Tag = dataset.Product[i]["ProductID"];
>
> if
> (dataset.Product.Rows[i].GetChildRows("relation1").Length > 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(object 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.Product.FindByProductID(Convert.ToInt32(node.Tag)).GetChildRow
> s("relation1").Length == 0)
> {
> // add your code to insert a new record in
> User_mm_Product
> }
> }
> else
> {
> if
> (this.dataset.Product.FindByProductID(Convert.ToInt32(node.Tag)).GetChildRow
> 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/subscript...ault.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.
>

Hi Cor,

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

Dim dataset As DataSet1 = New DataSet1()

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim productAdapter As DataSet1TableAdapters.ProductTableAdapter =
New DataSet1TableAdapters.ProductTableAdapter()
productAdapter.Fill(dataset.Product)

Dim userproductAdapter As
DataSet1TableAdapters.User_ProductTableAdapter = New
DataSet1TableAdapters.User_ProductTableAdapter()
userproductAdapter.Fill(dataset.User_Product, 1)

Dim relation As DataRelation = New DataRelation("relation1",
dataset.Product.Columns("ProductID"),
dataset.User_Product.Columns("ProductID"))
dataset.Relations.Add(relation)

PopulateTreeView()

End Sub

Private Sub PopulateTreeView()
TreeView1.CheckBoxes = 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)("ProductID").ToString() & "
" & _
dataset.Product.Rows(i)("ProductName").ToString()
node.Tag = dataset.Product.Rows(i)("ProductID")

If (dataset.Product.Rows(i).GetChildRows("relation1").Length >
0) Then
node.Checked = True
Else
node.Checked = False
End If

TreeView1.Nodes.Add(node)
Next
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) 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.Product.FindByProductID(CType(node.Tag,
Int32)).GetChildRows("relation1").Length = 0) Then
' add your code to insert a new record into
User_mm_Product table
End If
Else
If (dataset.Product.FindByProductID(CType(node.Tag,
Int32)).GetChildRows("relation1").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/subscript...ault.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,

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

To take one what I mean.

>Dim userproductAdapter As
>DataSet1TableAdapters.User_ProductTableAdapter = New
>DataSet1TableAdapters.User_ProductTableAdapter()

Dim userproductAdapter As New DataSet1TableAdapters.User_ProductTableAdapter

:-)

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

Thanks,

Cor


"Linda Liu [MSFT]" <v-lliu@online.microsoft.com> schreef in bericht
news:PjeHGDNoGHA.2028@TK2MSFTNGXA01.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(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim productAdapter As DataSet1TableAdapters.ProductTableAdapter =
> New DataSet1TableAdapters.ProductTableAdapter()
> productAdapter.Fill(dataset.Product)
>
> Dim userproductAdapter As
> DataSet1TableAdapters.User_ProductTableAdapter = New
> DataSet1TableAdapters.User_ProductTableAdapter()
> userproductAdapter.Fill(dataset.User_Product, 1)
>
> Dim relation As DataRelation = New DataRelation("relation1",
> dataset.Product.Columns("ProductID"),
> dataset.User_Product.Columns("ProductID"))
> dataset.Relations.Add(relation)
>
> PopulateTreeView()
>
> End Sub
>
> Private Sub PopulateTreeView()
> TreeView1.CheckBoxes = 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)("ProductID").ToString() & "
> " & _
> dataset.Product.Rows(i)("ProductName").ToString()
> node.Tag = dataset.Product.Rows(i)("ProductID")
>
> If (dataset.Product.Rows(i).GetChildRows("relation1").Length >
> 0) Then
> node.Checked = True
> Else
> node.Checked = False
> End If
>
> TreeView1.Nodes.Add(node)
> Next
> End Sub
>
> Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) 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.Product.FindByProductID(CType(node.Tag,
> Int32)).GetChildRows("relation1").Length = 0) Then
> ' add your code to insert a new record into
> User_mm_Product table
> End If
> Else
> If (dataset.Product.FindByProductID(CType(node.Tag,
> Int32)).GetChildRows("relation1").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/subscript...ault.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.
>

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-lliu@online.microsoft.com> wrote in message
news:PjeHGDNoGHA.2028@TK2MSFTNGXA01.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(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Button1.Click
> Dim productAdapter As DataSet1TableAdapters.ProductTableAdapter =
> New DataSet1TableAdapters.ProductTableAdapter()
> productAdapter.Fill(dataset.Product)
>
> Dim userproductAdapter As
> DataSet1TableAdapters.User_ProductTableAdapter = New
> DataSet1TableAdapters.User_ProductTableAdapter()
> userproductAdapter.Fill(dataset.User_Product, 1)
>
> Dim relation As DataRelation = New DataRelation("relation1",
> dataset.Product.Columns("ProductID"),
> dataset.User_Product.Columns("ProductID"))
> dataset.Relations.Add(relation)
>
> PopulateTreeView()
>
> End Sub
>
> Private Sub PopulateTreeView()
> TreeView1.CheckBoxes = 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)("ProductID").ToString() & "
> " & _
> dataset.Product.Rows(i)("ProductName").ToString()
> node.Tag = dataset.Product.Rows(i)("ProductID")
>
> If (dataset.Product.Rows(i).GetChildRows("relation1").Length >
> 0) Then
> node.Checked = True
> Else
> node.Checked = False
> End If
>
> TreeView1.Nodes.Add(node)
> Next
> End Sub
>
> Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) 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.Product.FindByProductID(CType(node.Tag,
> Int32)).GetChildRows("relation1").Length = 0) Then
> ' add your code to insert a new record into
> User_mm_Product table
> End If
> Else
> If (dataset.Product.FindByProductID(CType(node.Tag,
> Int32)).GetChildRows("relation1").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/subscript...ault.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.
>

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/subscript...ault.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.