下拉列表中的值不会保存到SQL Server表中

Question

我有一个下拉列表，由一个SQL Server表称为tblVisa填充。我的问题是，正在从SQL Server表填充的值没有被保存。除了下拉列表，其他的都被保存了。我试过使用.SelectedValue和.Text，但它仍然不起作用。

我的代码

protected void PopulateVisaType()
{
List<ListItem> result = new List<ListItem> { new ListItem("", "") };
SqlCommand cmd = new SqlCommand() { Connection = sqlConn, CommandText = "SELECT VisaType FROM tblVisa ORDER BY VisaType ASC" };
if (sqlConn.State == ConnectionState.Closed) 
{
sqlConn.Open(); 
}
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
result.Add(new ListItem(read["VisaType"].ToString(), read["VisaType"].ToString()));
}
read.Close();
sqlConn.Close();
cmd.Dispose();
DDLVisa.DataSource = result;
DDLVisa.DataValueField = "value";
DDLVisa.DataTextField = "text";
DDLVisa.DataBind();
}

下面是我将信息保存到数据库中的代码:

protected void LbSaveProfile_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand() { Connection = sqlConn, CommandText = "spSaveNewProviderInformation", CommandType = CommandType.StoredProcedure };
if (sqlConn.State == ConnectionState.Closed)
{ 
sqlConn.Open(); 
}
cmd.Parameters.AddWithValue("@EmployeeNumber", TbEmployeeNumber.Text.Trim());
cmd.Parameters.AddWithValue("@SSN", TbSSN.Text.Trim());
cmd.Parameters.AddWithValue("@ContractType", DDLContractType.SelectedItem.Value);
cmd.Parameters.AddWithValue("@Firstname", TbFirstname.Text.Trim());
cmd.Parameters.AddWithValue("@Lastname", TbLastname.Text.Trim());
cmd.Parameters.AddWithValue("@MiddleInitial", TbMiddleInitial.Text.Trim());
cmd.Parameters.AddWithValue("@ContractRenewalDate", TbContractRenewalDate.Text.Trim());
cmd.Parameters.AddWithValue("@Position", DDLPosition.Text.Trim());
cmd.Parameters.AddWithValue("@Specialty", DDLSpecialty.Text.Trim());
cmd.Parameters.AddWithValue("@PrimaryDepartment", DDLPrimaryDepartment.Text.Trim());
cmd.Parameters.AddWithValue("@SecondaryDepartment", DDLSecondaryDepartment.Text.Trim());
cmd.Parameters.AddWithValue("@Gender", DDLGender.Text.Trim());
cmd.Parameters.AddWithValue("@Birthdate", TbBirthdate.Text.Trim());
cmd.Parameters.AddWithValue("@EmailAddress", TbEmailAddress.Text.Trim());
cmd.Parameters.AddWithValue("@PhoneNumber", TbPhoneNumber.Text.Trim());
cmd.Parameters.AddWithValue("@Address", TbAddress.Text.Trim());
cmd.Parameters.AddWithValue("@PassportNumber", TbPassportNumber.Text.Trim());
cmd.Parameters.AddWithValue("@Citizenship", DDLCitizenship.Text.Trim());
cmd.Parameters.AddWithValue("@Visa", DDLVisa.Text.Trim());
cmd.Parameters.AddWithValue("@Status", 1);
cmd.ExecuteNonQuery();
sqlConn.Close();
Alert("Provider Information saved!");
ClearControls();
}

Answer 1

您最好为下拉列表提供列名。

那么，这样写:

protected void PopulateVisaType()
{
SqlConnection sqlConn = new SqlConnection("");
using (SqlCommand cmd = new SqlCommand("SELECT VisaType FROM tblVisa ORDER BY VisaType ASC", sqlConn))
{
if (sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
DDLVisa.DataSource = cmd.ExecuteReader();
DDLVisa.DataValueField = "VisaType";
DDLVisa.DataTextField = "VisaType";
DDLVisa.DataBind();
//DDLVisa.Items.Insert(0, new ListItem(""));   // optional blank row choice
sqlConn.Close();
}
}

所以TextField和DataText字段需要是来自数据源的命名列。

我还包括了一个可选的第一个空白选项，如果你需要/想要/期望没有选择。

但是，请记住，如果您不允许(或不希望)空字符串，并且希望该值为空，则该空字符串应该在数据库中转换为null。这适用于你所有的价值观。(可能是存储过程做的?)。