Issue with checking DropDownList's Selected Value exist in Database or Not

Question

I have a web page and there I have fields to hold some data.

One of the fields holds the IDs and names of students enrolled in a particular course. I want to be able to enroll new students to the course using a drop-down list. If the item selected in the drop-down list already exists in the Courses Table, I should display an error message.

I have a code to do these but when I select an already existing value in the drop-down list it doesn't show an error message. It assumes it's a new value and throws an exception because the Database doesn't accept duplicate records.

How can I solve this problem?

I use c# language to design this web page in 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();
    }

When i select a name from DropDownList which is not exist in Database, i get proper result.

For example, Think about "Jeff Bezos" is already registered for given course. When i choose "Jeff Bezos" i should get error message but I get exception which says that is duplicate.

Answer 1

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();
    }

Answer 2

1. Parameterize the sql
2. Make the sql a string/text and use it
3. Get rid if bad associative join, create a INNER instead
4. use a using which implements idisposable
5. Assumptions since you did not post the source of the list
6. Broad assumption on the int vs string for id (int is most common so I go with that)
7. Suggest this might be refactored to methods for better testing

       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();
                }
            }
        }