下拉列表的有条件人口 [英] Conditional population of a dropdownlist

问题描述

我有一个下拉列表，用于填充表格的两列。

我填写下拉列表的代码如下：

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

我的目标是在KOMUNA.ID_Komuna = PERSONI.ID_Komuna的情况下填充没有记录的下拉列表



提前感谢您的回复。

干杯。

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.

推荐答案

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;

这篇关于下拉列表的有条件人口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！