ASP.net Delete index in SQL table C#

Question

I have a table called students, I want to delete an students info, but first I need him/her to re-enter his/her login details before he/she is deleted from the table (Sort of like deactivating your account)

protected void btnLDelete_Click(object sender, EventArgs e)
{
    {
        string strcon = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\VC_temps.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        SqlConnection con = new SqlConnection(strcon);

        SqlCommand com = new SqlCommand("CheckUser", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter p1 = new SqlParameter("StudCode", TextBox1.Text);
        SqlParameter p2 = new SqlParameter("Pword", TextBox2.Text);
        com.Parameters.Add(p1);
        com.Parameters.Add(p2);
        con.Open();
        SqlDataReader rd = com.ExecuteReader();
        if (rd.HasRows)
        {

            string command = @"DELETE FROM Student WHERE StudCode= StudCode";
            SqlCommand com2 = new SqlCommand(command, con);
            SqlParameter q1 = new SqlParameter("StudCode", Session["StudCode"]);
            com.Parameters.Add(q1);
            Response.Redirect("Default.aspx");
        }

        else
        {
            Labelinfo.Text = "Invalid username or password.";

        }
    }
}

I also tried using a SP but came with the same results, I don't get an error but as soon as I click delete I get redirected to my login page and seems that I can Still log in

can someone please help?

Answer 1

If anyone is interested in the future i fixed it thanks to some help

 protected void btnDelete_Click(object sender, EventArgs e)
{
    {
        string strcon = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\VC_temps.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        SqlConnection con = new SqlConnection(strcon);

        SqlCommand com = new SqlCommand("CheckUser", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlParameter p1 = new SqlParameter("StudCode", TextBox1.Text);
        SqlParameter p2 = new SqlParameter("Pword", TextBox2.Text);
        com.Parameters.Add(p1);
        com.Parameters.Add(p2);
        con.Open();
        SqlDataReader rd = com.ExecuteReader();
        if (rd.HasRows)
        {
            con.Close();
            con.Open();
            string command = @"DELETE FROM Student WHERE StudCode = @StudCode";
            SqlCommand com2 = new SqlCommand(command, con);
            SqlParameter q1 = new SqlParameter("@StudCode", Session["StudCode"]);
            com2.Parameters.Add(q1); // Also com2 now
            com2.ExecuteNonQuery(); // Added to run the query
            Response.Redirect("Default.aspx");
        }

        else
        {
            Labelinfo.Text = "Invalid username or password.";

        }
    }

}

Answer 2

Inside your if statement, you are not executing the DELETE, only setting up a new command, com2.

You then add the parameter to the old com command object.

You need to decide if you want to use the old command, or continue with the new com2, and add the parameter to the proper command. You must then execute the command.

I am also pretty sure your variable StudCode needs an @ in front. The if would look something like then when you are finished

rd.Close();

string command = @"DELETE FROM Student WHERE StudCode = @StudCode";
SqlCommand com2 = new SqlCommand(command, con);
SqlParameter q1 = new SqlParameter("@StudCode", Session["StudCode"]);
com2.Parameters.Add(q1); // Also com2 now
com2.ExecuteNonQuery(); // Added to run the query
Response.Redirect("Default.aspx");

---

Depending on what data CheckUser returns, it may be a better idea to call ExecuteScalar initially to look for a specific value instead of whether the CheckUser stored procedure gives you a row back.