Populate listbox based on combo box selection ?? 2

[tman72]

I have an access database of two tables. tbldistrict is one column that lists sales districts. tblreps has 2 columns, salesrep and district for that rep. What I want to do is fill a listbox with sales rep names based on the selection in my combo box for sales district. It's been a while since I have done this, and I seem to have hit the wall and can't get through it. Any ideas?

 

[Mike555]

2 Questions...

1. Is this a Windows Form or Web Form application?

2. Does the tblreps column containing the district contain a foreign key which access the matching primary key of tbldistrict to find the correlating district?

--
Regards,
Mike

 

[tman72]

To answer your questions...

It is a windows form

In tblDist I have fldDistrict and in tblreps I have fldDistrict that corresponds to fldDistrict in tblDistrict.

 

[Mike555]

You could use something like this to populate the drop-down-list containing the Districts when the Windows Form opens. Please note that this is untested...

[COLOR=green]' Populate the sales District combobox (ddlDistrict)[/color]
Dim DistrictAdapter As New OleDbDataAdapter ("SELECT * FROM tblDistrict", [i]YourConnectionStringToTheAccessDatabaseGoesHere[/i])
Dim DistrictDataset As New DataSet
DistrictAdapter.Fill(FilterDistrictDataset, "tblDistrict")
Me.ddlDistrict.DataMember = "lkup_District"
Me.ddlDistrict.DataSource = DistrictDataset
Me.ddlDistrict.DataTextField = [i]"Column Name Of tblDistrict containing the district name."[/i]
Me.ddlDistrict.DataValueField = " fldDistrict"
Me.ddlDistrict.DataBind()

[COLOR=green]' Optional - If you wish to have a blank row appear first in the ddl [/color]
Me.ddlDistrict.Items.Insert(0, New ListItem("", ""))

[COLOR=green]'Optional - Cleanup Resources[/color]
DistrictAdapter.Dispose(): DistrictAdapter = Nothing: DistrictDataset.Dispose(): DistrictDataset = Nothing

Then to display the matching tblReps record in the listbox, do the following...
Drag and drop an OleDbDataAdapter onto your designer tray. Set the Adapter's Connection String to the connection string of the Access database. Create a Select Command for the OleDbDataAdapter which contains an input parameter for the District ID, and then select the salesreps with a matching ID. When done with the OleDbDataAdapter, right-click it on the design tray and select Generate Dataset to generate a New (not an Existing, but a NEW) dataset. (We'll call this dataset DsReps.) This dataset will be used to house the results OleDbDataAdapter's Select Statement. Then create a Public Function on the webform (or elsewhere if you wish) which will execute OleDbSelectCommand1's Select Statement. Set the listbox's datasource to the Public Function. Please note that is also untested code

Public Function DisplayReps(ByVal DistrictID As String) as DsReps

        [COLOR=green]' Ensure the Dataset is not populated more than once.[/color]
        If (Not IsNothing(DsReps1.Tables("tblReps"))) Then
            DsReps1.Tables("tblReps").Clear()
        End If

       With OleDbDataAdapter1
            If .SelectCommand.Connection.State = ConnectionState.Closed Then
                .SelectCommand.Connection.Open()
            End If
            .SelectCommand.Parameters(0).Value = FlitchNum
            .Fill(DsReps1)
            .SelectCommand.Connection.Close()
            .Dispose()
        End With

    Return DsReps1


    End Function

HTH

--
Regards,
Mike

 

[GerardMcL]

Ok Im very new to this and you'll have to forgive me if this is far too simple.

code:
Dim selDistrict

selDistrict = Combo1.Text
Set rs = dbConn.Execute ("SELECT salesrep FROM tblreps WHERE district = '" & selDistrict & "';")
While Not rs.EOF
List1.AddItem(rs("salesrep"))
rs.MoveNext
Wend


Now Im sure I have completely over simplified this or misread your query, but thats my 2 pennys worth. Also Im more used to VB6 so there may be a few syntax errors, but this always works very well for me.

 

[ca8msm]

GerardMcl,

That method (using a recordset until EOF) came from VB6 and has since been replaced with using a DataSet/DataTable/DataReader. You can see two of these with examples at faq855-5662 (although it is specifically for the ASP.NET forum it is still relevant).

Hope this helps.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[tman72]

I'm somewhat familiar with VB 6 but things don't seem to work quite the same in .NET. I've been working with this most of the day and hope to get it to work. I'm close, but not quite there yet. Thanks for the help!

 

[tman72]

After some investingating, I decided to use a recordset and the recordset filter method, where rstdistrict.Filter = "fldDistrict = '" & mycboslectedvalue & "'"

The field names are not exactly the same as what I am using, but the method works.