Grouping data already in the dataset

[Lemon]

Hey, I'm stuck with a problem I have to get sorted out!!

I'm need to group data thats already been loaded into a dataset. The data is loaded populated to a dropdownlist. Sql server database in case you're wondering... The user must select the desired printer model, and the next query fires, retieving all the different cartridges available to that model fro the database!

The thing is the database holds data on printer cartridges. Now some printer models have more than one type of cartridge, making the same printer model appear more than once in the dropdownlist. The query pulls the data from the data base an populates it. Now I have the problem that not all the fields in the database table are in the dataset, so I cant just do a normal 'GROUP BY' clause on the field in the database, since the data in the dataset is already queried by the printer type, eg.Inkjet, or Laserjet, etc. So now, say, I want to check the Lexmark manufacturer, the Inkjet type. So out of the table comes all Inkjet models for Lexmark. Now the table holds data on all printer types, not just inkjet... This makes a normal GRUOP BY clause unusable, since the table and dataset records are different....

eg. I would get something like this in my dropdownlist:

Lexmark 1200
Lexmark 1200
Lexmark 1200
Lexmark 1200
Lexmark 1400
Lexmark 1400
Lexmark 3200
Lexmark 3200

Instead I'd like this result, having the data grouped for user friendlyness, etc:

Lexmark 1200
Lexmark 1400
Lexmark 3200

I just cant get my head around how I can achieve this. If anyone could be of any kind of help, or give some suggestions, I'd appreciate it! THANX!

 

[jmcilhinney]

I think you need to perform two queries and return two result sets. The first would get each unique printer name and ID, which would be bound to your ComboBox. The second would contain all the cartridge data. You could then filter the cartridge data using a DataView based on the printer chosen by the user.

 

[jmcilhinney]

Given that you are using SQL server, you can include multiple SQL statements in the CommandText of a single SqlCommand object, seperated by semicolons. Alternatively you can use seperate SqlCommands and fill individual DataTables instead of a DataSet.

 

[DavidT_macktool]

one table?

Is the data loaded into one table with the primary key clustered to include: Manufacturer, Model, and Cartridge? Breaking it down into a one to many to many relationship seems like the best move. Select distinct values for manufacturers, after a manufacturer is selected query models, after model is selected query cartridges. If you have ever downloaded device drivers from a website, this is how a user is prompted to find a specific driver; same in your case for cartridges.

 

[Lemon]

I have it like that... One SQL query returns the printer models(the problem one), and the 'SelectedIndexChanged' event in that ddl, triggers the query to populate the next ddl with the available cartridges.... The problem query works great, but I just cant get those duplicate printer models out... Its not that the query aint working, it returns exactly what it should, and it lists all the applicable cartridges as well...just this damn grouping on the printer models thats haunting me!

Yeah, I thought of someting like finding a way to write a query that queries a datatable in the dataset, and then Grouping by one of the fields in that datatable...Dont have a clue as to how I'll accomplish that one though...

 

[Lemon]

Is the data loaded into one table with the primary key clustered to include: Manufacturer, Model, and Cartridge? Breaking it down into a one to many to many relationship seems like the best move. Select distinct values for manufacturers, after a manufacturer is selected query models, after model is selected query cartridges. If you have ever downloaded device drivers from a website, this is how a user is prompted to find a specific driver; same in your case for cartridges.

Well ,my database is laid out so that each manufacturer's products are all held within one table, no relationships... The fields, are the PrinterModel(used to query on),CartridgeDescription,CartridgeCode(also for querying),Capacity,PageYield and Type (for querying). So fact of the matter is the database isn't too complex at all!

The Manufacturer and Type fields info has been hard-coded into the page using ddl's. So from what the user selects there determines what he'll receive from the first query (listing of printer models where the TYPE matches "inkjet" for example).

 

[Lemon]

Strange aint it, when you find out what your problem is and its the simplest thing, well for future refernce, if anyone else finds the same problem, all it takes is adding the 'DISTINCT' keyword to your SQL statement...e.g. SELECT DISTINCT * FROM....

 

[DavidT_macktool]

Should have highlighted that key word..

Select distinct values for manufacturers, after a manufacturer is selected query models, after model is selected query cartridges.

Glad you solved it!