VB.net and Databases

[vb.net_beginner]

Ok I realise my last submission was waffle so in short here is what I need to do:

Populate a List Box lstManufacturers with the distinct values from a specified Column in a database table. The Column is called 'Car Manufacturers' and there are 4 distinct values in this column: Jaguar, Audi, BMW, Mercedes.

I then need to populate another List Box but this is only done after selecting one of the above values from the lstManufacturers it then displays the makes of the Cars from another DB Column 'Car Models' in this 2nd List Box lstModels. This 2nd List Box only displays the Car Models for the selected Manufacturer.

Any help greatly appreciated....

[debasisdas]

1. Select the distinct value from the column Car Manufacturers and display in the first List Box.
2. Select the value from the first List Box.
3. Pass this value to another SQL dynamically at run time.
4. Fetch the values from the SQL and populate the second List Box.

[vb.net_beginner]

Thanks for this but I am struggling a bit here.

I have added the below to the form load event:

Dim strCarManufacturer = "SELECT DISTINCT [Car Manufacturer] FROM Cars"
Me.lstCarManufacturer.Items.Add(strCarManufacturer.ToString())

This produces an error as the lstBox has 'Use Data Bound Items' ticked in and the database set:

Items collection cannot be modified when the DataSource property is set.

If I go back to the form designer and remove the tick above the List Box populates with:

SELECT DISTINCT [Car Manufacturer] FROM Cars

I have also observed tat through the 'Use Data Bound Items' wizard I can write a sql script against the specific List Box the problem with this is in doing so (using the sql above)I get the error below:

The schema returned by the new query differs from the base query

Pulling my hair out...

[debasisdas]

In which line you are executing the SQL statement ?

[vb.net_beginner]

Ok here is where I am at now.

I have populated the 1st List Box but not with distinct entries. I have don this by using the tick box option I mentioned earlier.

As for the latter part of this post the Lines I am using are below but all this does is populates the 2nd List Box with the actual SQL as appose to the results:

Private Sub lstCarModel_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCarModel.SelectedIndexChanged

Dim strValue0 As String = "SELECT [Car Model] FROM Cars WHERE [Car Make] = 'Jaguar'"
Dim strValue1 As String = "SELECT [Car Model] FROM Cars WHERE [Car Make] = 'Audi'"
Dim strValue2 As String = "SELECT [Car Model] FROM Cars WHERE [Car Make] = 'BMW'"
Dim strValue3 As String = "SELECT [Car Model] FROM Cars WHERE [Car Make] = 'Mercedes'"
Dim strNoValue As String = ""

If Me.lstPotentialRating.SelectedIndex = 0 Then
Me.lstSelectedRating.Items.Clear()
Me.lstSelectedRating.Items.Add(strValue0)
ElseIf Me.lstPotentialRating.SelectedIndex = 1 Then
Me.lstSelectedRating.Items.Clear()
Me.lstSelectedRating.Items.Add(strValue1)
ElseIf Me.lstPotentialRating.SelectedIndex = 2 Then
Me.lstSelectedRating.Items.Clear()
Me.lstSelectedRating.Items.Add(strValue2)
ElseIf Me.lstPotentialRating.SelectedIndex = 3 Then
Me.lstSelectedRating.Items.Clear()
Me.lstSelectedRating.Items.Add(strValue3)
Else
Me.lstSelectedRating.Items.Clear()
Me.lstSelectedRating.Items.Add(strNoValue)
End If

End Sub

[vb.net_beginner]

I got a little further with this last night.

I now have the below:

Dim strSql As String = "SELECT DISTINCT [Car Models] FROM Cars"
Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0; " _
& "Data Source =I:\cars.accdb"
Dim odaCarModel As New OleDb.OleDbDataAdapter(strSql, strPath)
Dim datCarModel As New DataTable
odaCarModel.Fill(datCarModel)

Me.lstCarModel.Items.Add(odaCarModel.Fill(datCarModel))

odaCarModel.Dispose()

This seems to recognise that there are only 4 distinct entries but instead of populating the list box with the entries it populates it with the number 4...

Any ideas?

[vb.net_beginner]

Cracked the first bit of this:

Dim strSql As String = "SELECT DISTINCT [Car Models] As Car_models FROM Cars"
Dim strPath As String = "Provider=Microsoft.ACE.OLEDB.12.0; " _
& "Data Source =I:\Cars.accdb"
Dim odaCarModels As New OleDb.OleDbDataAdapter(strSql, strPath)
Dim datCarModels As New DataTable
odaCarModels.Fill(datCarModels)
odaCarModels.Dispose()

lstCarModels.DataSource = datCarModels
lstCarModels.DisplayMember = "Car_models"

Now onto populating the second list box based on what is selected in the first

[vb.net_beginner]

I have managed to resolve both points above myself now...

Thanks for the help