Dataset and Select Distinct Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dataset and Select Distinct Problem

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-13 : 11:28:37

I'm using .Net to get a dataset for a dropdownlist.
I need 2 values, one for ddl text, the other for ddl value.

So, I have this:

SELECT DISTINCT RequestNumber, RequestType
FROM tblWorkRequests WHERE RequestFor = 'Lab'
ORDER BY RequestNumber ASC

There is a problem with the above. It is causing the ddl not to postback correctly.

The table tblWorkRequests has many reqestnumber repeats and a type for it. I don't need them all, just the unique ones for lab.

Suggestions?

Thanks,

Zath

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-13 : 11:52:18

RequestNumber is not the primary key? Follow the link in my signature and give us the information it asks for, and you will get an answer pretty fast.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-13 : 12:27:59

No, REquestNumber is not the primary key.

Here's the table in question:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblEMC_LWR_Test_tblWorkRequests]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblEMC_LWR_Test] DROP CONSTRAINT FK_tblEMC_LWR_Test_tblWorkRequests
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblWorkRequests]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblWorkRequests]
GO

CREATE TABLE [dbo].[tblWorkRequests] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[RequestNumber] [int] NULL ,
[RequestFor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestDate] [datetime] NULL ,
[Status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PercentComplete] [float] NULL ,
[StatusColor] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [nvarchar] (3500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AClosureDate] [datetime] NULL ,
[RequesterID] [int] NULL ,
[Make] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Model] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelYear] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SampleSize] [int] NULL ,
[LastUpdate] [datetime] NULL ,
[ProgramNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsActivity] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WRDesc] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ParentWR] [int] NULL ,
[OnHoldReason] [nvarchar] (3500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [nvarchar] (3500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectCtr] [int] NULL ,
[RejectedBy] [int] NULL ,
[LastModifiedBy] [int] NULL ,
[CloseDate] [datetime] NULL
) ON [PRIMARY]
GO

I need two fields from the table,RequestNumber and RequestType

All unique according to RequestNumber.

If I run the select in the analyzer, the data comes back ok.

I think the problem is elsewhere in my code for some reason.

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-13 : 12:49:44

I found the problem. It was in the code and the value's of the dropdownlist were not unique.

Thanks,

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources