1 Replies - 4706 Views - Last Post: 18 June 2008 - 09:36 AM Rate Topic: -----

#1 Nava_gini  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 17-June 08

Displaying data in second combo box based from data from first combo b

Posted 17 June 2008 - 10:55 PM

When I select an item from the first combo box, the second combo box should be populated with items related to the first combo box selection. I am developing a system with a database, and the datasource is 'dsInventory'. This is where all the information to be displayed is retrieved. And there are binding sources to bind the datasource items to the combo boxes.

My question is how do I make the second combo box display ONLY sub values of each value of the first combo box.

But now the problem that I face is that all the items from the first combo box display the same sub items in the second combo box when it is selected. Ex :


First Combo Box selections :

COUVETURE
CREAM
DOUGH
BISCUITS
PM
CC , etc

Second Combo box :

If 'PM' from the first combo box is selected, then the items that should display are :

B
C
L
P
W

Now...I choose any item from the first combo box. The same list of items of the second combo box (B,C,L,P,W) display for all the items from the first combo box.

The idea is to just display the items related to the 1st combo box.

The software I have used in VB.net 2008 and Sql Server 2000.

Below is the coding for the section I want working. The bold section of the code is where the data source is linked to the table adapters.


Imports System.Windows.Forms.DataGrid
Imports System.Data.SqlClient

Public Class InventoryMainForm
	Private CatCodeDesc As String
	Private CatCode As String
	Private WarehouseLocation As String
	Private Warehouse As String
	Private UOMCode As String
	Private subcatcode As String
	Private subcat1code As String
	Private subcat2code As String
	Private subcat3code As String
	Private AddNew As Boolean
	Dim DQcheck As New SqlCommand
	Dim DHcheck As New SqlDataAdapter
	Dim DScheck As New DataSet
	Dim Dvcheck As New DataView
	
