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:
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
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
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
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.