I recently change a code because I am trying to avoid sql injection attacks, I parameterized my query with this. But I'm getting this error:
There is already an open DataReader associated with this Command which must be closed first.
The error comes in this line:
cmd1.ExecuteNonQuery();
Then I tried to close the reader by doing this; putting dr.Close(), before and/or after the ExecuteNonQUery();
dr.Close();
cmd1.ExecuteNonQuery();
Instead, it gave me another error
Invalid attempt to call CheckDataIsReady when reader is closed.
Here is my Code
protected void BtnPassRec_Click(object sender, EventArgs e)
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE email = @email", con))
{
cmd.Parameters.AddWithValue("@email", MailTxt.Text.Trim());
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
String myRef = Guid.NewGuid().ToString();
int Id = Convert.ToInt32(dr[0]);
SqlCommand cmd1 = new SqlCommand("insert into ForgotPassRequests values ('" + myRef + "','" + Id + "', getdate())", con);
cmd1.ExecuteNonQuery();
string user = dr[1].ToString();
string ToEmailAddress = dr[1].ToString();
string email = dr[1].ToString();
String EmailBody = "<p Style='font-size: 15px;'>Hi</p>" + user + ",<br/><br/><p Style='font-size: 15px;'>Click the button to reset your password</p> <br/><a style='display: block; width: 188px; height: 28px; font-size: 15px; background: #32CD32;padding: 9px;font-family: 'Graphik', sans-serif; text-align:center; border-radius: 5px;color: white;font-weight: 600; text-decoration: none;' href = '"
+ Request.Url.AbsoluteUri.Replace("RecoverPassword", "PasswordReset.aspx?Uid=" + myRef) + "'>Reset Password</a>" + "<br />;
MailMessage PassRecMail = new MailMessage("mannyrchrd@gmail.com", MailTxt.Text.Trim())
{
Body = EmailBody,
IsBodyHtml = true,
Subject = "Password Reset"
};
SmtpClient SMTP = new SmtpClient("smtp.gmail.com", 587)
{
UseDefaultCredentials = false,
Credentials = new NetworkCredential()
{
UserName = "mannyrchrd@gmail.com",
Password = "xxxxxxxxxx"
},
EnableSsl = false
};
SMTP.Send(PassRecMail);
dvMessage.Visible = true;
Error.Visible = false;
LblPassRec.Text = "A password reset link has been sent to your email";
LblPassRec.ForeColor = Color.Green;
}
else
{
Error.Visible = true;
labelerror.Text = "Please provide a valid and existing email !";
labelerror.ForeColor = Color.Red;
dvMessage.Visible = false;
}
}
}
}
catch (SqlException ex)
{
string msg = "Error:";
msg += ex.Message;
throw new Exception(msg);
}
}