Click here to Skip to main content
15,904,156 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I have a dropdown list to populate with two columns of a table.
The code that I do populate the dropdown list is as following:
C#
public void Paraqit_ddListKomuna()
       {
           SqlConnection sqlConn = new SqlConnection(StringKoneksioni.Stringu);
           try
           {

               SqlCommand sqlCmd = new SqlCommand("procParaqitKomunaDDL", sqlConn);
               sqlConn.Open();
               sqlCmd.CommandType = CommandType.StoredProcedure;
               SqlDataReader reader = sqlCmd.ExecuteReader();
               if (reader.HasRows)
               {
                   ddListKomuna.DataSource = reader;
                   ddListKomuna.DataTextField = "Komuna";
                   ddListKomuna.DataValueField = "ID_Komuna";
                   ddListKomuna.DataBind();
                   ddListKomuna.Items.Insert(0, new ListItem("--Zgjedh--", "0"));
               }
           }
           catch
           {
           }
           finally
           {
               sqlConn.Close();
           }

       }

This is populated from following table
SQL
CREATE TABLE [dbo].[KOMUNA](
	[ID_Komuna] [int] IDENTITY(1,1) NOT NULL,
	[Komuna] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_KOMUNA] PRIMARY KEY CLUSTERED 
(
	[ID_Komuna] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Also this table is related with following table
SQL
CREATE TABLE [dbo].[PERSONI](
	[ID_Personi] [int] IDENTITY(1,1) NOT NULL,
	[Emri] [nvarchar](20) NOT NULL,
	[Mbiemri] [nvarchar](20) NOT NULL,
	[Gjinia] [nvarchar](10) NOT NULL,
	[VitiLindjes] [date] NOT NULL,
	[VendiLindjes] [nvarchar](30) NOT NULL,
	[ID_Roli] [int] NOT NULL,
	[ID_Identifikimi] [int] NOT NULL,
	[ID_Komuna] [int] NOT NULL,
 CONSTRAINT [PK_PERSONI] PRIMARY KEY CLUSTERED 
(
	[ID_Personi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PERSONI]  WITH CHECK ADD  CONSTRAINT [FK_PERSONI_KOMUNA] FOREIGN KEY([ID_Komuna])
REFERENCES [dbo].[KOMUNA] ([ID_Komuna])
GO

ALTER TABLE [dbo].[PERSONI] CHECK CONSTRAINT [FK_PERSONI_KOMUNA]
GO

ALTER TABLE [dbo].[PERSONI]  WITH CHECK ADD  CONSTRAINT [FK_PERSONI_ROLI] FOREIGN KEY([ID_Roli])
REFERENCES [dbo].[ROLI] ([ID_Roli])
GO

ALTER TABLE [dbo].[PERSONI] CHECK CONSTRAINT [FK_PERSONI_ROLI]
GO


The stored procedure that I'm using is as following
SQL
ALTER PROCEDURE [dbo].[procParaqitKomunaDDL]

AS
BEGIN
SELECT KOMUNA.ID_Komuna, KOMUNA.Komuna FROM KOMUNA
 where exists (select KOMUNA.ID_Komuna, KOMUNA.Komuna from KOMUNA, PERSONI where 
KOMUNA.ID_Komuna = PERSONI.ID_Komuna) ORDER BY KOMUNA.Komuna ASC

END

My goal is to populate the dropdownlist without records in the condition where KOMUNA.ID_Komuna = PERSONI.ID_Komuna

Thank you in advance for your reply.
Cheers.
Posted
Comments
Naveen.Sanagasetti 14-Oct-15 10:40am    
didn't get your point, could you please share some sample data and expected result.
dr_iton 14-Oct-15 11:26am    
The problem is in Sotred Procedure, I want to exclude to show the records in dropdown list where KOMUNA.ID_Komuna = PERSONI.ID_Komuna
ZurdoDev 14-Oct-15 11:00am    
OK, where are you stuck? Put a where clause on it and be done.
dr_iton 14-Oct-15 11:26am    
The problem is in Sotred Procedure, I want to exclude to show the records in dropdown list where KOMUNA.ID_Komuna = PERSONI.ID_Komuna
ZurdoDev 14-Oct-15 11:38am    
I still don't see the problem.

1 solution

SQL
SELECT KOM.ID_Komuna, KOM.Komuna
 FROM KOMUNA AS KOM
 WHERE NOT EXISTS (SELECT * FROM PERSONI AS PER
 WHERE KOM.ID_Komuna = PER.ID_Komuna)
 ORDER BY KOM.Komuna ASC;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900