Solved

ASP.NET: Table Adapters and SELECT DISTINCT

Posted on 2008-06-20
13
1,379 Views
Last Modified: 2013-11-26
'lo guys,

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?

Thanks,

Chris Dent
0
Comment
Question by:Chris Dent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 3

Expert Comment

by:JayeshKitukale
ID: 21830218
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
0
 
LVL 3

Expert Comment

by:JayeshKitukale
ID: 21830226
If you have SQL management studio or from code try firing the select distinct query you want to create the adapter for.
0
 
LVL 71

Author Comment

by:Chris Dent
ID: 21830420

Hey :)

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.

In my case I have a main Table Adapter with:

SELECT * FROM Software_Installed

Where Software_Installed contains:

Linked_ID - SmallInt (Foreign Key)
Linked_Asset_Tag - varchar(50) (Foreign Key)
Software_Name - varchar(50)
Software_Publisher - varchar(50)
Software_Version - varchar(50)
Install_Date - datetime

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?

Chris
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 3

Expert Comment

by:JayeshKitukale
ID: 21830489
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.
0
 
LVL 71

Author Comment

by:Chris Dent
ID: 21830556

I hadn't, but I have now. It gives the same error.

Exception Details: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Isn't it fun? ;)

Chris
0
 
LVL 3

Expert Comment

by:JayeshKitukale
ID: 21830573
Try adding one field to the select statement at a time until it works: Start from adding the primary fields. I assume you are creating a new TableAdapter.
0
 
LVL 3

Expert Comment

by:JayeshKitukale
ID: 21830580
Also check if adding another SELECT * from table work in the new table adapter?
0
 
LVL 71

Author Comment

by:Chris Dent
ID: 21830618

It works if I add the Primary fields (or the fields with constraints), but I get a lot of repetition (2270 rows in the table) because the row is no longer distinct.

Unless there's an alternative query to get the field I want?

Inability to come up with that just leads to back to my lack of knowledge of asp.net / visual basic / sql; maybe I should stick with Active Directory and networking, much safer :)

Chris
0
 
LVL 71

Author Comment

by:Chris Dent
ID: 21830640

I have a couple of other SELECT queries (in addition to the default) under each table adapter and those all work well.

e.g.

SELECT * FROM Software_Installed WHERE Software_Name=@Something

It's the just the DISTINCT, or any where the columns returned are limited and exclude Primary Keys / Indexes that seem to cause the problem.

Chris
0
 
LVL 3

Accepted Solution

by:
JayeshKitukale earned 500 total points
ID: 21830661
:) 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.
0
 
LVL 3

Expert Comment

by:JayeshKitukale
ID: 21830688
For more info on such - see the Law of Leaky Abstractions by Joel (http://joelonsoftware.com)
0
 
LVL 71

Author Comment

by:Chris Dent
ID: 21830696

Fair enough :) I suspected it might not be an easy solution. It was worth a try in case I was just missing something really obvious.

I'll just stick with code in a public class to deal with these then. There are only 10 of this type at the moment.

Thanks for your help though, it's certainly appreciated :)

Chris
0
 
LVL 71

Author Closing Comment

by:Chris Dent
ID: 31469091
Thanks :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Easy filter aspnet 2 46
Visual Studio npm 1 39
SQL Server Serialization error 8 45
function that i need to run every 1 min in win form app 6 39
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question