Problem With duplicate values - SQL Server Forums

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

SQL Server Development (2000)

Problem With duplicate values

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-08-09 : 08:39:33

Hi,
I have some duplicate values in my table.I am not getting the required output.Following is my table structure

CTNID NAME PRICE PERIOD
1 CTN1 1000 1year
2 CTN2 2000 2year
3 CTN1 1212 2year
4 CTN3 1131 3year

I have a dropdownlist Period. when i select 1year based on NAme corresponding price should be displayed.But in my table i have 2 NAmes with different years & prices. For eg i select 1 year for the NAME CTN1, But there are two CTN's,So i am a bit confused as to what query should i give
Hope anyone could find a solution..
Thanks...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 08:50:57

your query should be like this ..


Select Price From <Your Table> Where [Name] = 'CTN1' Period = '1 Year'

Chirag

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 08:54:07

roxcy,

U Can Format the table using code tags sorrounding the table data so that it looks like;


CTNID  NAME   PRICE PERIOD
1      CTN1  1000   1year
2      CTN2  2000   2year
3      CTN1  1212   2year
4      CTN3  1131   3year

quote:
Originally posted by roxcy

... I have a dropdownlist Period. when i select 1year based on NAme corresponding price should be displayed.
But in my table i have 2 NAmes with different years & prices.

For eg i select 1 year for the NAME CTN1, But there are two CTN's,So i am a bit confused ..

U have to tell us what needs to be selected.
One (if so which one) or both ?

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 09:58:19

Do you want to disply two names or one?
If one, define it

Madhivanan

Failing to plan is Planning to fail

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-08-10 : 03:51:13

As i Have mentioned earlier I hve dropdownlist which has some years say,1 year,2 year...Year has already been defined in the table.NAME has also been defined in the table
My requirement is when i click on any year,Correspondig name with price should be displayed.

The following query is working fine but can i do it using stored procedure
Select Price From <Your Table> Where [Name] = 'CTN1' Period = '1 Year'

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-10 : 04:14:18

Yeah you can do that in the SP.

Somthing like this


Create Proc ShowValues 
(
@pName varchar(100),
@pYear varchar(20)
)
As
Begin 
Select Price From <Your Table> 
Where [Name] = @pName Period = @pYear
End

Chirag

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-08-10 : 05:43:56

CREATE PROCEDURE amcsp_GetRATEDetails
(
@intCTNID as integer = 0,
@NAME varchar(100),
@PERIOD varchar(20)
)
AS

if(@intCTNID = NULL) OR (@intCTNID = 0)
BEGIN

select CTNID,convert(varchar,PRICE,102)PRICE,PERIOD FROM AMCMRATE
where NAME = @NAME AND PERIOD = @PERIOD
END
ELSE

BEGIN

select CTNID,convert(varchar,PRICE,102)PRICE,PERIOD FROM AMCMRATE
where NAME = @NAME AND PERIOD = @PERIOD AND CTNID = @intCTNID

END

This is the function where i calling the perocedure.But i am getting error saying @Name must be declared.

Public Function GetRateDetails(ByVal intCTNID As Integer, ByVal dsNAME As DataSet, ByVal dsPeriod As DataSet) As DataSet
Dim dsrate As DataSet
Dim arrParam(3, 4) As Object

arrParam = New Object(,) {{"@intCTNID,@NAME,@PERIOD", intCTNID, dsNAME, dsPeriod, SqlDbType.Int, _
ParameterDirection.Input, 10}}
dsrate = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "amcsp_GetRATEDetails", arrParam)

dsrate.Tables(0).TableName = GetDataTableName(EnumDataTableList.RATE)

Return dsrate

End Function

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-10 : 06:08:53

Your proc should be like this


 CREATE PROCEDURE amcsp_GetRATEDetails
(
	@intCTNID as integer = 0,
	@NAME varchar(100),
	@PERIOD varchar(20)
)
AS

if(@intCTNID  = Is NULL) OR (@intCTNID = 0)
BEGIN
	select CTNID,convert(varchar,PRICE,102)PRICE,PERIOD FROM AMCMRATE
	where NAME = @NAME AND PERIOD = @PERIOD
END
ELSE
BEGIN
	select CTNID,convert(varchar,PRICE,102)PRICE,PERIOD FROM AMCMRATE
	where NAME = @NAME AND PERIOD = @PERIOD AND CTNID = @intCTNID
END

Or 
-- Just check this also should work fine.. but test it before implementing. 
 CREATE PROCEDURE amcsp_GetRATEDetails
(
	@intCTNID as integer = 0,
	@NAME varchar(100),
	@PERIOD varchar(20)
)
AS
	select CTNID,convert(varchar,PRICE,102)PRICE,PERIOD FROM AMCMRATE
	where NAME = @NAME AND PERIOD = @PERIOD AND (CTNID = @intCTNID or @intCTNID is null or @intCTNID = 0 )
END

Try to run this procedure from the Query Analyser first.. if you dont get error then there
is some flaw with your front end code..

Chirag

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-08-10 : 07:42:03

Thanks .....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources