检查下拉列表中列表的选定值是否存在于数据库的问题

Question

>我有一个网页，那里有字段来保存一些数据。

其中一个字段包含注册特定课程的学生的 ID 和姓名。我希望能够使用下拉列表为新学生注册课程。如果在下拉列表中选择的项目已存在于课程表中，我应该显示一条错误消息。

我有一个代码来做这些，但是当我在下拉列表中选择已经存在的值时，它不会显示错误消息。它假定它是一个新值并引发异常，因为数据库不接受重复记录。

如何解决这个问题？

我使用 c# 语言在 ASP.NET 中设计此网页。

protected void DropDownList1_SelectedIndexChanged(object sender, 
EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|Ceng.mdf;Integrated Security=True");

string qs = Request.QueryString["id"]; 
// Variable qs Keeps CourseID retrieved from query string
string sv = DropDownList1.SelectedItem.Value; 
// Variable sv keeps selected value from DropDownList

SqlCommand cmd = new SqlCommand("Select * from Enrolment as e, Students as s where e.StudentID = s.StudentID and " +
"CourseID = " + qs + " and StudentName = '" + sv +"'", con);
// There are Students, Courses, and Enrolment tables in database
// Students table columns are StudentID, StudentName, BirthDate
// Course table columns are CourseID, CourseCode, CourseName, Instructor
// Enrolment table columns are CourseID and StudentID

con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if(reader.HasRows)
{
Label1.Visible = true;
Label1.Text = "The selected student is already registered to the course!";
Label1.ForeColor = Color.Red;
}
else
{
Label1.Visible = true;
Label1.Text = "The selected student is succesfully registered!";
Label1.ForeColor = Color.Green;

SqlDataSource4.Insert();
GridView1.DataBind();
}
reader.Close();
con.Close();
}

当我从下拉列表中选择一个数据库中不存在的名称时，我得到了正确的结果。

例如，Think about "Jeff Bezos"已经注册了给定的课程。当我选择"杰夫贝佐斯"时，我应该会收到错误消息，但我收到异常，说这是重复的。

Answer 1

1. 参数化 sql
2. 使sql成为字符串/文本并使用它
3. 如果关联联接错误，请删除，改为创建INNER
4. 使用实现一次性的using
5. 假设，因为您没有发布列表的来源
6. 对 id 的 int 与字符串的广泛假设(int 是最常见的，所以我同意)
7. 建议可以将其重构为更好的测试方法

using System;
using System.Data.SqlClient;
// more here likely...
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string connectionString = @"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|Ceng.mdf;Integrated Security=True";
string getStudentIdSql = @"
SELECT s.StudentID 
FROM Enrolment AS e
INNER JOIN Students AS s 
ON e.StudentID = s.StudentID 
AND E.CourseID = @CourseID
AND StudentName = @StudentName
";
int courseId = int.Parse(Request.QueryString["id"]); // consider tryparse here, I make assumptions on the int also
// string studentName = DropDownList1.SelectedItem.Value; // assumption if the values are there
string studentName = DropDownList1.SelectedItem.Text; // assumption based on code/comments, key part where it is defined is missing from question
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(getStudentIdSql, conn))
{
cmd.Parameters.Add("@CourseID", SqlDbType.Int).Value = courseId;
cmd.Parameters.Add("@StudentName", SqlDbType.VarChar, 80).Value = studentName;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
/* if we wanted to have the rows
while (reader.Read())
{
Console.WriteLine($"{reader.GetInt32(0)}t{ reader.GetString(1)}");
}
*/
Label1.Visible = true;
Label1.Text = "The selected student is already registered to the course!";
Label1.ForeColor = Color.Red;
}
/* basic thing
else
{
Console.WriteLine("No rows found.");
}
*/
else
{
Label1.Visible = true;
Label1.Text = "The selected student is succesfully registered!";
Label1.ForeColor = Color.Green;
SqlDataSource4.Insert();
GridView1.DataBind();
}
reader.Close();
}
}
}

Answer 2

protected void DropDownList1_SelectedIndexChanged(object sender, 
EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=|DataDirectory|Ceng.mdf;Integrated Security=True");
int selectedCourseId = 0;
string qs = Request.QueryString["id"];        
int.TryParse(qs, out selectedCourseId);
string sv = DropDownList1.SelectedItem.Value;           
SqlCommand cmd = new SqlCommand("Select * from Enrolment as e, Students as s where e.StudentID = s.StudentID and " +
"e.CourseID = @CourseID and s.StudentName = @StudentName", con);
cmd.Parameters.Add("@CourseID", SqlDbType.Int).Value = selectedCourseId;
cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar).Value = sv;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if(reader.HasRows)
{
Label1.Visible = true;
Label1.Text = "The selected student is already registered to the course!";
Label1.ForeColor = Color.Red;
}
else
{
Label1.Visible = true;
Label1.Text = "The selected student is succesfully registered!";
Label1.ForeColor = Color.Green;

SqlDataSource4.Insert();
GridView1.DataBind();
}
reader.Close();
con.Close();
}