Code not writing values in database asp.net

Question

So Im making a site and implementing registration functionality, I got login to work but I'm having troubles with registration.

Basically it doesn't write anything id db, I tried multiple things but nothing works, I don't get any errors or exceptions it just doesn't write anything in db, the activation mail part works fine, I get the mail but nothing is written in UsersInfo db and UsersActivations db.

public partial class RegistrationPage : System.Web.UI.Page
{
    protected void ActivationMail(int userId)
    {
        string constr = ConfigurationManager.ConnectionStrings["UsersConnectionString"].ConnectionString;
        string activationCode = Guid.NewGuid().ToString();
        using (SqlConnection sqlConn = new SqlConnection(constr))
        {
            using (SqlCommand sqlCom = new SqlCommand("INSERT INTO UserActivations VALUES(@UserId, @ActivationCode)"))
            {
                using (SqlDataAdapter sqlAdpt = new SqlDataAdapter())
                {
                    sqlCom.Parameters.AddWithValue("@UserId", userId);
                    sqlCom.Parameters.AddWithValue("@ActivationCode", activationCode);
                    sqlCom.Connection = sqlConn;
                    sqlConn.Open();
                    sqlConn.Close();
                }
            }
        }

        using (MailMessage mm = new MailMessage("sender@gmail.com", txt_mail.Text))
        {
            mm.Subject = "Activation Mail";
            string body = "Hello " + txt_username.Text.Trim() + ",";
            body += "<br /><br />Please click the following link to activate your account";
            body += "<br /><a href = '" + Request.Url.AbsoluteUri.Replace("CS.aspx", "Activation.aspx?ActivationCode=" + activationCode) + "'>Click here to activate your account.</a>";
            body += "<br /><br />Thanks";
            mm.Body = body;
            mm.IsBodyHtml = true;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.EnableSsl = true;
            NetworkCredential NetworkCred = new NetworkCredential("lukagamermaker@gmail.com", "1,2,3,4,5,6");
            smtp.UseDefaultCredentials = true;
            smtp.Credentials = NetworkCred;
            smtp.Port = 587;
            smtp.Send(mm);
            ClientScript.RegisterStartupScript(GetType(), "alert", "alert('Activatio mail sent , please check you e-mail.');", true);
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if(IsPostBack)
        {
            SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["UsersConnectionString"].ConnectionString);
            sqlCon.Open();

            string checkuser = "select count(*) from UsersInfo where UserName='" + txt_username.Text + "'";
            SqlCommand sqlCom = new SqlCommand(checkuser,sqlCon);

            int temp = Convert.ToInt32(sqlCom.ExecuteScalar().ToString());
            if (temp == 1)
            {
                Response.Write("There is already user using that User Name");
            }

            sqlCon.Close();
        }
    }

    protected void btn_register_Click(object sender, EventArgs e)
    {
        int userid = 0;

        if ((txt_username.Text != "") && (txt_password.Text != "") && (txt_mail.Text != ""))
        {
            SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["UsersConnectionString"].ConnectionString);
            sqlCon.Open();
            string insertQuery = "insert into UsersInfo (UserName,Password,Mail) values (@Uname , @Pass , @Email)";
            SqlCommand sqlCom = new SqlCommand(insertQuery, sqlCon);

            sqlCom.Parameters.AddWithValue("@Uname", txt_username.Text);
            sqlCom.Parameters.AddWithValue("@Pass", txt_password.Text);
            sqlCom.Parameters.AddWithValue("@Email", txt_mail.Text);

            ActivationMail(userid);

            Response.Redirect("Login.aspx");

            sqlCon.Close();
        }
        else
        {
            Response.Write("Error in making account");
        }
    }
}

A command needs to be executed. If you only declare it, it does nothing. Use _sqlCom.ExecuteNonQuery():_ where needed — Steve, Jan 19 '18 at 17:58
And now imagine someone writes in the txt_username textbox this string _'; DELETE FROM UsersInfo; --_ Why do you use parameters everywhere except when you check if the username exists? — Steve, Jan 19 '18 at 18:30
Last but not least. Storing password in clear text is considered one of the biggest existing security risks. You should try to [Hash and Salt passwords](https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database) — Steve, Jan 19 '18 at 18:32

Ehsan Ullah Nazir · Answer 1 · 2018-01-19T19:10:27.217

You are not executing insert query. Execute query with ExecuteNonQuery()

SqlCommand.ExecuteNonQuery Method : Executes a Transact-SQL statement against the connection and returns the number of rows affected.

Update your code to execute your query as well

using (SqlCommand sqlCom = new SqlCommand("INSERT INTO UserActivations VALUES(@UserId, @ActivationCode)"))
{ 
   sqlCom.Parameters.Add("@UserId",SqlDbType.VarChar,30).Value=userId; 
   sqlCom.Parameters.Add("@ActivationCode",SqlDbType.VarChar,30).Value=activationCode;
   sqlCom.Connection = sqlConn;
   sqlConn.Open();

   //here it is
   sqlCom.ExecuteNonQuery();
   sqlConn.Close();               
}

It's a bad idea to use Parameters.AddWithValue. In this case, ADO.NET must guess the data type, and there's a special hazard when using strings and AddWithValue. For more details over this ,

Read Under the Table - How Data Access Code Affects Database Performance And Can we stop using AddWithValue() already? (picked from comments by @mason)

When I do that it says String or binary data would be truncated . — Luka Tubić, Jan 19 '18 at 18:18
You should not be telling people to use AddWithValue. You're teaching somebody the wrong way. See [Can We Stop Using AddWithValue Already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — mason, Jan 19 '18 at 18:26

Code not writing values in database asp.net

1 Answers1