[b]Private Sub InventoryMainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Try
			Me.CountryTableAdapter.Fill(Me.DsSales.country)
			Me.Inv_itemsubcategoryTableAdapter.Fill(Me.dsInventory.inv_itemsubcategory)
			Me.Inv_itemlocTableAdapter.Fill(Me.dsInventory.inv_itemloc)
			Me.Inv_itemuomTableAdapter.Fill(Me.dsInventory.inv_itemuom)
			Me.Inv_itemcategoryTableAdapter.Fill(Me.dsInventory.inv_itemcategory)
			Me.Inv_itemCat3TableAdapter.Fill(Me.dsInventory.inv_itemCat3)
			Me.Inv_itemCat4TableAdapter.Fill(Me.dsInventory.inv_itemCat4)
			Me.Inv_itemCat5TableAdapter.Fill(Me.dsInventory.inv_itemCat5)
		Catch ex As Exception
			MessageBox.Show(ex.Message, Me.Text + " FrmLoad", MessageBoxButtons.OK, MessageBoxIcon.Information)
		End Try
	End Sub[/b]

 '=========Inventory Item Sub Category===========
	Private Sub subcatBindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorAddNewItem.Click
		subcatcode = cmbsubcatcode.SelectedValue
		cmbsubcatcode.Visible = False
		txtsubcat.Visible = True
		cmbitemcat.Text = ""
		txtsubcat.Text = ""
		txtsubcatdesc.Text = ""
		subcat(False)
		AddNew = True
		cmbitemcat.Focus()
	End Sub

	Private Sub subcat(ByVal check As Boolean)

		dgsubcategory.Enabled = check
		BindingNavigatorMoveFirstItem1.Enabled = check
		BindingNavigatorMoveLastItem1.Enabled = check
		BindingNavigatorMoveNextItem1.Enabled = check
		BindingNavigatorMovePreviousItem1.Enabled = check
		BindingNavigatorCountItem1.Enabled = check
		BindingNavigatorPositionItem1.Enabled = check

		subcatBindingNavigatorRefre****em.Visible = check
		subcatBindingNavigatorAddNewItem.Visible = check
		subcatBindingNavigatorEditItem.Visible = check
		cmbsubcatcode.Visible = check
		txtitemcat.Visible = check
		If check = False Then
			check = True
			subcatBindingNavigatorDeleteItem.Text = "Cancel"
		Else
			check = False
			subcatBindingNavigatorDeleteItem.Text = "Delete"
		End If

		subcatBindingNavigatorSaveItem.Visible = check
		txtsubcat.Visible = check
		cmbitemcat.Visible = check
		txtsubcatdesc.Enabled = check
	End Sub

	Private Sub subcatBindingNavigatorEditItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorEditItem.Click
		If dgsubcategory.RowCount > 0 Then
			subcatcode = cmbsubcatcode.SelectedValue
			subcat(False)
			txtsubcat.Enabled = False
			cmbitemcat.Enabled = False
			cmbitemcat.Focus()
		Else
			MessageBox.Show("No record to edit.", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
		End If
	End Sub

	Private Sub subcatBindingNavigatorDeleteItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorDeleteItem.Click
		Try
			If dgsubcategory.RowCount > 0 Then
				If subcatBindingNavigatorDeleteItem.Text = "Delete" Then
					If MessageBox.Show("Are you sure to delete this record?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
						gSQLstr = "SELECT COUNT(iim_subcat) FROM inv_itemmaster WHERE  iim_subcat = " & cSQL(cmbsubcatcode.Text) & ""
						If gSQLcon.State = ConnectionState.Closed Then
							gSQLcon.Open()
						End If
						DQcheck.Connection = gSQLcon
						DQcheck.CommandText = gSQLstr
						DHcheck.SelectCommand = DQcheck
						DHcheck.Fill(DScheck, ("subcat_list"))
						Dvcheck.Table = DScheck.Tables("subcat_list")
						If Dvcheck.Table.Rows(0).Item(0) > 0 Then
							MessageBox.Show("Record cannot be deleted!", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
						Else
							InvitemsubcategoryBindingSource.RemoveCurrent()
							Inv_itemsubcategoryTableAdapter.Update(dsInventory)
							MessageBox.Show("Record has been deleted.", "Delete", MessageBoxButtons.OK, MessageBoxIcon.Information)
						End If
						DScheck.Clear()
						gSQLcon.Close()
					End If
				Else
					If AddNew = True Then
						AddNew = False
					End If
					subcat(True)
					txtsubcatdesc.Enabled = False
					refreshsubcat()
					cmbsubcatcode.SelectedValue = subcatcode

				End If
			Else
				MessageBox.Show("No record to delete.", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
			End If

		Catch ex As Exception
			MessageBox.Show(ex.Message, Me.Text + " - Item Category Level 2(Delete)", MessageBoxButtons.OK, MessageBoxIcon.Information)
		End Try
	End Sub

	Private Sub subcatBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorSaveItem.Click
		Dim strmsg As String = ""
		Try
			If MessageBox.Show("Save Item?", "Save", MessageBoxButtons.YesNo, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then
				If AddNew = True Then
					If txtsubcat.Text.Trim = "" Then
						strmsg = strmsg & "Required field - Category Code." & vbCrLf
					Else
						gSQLstr = "SELECT COUNT(iis_code) FROM inv_itemsubcategory WHERE  iis_code = " & cSQL(txtsubcat.Text) & ""
						If gSQLcon.State = ConnectionState.Closed Then
							gSQLcon.Open()
						End If
						DQcheck.Connection = gSQLcon
						DQcheck.CommandText = gSQLstr
						DHcheck.SelectCommand = DQcheck
						DHcheck.Fill(DScheck, ("subcat_list"))
						Dvcheck.Table = DScheck.Tables("subcat_list")


						If Dvcheck.Table.Rows(0).Item(0) > 0 Then
							strmsg = strmsg & "Required field - Category Code already exits, please assign another Category Code!" & vbCrLf
						End If
						DScheck.Clear()
						gSQLcon.Close()
					End If
				End If
				If cmbitemcat.Text.Trim = "" Then
					strmsg = strmsg & "Required field - Level 1 Category." & vbCrLf
				End If
				If txtsubcatdesc.Text.Trim = "" Then
					strmsg = strmsg & "Required field - Description." & vbCrLf
				End If
				If strmsg = "" Then
					If AddNew = True Then
						dsInventory.inv_itemsubcategory.Addinv_itemsubcategoryRow(Trim(txtsubcat.Text), txtsubcatdesc.Text, cmbitemcat.Text)
						Inv_itemsubcategoryTableAdapter.Update(dsInventory.inv_itemsubcategory)
						Inv_itemsubcategoryTableAdapter.Fill(dsInventory.inv_itemsubcategory)
						AddNew = False
					Else
						InvitemsubcategoryBindingSource.EndEdit()
						Inv_itemsubcategoryTableAdapter.Update(dsInventory.inv_itemsubcategory)
					End If
					MessageBox.Show("Record saved", "Save", MessageBoxButtons.OK, MessageBoxIcon.None)
					subcat(True)
					cmbsubcatcode.Enabled = True
					cmbsubcatcode.SelectedValue = subcatcode
				Else
					MessageBox.Show(strmsg, Me.Text + " -Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
				End If
			End If
		Catch ex As Exception
			If ex.Message.Contains("Column 'iis_code, iis_itemcode' is constrained to be unique. ") = True Then
				MessageBox.Show("The code already exits, please assign another code!", Me.Text + " - Item Category Level 2", MessageBoxButtons.OK, MessageBoxIcon.Information)
				Exit Sub
			Else
				MessageBox.Show(ex.Message, Me.Text + " - Item Category Level 2(Save)", MessageBoxButtons.OK, MessageBoxIcon.Information)
			End If
		End Try
	End Sub

	Private Sub subcatBindingNavigatorRefre****em_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles subcatBindingNavigatorRefre****em.Click
		refreshsubcat()
	End Sub

	Private Sub refreshsubcat()
		Try
			Inv_itemsubcategoryTableAdapter.Fill(dsInventory.inv_itemsubcategory)
		Catch ex As Exception
			MessageBox.Show(ex.Message, Me.Text + " - Item Category Level 2(Refresh)", MessageBoxButtons.OK, MessageBoxIcon.Information)
		End Try
	End Sub

	Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
		Me.Close()
	End Sub



Please help me solve this problem.

Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Displaying data in second combo box based from data from first combo b

#2 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Displaying data in second combo box based from data from first combo b

Posted 18 June 2008 - 09:36 AM

You are going to need to run a separate query to fill the second combobox using the value returned from the first combobox in the where portion of the SQL statement.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1