Databound Listbox? 1

[jgoodman00]

I am trying to fill a listbox with data from a SQL Server database.

I am using the Northwind database, & attempting to display orders in a datagrid, based on selecting a customer from a listbox.

I dragged the orders & customers tables from server explorer, to create a dataconnection (dcNorthwind), two dataadapters (daOrders & daCustomers), & generated the dataset (dsNorthwind1). I then created a dataview dvCustomers.

If I preview the data it is visible.

I then set the:

dvCustomers.Table = dsNorthwind1.Customers
DataSource = dvCustomers
DataTextField = CompanyName
DataValueField = CustomerID

In the page_load event I fill daCustomers. The page loads fine, but nothing is displayed in the listbox.

If I:

Response.Write dvCustomers.Table.Rows.Count.ToString

I get 91 records, which is correct.


Any suggestions as to where it is going wrong?



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[culshaja]

You have to issue the databind command on the listbox after you have set the datasource etc.

James

James Culshaw
james@miniaturereview.co.uk

http://www.miniaturereview.co.uk

http://www.caninerescue.co.uk

 

[jgoodman00]

D'Oh!

Wood from trees moment! TY

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[jgoodman00]

Ok, I am starting to really like this & am thinking that creating linked listboxes shouldnt be anywhere near as difficult as it is in ASP 3.

(This is all using the Northwind DB)

I want to filter the list of customers in my customer listbox based on the type of ContactType in the Customers table.

As I said above, I have a DataAdapter daCustomers.

Can i create a subset from this which will be:

SELECT UNIQUE ContactType FROM Customers

using that DataAdapter?

If so how?

From what I see a DataView can only filter records. I need to modify the select statement (to include unique).
Is this understanding correct?

I think a DataTable might be able to do this, but what is a DataTable?
I cannot find a DataTable object to add to my webform.
Can DataTables only be implemented in code?



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[culshaja]

A DataTable is the object type that the tables in your dataset are. It is filled via a datadapter or by code adding a row at a time. If you want to filter data in a datatable you have to use a dataview. To have unique data in the datatable I would load it with the correct SQL statement.

James

James Culshaw
james@miniaturereview.co.uk

http://www.miniaturereview.co.uk

http://www.caninerescue.co.uk

 

[jgoodman00]

Ok thanks.

So based on my description a seperate DataAdapter would be required because it has a completely different SELECT statement?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[culshaja]

You can change the select command/statement at runtime on the one datadapter object.

James

James Culshaw
james@miniaturereview.co.uk

http://www.miniaturereview.co.uk

http://www.caninerescue.co.uk

 

[jgoodman00]

Ok, I am still no better off with this.

I have decided to use the Country column of the Customers table because I thought it would be easier to visualise.

I created a listbox, lstCountry & created the databinding at runtime:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here  

        'We need to check if the form is a postback
        If Not IsPostBack Then
            'Fill the dataadapters!  
            daCustomers.Fill(DsNorthwind1, "Customers")
            Call LoadCountries()
        End If

    End Sub

    Sub LoadCountries()
        'Need to load a unique list of countries based on the customers table
        Dim sConnectionString As String
        Dim sqlString As String
        Dim MyConnection As SqlClient.SqlConnection
        Dim MyDataAdapter As SqlClient.SqlDataAdapter
        Dim DS As New DataSet()

        sConnectionString = "Server=server1;Database=NorthWind; "
        sConnectionString += "UID=sa; pwd=;"

        sqlString = "SELECT DISTINCT Country FROM Customers ORDER BY Country"

        MyConnection = New SqlClient.SqlConnection(sConnectionString)
        MyDataAdapter = New SqlClient.SqlDataAdapter(sqlString, MyConnection)

        MyDataAdapter.Fill(DS, "Customers")

        With lstCountry
            .DataSource = DS.Tables("Customers").DefaultView
            .DataTextField = DS.Tables("Customers").Columns("Country").ToString
            .DataValueField = DS.Tables("Customers").Columns("Country").ToString
            .DataBind()
        End With
    End Sub

This part works well, & a list of unique countries is inserted into the listbox.

lstCountry.AutoPostBack = True

When I select a value(s) from the listbox the following code is executed:

    Private Sub lstCountry_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCountry.SelectedIndexChanged
        Dim strTemp As String
        strTemp = "Country In(" & GetSelectedTextItems(Me.lstCountry) & ")"
        dvCustomers.RowFilter = strTemp
    End Sub

    Function GetSelectedTextItems(ByVal lst As ListBox) As String
        'Need to generate a csv list of customerID's and copy them to txtCustomerList
        Dim strTemp As String = "'"

        Dim i As Integer
        For i = 0 To lst.Items.Count - 1
            If lst.Items(i).Selected = True Then
                strTemp += lst.Items(i).Value & "', '"
            End If
        Next

        'Remove the trailing chars
        strTemp = Left(strTemp, Len(strTemp) - 3)

        Return strTemp
    End Function

However, this does nothing, & lstCustomers remains full of the complete list of entries.

If I dont issue the lstCustomer.DataBind() command until after dvCustomer is modified nothing at all is shown in lstCustomers


Any ideas?


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[jgoodman00]

Sorted:

I needed to move the daCustomers.Fill command outside of the

If Not IsPostBack

statement...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures