ASP.NET: Table Adapters and SELECT DISTINCT

I've not been having a lot of luck finding a neat solution to this issue so I thought I'd give a question a try.

I have an ASP.NET web application accessing an MS SQL database. The Data Access Layer is all currently done using TableAdapters and for the most part they're working very nicely with the ObjectDataSource component (kind of skipped Business Logic in most cases).

I want to populate a few drop down boxes with SELECT DISTINCT queries. The problem is two-fold.

First I can't create those queries and execute them successfully within the schema for an existing Table Adapter (unique / not null constraints).

And secondly, I can't create another TableAdapter including only the columns without constraints, it crashes (An unexpected error has occurred) on the last step and won't create the FillBy and GetBy part.

Here's where I get a bit stuck. I don't really understand anything in the source for the table adapters well enough to manually add stuff. And I'm having trouble thinking of an easy alternative without resorting to littering my code with SQL queries.

Is placing an SQL query to populate a DataSet for this in the Code Behind file a poor plan (in terms of ongoing maintenance)? Or is there a better place to put such things? Or a better / simpler alternative?

1. Please provide the table fields and their types here.
2. Provide the fields you are trying to select in the tableadapter which fails
3. Answer to your last question is YES - it is a bad practice
4. An alternative is to hand-code the tableadapter, but this is the last resort after we investigate from answers to above questions

The query works perfectly when executed using query manager or as an SQL Data Source in ASP.

As far as I understand, when using the Table Adapter it generates a Schema on first creation. Then every query associated with the Table Adapter uses that same Schema (hence the warning when you create a query that doesn't quite).

It also executes successfully using Preview Data from the Table Adapter in Visual Studio.

Software_Name and Linked_Asset_Tag are a composite / combined Primary Key for the table. Because of that neither is allowed to be Null.

The query I want to use to populate the Drop Down Box is:

SELECT DISTINCT Software_Publisher FROM Software_Installed

Not exactly complicated :)

The problem comes, as far as I know, when it fills the Data Set it checks it for constraints, then fails because the returned data leaves most columns Null (I can see this in Preview Data on the Table Adapter).

I thought the normal approach was therefore to create a Table Adapter with a Schema that only contains the fields I'm interested in (those without constraints). However that just makes Visual Studio pop up the unexpected error.

At the moment the best way I can think of to get around this is to call a Public Function stored in a Class in the App_Code directory and have that fill and return a DataSet when called from the Code Behind for the page. Hopefully that is at least better than the SQL Data Source or having the query itself in the code behind?

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Did you try without DISCTINCT in SELECT DISTINCT Software_Publisher FROM Software_Installed:
SELECT Software_Publisher FROM Software_Installed?
Does this work?
Your app_code approach looks fine as far as a quick solution is concerned.

:) FYI these tools that generate code always have limitations - they work mostly only for the basic requirements. We started using TableAdapters in our last project and ended up using NHibernate which is much more serious non-microsoft non-rapid application development robust technology, but the learning curve is too steep to get started quickly.

Summary
Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images. This article describes how to insert Windows control objects int…

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…

In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …