Datagrid dependent on dropdownlist selection

[minn]

Hi. I have a drop down list which displays vendors. Based on what vendor the user selects, a datagrid should show records of the specified vendor.
e.g, user select Microsoft so info for all Microsoft software is returned in the datagrid.

I have tried doing this and the form compiles but does not work as it should. It just shows the record belonging to the first entry in the dropdownlist and when trying to select another vendor, the same record displays in the datagrid.

Here is my code:

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.oledb[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] WebForm1
[/SIZE][SIZE=2][COLOR=#0000ff]Inherits[/COLOR][/SIZE][SIZE=2] System.Web.UI.Page[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]'Define database connection
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] con [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= g:\Software.mdb")
[/SIZE][SIZE=2][COLOR=#008000]'create dataadapter
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dasoftware [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter[/SIZE]
 
[SIZE=2][SIZE=2]#[/SIZE][SIZE=2][COLOR=#0000ff]Region[/COLOR][/SIZE][SIZE=2] " Web Form Designer Generated Code "
[/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Page_Load([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]'Put user code to initialize the page here
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] Page.IsPostBack [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]VendorsLoad()
SoftwareLoad()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Fill the dropdownlist with data
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] VendorsLoad()[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]'Define a new dataadapter
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] davendors [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter
[/SIZE][SIZE=2][COLOR=#008000]'Define a new dataset
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dsvendors [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#008000]' sql select statement as a string to get vendors info
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strsql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = " SELECT Vendors.VendorID, Vendors.Vendor " & _
" FROM Vendors " & _
" ORDER BY Vendors.Vendor "
[/SIZE][SIZE=2][COLOR=#008000]'create the dataset
[/COLOR][/SIZE][SIZE=2]dsvendors = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#008000]'pass the data into the dataadapter
[/COLOR][/SIZE][SIZE=2]davendors = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(strsql, con)
[/SIZE][SIZE=2][COLOR=#008000]'fill the dataset
[/COLOR][/SIZE][SIZE=2]davendors.Fill(dsvendors)
[/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] ddlVendors
.DataTextField = "Vendor"
.DataValueField = "VendorID"
.DataSource = dsvendors
.DataBind()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]With
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] ddlVendors_SelectedIndexChanged([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] ddlVendors.SelectedIndexChanged[/SIZE]
[SIZE=2]
SoftwareLoad()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' load the specified records into the datagrid
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] SoftwareLoad()[/SIZE]
[SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]'define new dataset
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dsmain
dsmain = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#008000]'sql statement defining the data that should go in the datgrid
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strsql1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT [GKLS Software].ApplicationID, [GKLS Software].Application, " & _
" [GKLS Software].VendorID, [GKLS Software].Product_Key " & _
" FROM [GKLS Software], Vendors, software_Type " & _
" WHERE [GKLS Software].VendorID =[Vendors].[VendorID] " & _
" AND [GKLS Software].SoftwareTypeID=[Software_Type].[SoftwareTypeID]"
[/SIZE][SIZE=2][COLOR=#008000]' Another string which will concatenate strsql1 with another where/and condition
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strsql2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = strsql1 & _
" AND [GKLS Software].VendorID = " & _
ddlVendors.SelectedItem.Value [/SIZE][SIZE=2][COLOR=#008000]' to filter the returned records based on the dropdownlist selection
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' define the selectcommand settings
[/COLOR][/SIZE][SIZE=2]dasoftware.SelectCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand
dasoftware.SelectCommand.Connection = con
dasoftware.SelectCommand.CommandText = strsql2
dasoftware.Fill(dsmain) [/SIZE][SIZE=2][COLOR=#008000]' fill the dataset
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'pass the correct info to the datagrid
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] grdSoftware
.DataSource = dsmain
.DataBind()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]

Could someone please help and suggest where i may be going wrong or suggest a better method of doing this?

Much appreciated!

 

[DavidT_macktool]

This is a very typical Master/Detail situation.
A quick search for Master/Detail tutorials and/or using the dataform wizard will help you set up and understand the relationship you want to utilize.

 

[minn]

I prefer not to use the dataset wizard and code things myself as it helps me understand things better. Thanks any way.

I have a feeling it is something to do with the postback as the drop down list selection may not be being fed back to the server. Any ideas anyone?

 

[DavidT_macktool]

Your StrSQL1 is confusing me. You are really only selecting data fields from the Software dataset here.
Is "[GKLS Software].VendorID =[Vendors].[VendorID] AND [GKLS Software].SoftwareTypeID=[Software_Type].[SoftwareTypeID]" really necessary?

Correct me if I am wrong here, but it seems that you want to select software based on the selected VendorID: Isn't "[GKLS Software].VendorID =[Vendors].[VendorID]" and ["GKLS Software].VendorID = " ddlVendors.SelectedItem.Value] redundant.
Could be the source of your problem.

Start by displaying your complete SQL String in a messagebox to see if it is built correctly.

 

[minn]

Thanks for the suggestions,

but i have managed to figure out the problem. For the drop down list i had to set the autopostback property to true and also had to move the softwareload() method out of the ispostback statement in the page_Load

That seems to have done the trick​