VB.Net, Database connections, SQL

[crono.Serge]

Hi guys,

I'm trying to finish up a VB.Net project, which uses SQL statments to, I guess, sort out and display data.

The assignment requires the use a Vehicle database (ACCESS). The fields are InventoryID (Primary), Manufacturer, ModelName, Year, VehicleID, and CostValue.

Thing is I have to get the Manufacturer to be DISTINCT. I've tried using
SELECT DISTINCT * FROM Vehicle but, it just blows up in my face when I run it, since I'm using a combo box to display the list of manufacturers, when I pull it down, and select a different manufacturer, it crashes. (Even if I dont use the distinct clause) I get the following error

An unhandled exception of type 'System.IndexOutOfRangeException' occurred in system.data.dll

Additional information: An OleDbParameter with ParameterName 'Vehicle' is not contained by this OleDbParameterCollection.


Here's the code for my combo box

Private Sub cboManufacturer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboManufacturer.SelectedIndexChanged
'Display records according to SQL statements
DsAutoCenter1.Clear()
dbVehicles.SelectCommand.Parameters("Vehicle").Value = cboManufacturer.Text
dbVehicles.Fill(DsAutoCenter1)
End Sub

Oh...before I get side tracked, how would I set the Manufacturer to be DISTINCT and also SELECT 'everything else' FROM Vehicle? Would SELECT DISTINCT (Manufacturer), 'everything else' FROM Vehicles work? Or does that still DISTINCT everything?

[Brandoe85]

Hi,

You are trying to display the field "Vehicle" but it doesn't exist in your table.

As for your second question, select distinct manufacturer will only return the unique rows, you can't get distinct 1 row and all of the rest.

Hope that helps :)

Good luck;

[crono.Serge]

I got it working. I used 2 adapters and 2 datasets.

I used 2 separate querys, each using the DISTINCT clause, but, it does'nt do anything.

Here's the first adapter SQL Query dbManufacturer

SELECT DISTINCT InventoryID, Manufacturer FROM Vehicle ORDER BY Manufacturer

And here's the second SQL Query dbAutoCenter

SELECT DISTINCT InventoryID, Manufacturer, ModelName, Year, VehicleID, CostValue FROM Vehicle WHERE (Manufacturer = ?)

That should get rid of any duplicate entries, but, it does'nt. Question: WHY?

[Brandoe85]

The way you are using distinct won't work as you expect. InventoryID probably has many distinct values, that have duplicate of manufacturer. Just grab the column you need distinct of, that will give you want you are looking for.

Good luck;

[crono.Serge]

Your right, each InventoryID is distinct, and if I get rid of it, the whole thing'll blow up in my face. Hmm...I'm in quite the dilema.

I'm not actually sure how to do that. Great, I'm stumped.......

[Brandoe85]

Your right, each InventoryID is distinct, and if I get rid of it, the whole thing'll blow up in my face. Hmm...I'm in quite the dilema.

I'm not actually sure how to do that. Great, I'm stumped.......

No worries, if you explain it in more detail we can get it going. :)

I thought you just needed select distinct manufacturer to fill your drop down?

[crono.Serge]

But I've tried removing the PK from either query, but I get an error
An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll

Additional information: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

I populate the combo box with one dataset and populate the rest of the form using the WHERE Manufacturer = ? which somehow (not to sure on the details), lets the rest of the from know to go to the selected record. So for now...I'm stumped

[crono.Serge]

Here's the actually assignment:
Write a program that creates a Windows application to display data from the VB Auto Center Vehicle table from the VbAuto.mdb database on your student CD (also available in External Links if you cannot locate your student CD). Create a drop-down ComboBox of manufacturers whose Items collection source is populated at run-time from the database. Be sure to include each manufacturer only once. The user selects a manufacturer from the ComboBox and the program displays the remaining fields in labels for the selected manufacturer.
Here's all my VB Code:

Private Sub frmAutoCenter_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dbManufacturer.Fill(DsManufacturer1)
End Sub

Private Sub cboManufacturer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboManufacturer.SelectedIndexChanged
DsAutoInfo1.Clear()
dbAutoCenter.SelectCommand.Parameters("Manufacturer").Value = cboManufacturer.Text
dbAutoCenter.Fill(DsAutoInfo1)
End Sub

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
Me.Close()
End Sub

My 2 data adapters dbManufacturers and dbAutoCenter have the following queries
dbManufacturers: (populates the combo box)

SELECT DISTINCT Manufacturer, InventoryID FROM Vehicle

dbAutoCenter: (populates the rest of the form)

SELECT DISTINCT Vehicle.* FROM Vehicle

dbManufacturer generated dsManufacturer1 which is used by the combo box, and the rest of the labels pull there info from dsAutoInfo1 which is generated from dbAutoCenter. I have to remove all duplicate entries of the manufacturers, and those SQL queries SHOULD work....should'nt they?

[Brandoe85]

Hmm...can you post the database?

[crono.Serge]

Um....how?

[crono.Serge]

4650
Sorry this took me so long, been a bit busy. Here's the database

[crono.Serge]

Hi again,

Even though my semester is over, I would really like to know how to do that, just for future refrences.

Thanks guys!