Conditional Combo Boxes

[jkrause]

Hi all!
I have two combo boxes: cboRepairCategory (plumbing, landscaping, etc) and cboVendors. I am using Stored Procedures in SQL to store the data from my database and then load them into the combo boxes.

The cboRepairCategory will be loaded first. Once the user has selected what type of repair it is I want it to restrict the amount of vendors that can be selected, so instead of saying:

CREATE PROCEDURE dbo.up_Fill_Vendor_Combo

AS

SELECT VendorID, CompanyName
	FROM Vendors
	ORDER BY CompanyName

I would select only the vendors that have the RepairType of say Landscaping, so the cboVendor would load now only Vendors with Landscaping instead of all vendors.

My question is this: Is this done in SQL in my Stored Procedure or is it done in VB in my functions for my combo boxes. I've never tried this before I am totally lost on it. I have fiddled with the code on both ends but to no avail. Thank you in advance!

[Ranx]

I use a dataview for conditional combos. Without knowing your table structure, I can only give you an overview. I hope it helps

Dim dv as Dataview = New Dataview(DT)  '  DT is the datatable returned by your sp with all the data in it
dv.Sort = "SortField"
dv.RowFilter = "FilterField = '" & ComboBox1.Text & "'"
ComboBox2.DataSource = dv

[kvprajapati]

>Is this done in SQL in my Stored Procedure

You can do so by passing some reference details from the value of previously selected combobox.

CREATE PROCEDURE dbo.up_Fill_Vendor_Combo
@repairCat varchar(40)
AS

SELECT VendorID, CompanyName
	FROM Vendors Where RepairCategory=@repairCat
	ORDER BY CompanyName

[jkrause]

Thanks adatapost! I also had to add a line of code in my VB as well.

'*Configures command object
With sqlComVendor
   .Connection = sqlconFC
   .CommandText = "up_Fill_Vendor_Combo"
   .CommandType = CommandType.StoredProcedure
   .Parameters.Add ("@RepairTypeID", SqlDbType.Int).Value = _
      cboRepairType.SelectedValue
End With

This basically set the value of the parameter set by the Vendor SP to that of the cboRepairType, so that when the first combo box (RepairType) was selected then only the Vendors related to it were displayed. I chose to call it from the cboRepairType_SelectedIndexChanged sub as well, so no buttons were needed. It worked awesome, thanks!

[jkrause]

>Is this done in SQL in my Stored Procedure

You can do so by passing some reference details from the value of previously selected combobox.

CREATE PROCEDURE dbo.up_Fill_Vendor_Combo
@repairCat varchar(40)
AS

SELECT VendorID, CompanyName
	FROM Vendors Where RepairCategory=@repairCat
	ORDER BY CompanyName

Thanks adatapost! I also had to add a line of code in my VB as well.

'*Configures command object
With sqlComVendor
   .Connection = sqlconFC
   .CommandText = "up_Fill_Vendor_Combo"
   .CommandType = CommandType.StoredProcedure
   .Parameters.Add ("@RepairTypeID", SqlDbType.Int).Value = _
      cboRepairType.SelectedValue
End With

This basically set the value of the parameter set by the Vendor SP to that of the cboRepairType, so that when the first combo box (RepairType) was selected then only the Vendors related to it were displayed. I chose to call it from the cboRepairType_SelectedIndexChanged sub as well, so no buttons were needed.