Click here to Skip to main content
15,938,682 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am Working on My Semester Project. Here is the Scenario that the Admin Creates a User Account With Basic Info username email and cnic, then an activation link is send to user to his email and then user follow that email to activate his account and set password for his account and then login to enjoy facilities of his account.

But When The Code is Giving Some Errors, Basic info is inserted in the database successfully but the activation code and is not stored in the database. Here is the full code. If anyone can Help plz... The Store Procedure USP_Register_Candidate is Working Well but Store Procedure USP_CandidateActivation is not inserting cand id and activation code into the database.
Code For Admin to Add Candidate
C#
 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;
using System.Net.Mail;
using System.Data;

public partial class AdminAddCandidate : System.Web.UI.Page
{
    DBClass db1 = new DBClass();


    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void BtnRegisterCand_Click1(object sender, EventArgs e)
    {
        int userId = 0;
        db1.sqlcmd = new SqlCommand("USP_Register_Candidate");

        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            db1.sqlcmd.CommandType = CommandType.StoredProcedure;
            db1.sqlcmd.Parameters.AddWithValue("@UserName", TxtBxCandUsername.Text.Trim());
            db1.sqlcmd.Parameters.AddWithValue("@CNIC", TxtBxCandCNIC.Text.Trim());
            db1.sqlcmd.Parameters.AddWithValue("@Email", TxtBxCandEmail.Text.Trim());
            db1.sqlcmd.Parameters.AddWithValue("@RecoveryEmail", TxtBxCandRecoveryEmail.Text.Trim());
            db1.sqlcmd.Connection = db1.sqlcon;
            db1.sqlcon.Open();
            userId = Convert.ToInt32(db1.sqlcmd.ExecuteScalar());
            db1.sqlcon.Close();
        }

        string message = string.Empty;
        switch (userId)
        {
            case -1:
                message = "Username already exists.\\nPlease choose a different username.";
                break;
            case -2:
                message = "Supplied email address has already been used.";
                break;
            case -3:
                message = "Supplied CNIC has already been used.";
                break;
            default:
                message = "Registration successful.\\nUser Id: " + userId.ToString();
                SendActivationEmail(userId);
                break;
        }
        ClientScript.RegisterStartupScript(GetType(), "alert", "alert('" + message + "');", true);
    }

    private void SendActivationEmail(int userId)
    {
        string activationCode = Guid.NewGuid().ToString();
        db1.sqlcmd = new SqlCommand("USP_CandidateActivation");
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            db1.sqlcmd.CommandType = CommandType.StoredProcedure;
            db1.sqlcmd.Parameters.AddWithValue("@UserId", userId);
            db1.sqlcmd.Parameters.AddWithValue("@ActivationCode", activationCode);
            db1.sqlcmd.Connection = db1.sqlcon;
            db1.sqlcon.Open();
            db1.sqlcmd.ExecuteNonQuery();
            db1.sqlcon.Close();
        }
        using (MailMessage mm = new MailMessage("sender@gmail.com", TxtBxCandEmail.Text))
        {
            mm.Subject = "Account Activation";
            string body = "Hello " + TxtBxCandUsername.Text.Trim() + ",";
            body += "<br /><br />Please click the following link to activate your account";
            body += "<br /><a href = '" + Request.Url.AbsoluteUri.Replace("AdminAddCandidate.aspx", "CandidateAccountActivation.aspx?ActivationCode=" + activationCode) + "'>Click here to activate your account.</a>";
            body += "<br /><br />Thanks <br /> Electoral Information System";
            mm.Body = body;
            mm.IsBodyHtml = true;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.EnableSsl = true;
            NetworkCredential NetworkCred = new NetworkCredential("someEmail@gmail.com", "somePassword");
            smtp.UseDefaultCredentials = true;
            smtp.Credentials = NetworkCred;
            smtp.Port = 587;
            smtp.Send(mm); 
        }
    }
}

SQL
CREATE PROC USP_CandidateActivation
@UserId INT, @ActivationCode UNIQUEIDENTIFIER
AS
BEGIN
INSERT INTO CandidateActivation (CandID,ActivationCode) VALUES(@UserId, @ActivationCode)
END

SQL
CREATE PROC USP_Register_Candidate
@UserName VARCHAR(30),@CNIC VARCHAR(13), @Email VARCHAR(50),@RecoveryEmail VARCHAR(Max)
AS
BEGIN
      SET NOCOUNT ON; 
      IF EXISTS(SELECT CandUserName FROM Candidates WHERE CandUserName = @UserName)
      BEGIN
            SELECT -1 -- User Name exists.
      END
      ELSE IF EXISTS(SELECT CandEmail FROM Candidates WHERE CandEmail = @Email)
      BEGIN
	  SELECT -2 -- Email exists.
	  END
	  ELSE IF EXISTS (SELECT CandCNIC FROM Candidates WHERE CandCNIC = @Email)
	  BEGIN
	  SELECT -3 -- CNIC exists
	  END
	  ElSE
	  BEGIN
INSERT INTO Candidates (CandUserName,CandEmail,CandRecoveryEmail,CreatedDate,CandCNIC) VALUES(@UserName,@Email,@RecoveryEmail,GETDATE(),@CNIC) SELECT @@IDENTITY

            SELECT SCOPE_IDENTITY() -- CandID                 
     END
END



[Edit member="Tadit"]
Replaced Email and Password with example Texts.
[/Edit]
Posted
v2
Comments
Do you get any exception? Have you debugged the code? What are your observations?

1 solution

As I can see your USP_CandidateActivation procedure is expecting as datatype of UNIQUEIDENTIFIER, but you are passing a string value for ActivationCode. That could be an issue. You may try this:

db1.sqlcmd.Parameters.Add("@ActivationCode", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
 
Share this answer
 
v2
Comments
Muhammad Taqi Hassan Bukhari 11-Apr-14 15:24pm    
No Sir, Your Suggested Code doesn't work.
I have this code and this is perfectly working.

string activationCode = Guid.NewGuid().ToString();
string sqlquery = "INSERT INTO UserActivation VALUES(@UserId, @ActivationCode)";
db1.sqlcmd = new SqlCommand(sqlquery, db1.sqlcon);
using (SqlDataAdapter sda = new SqlDataAdapter())
{
db1.sqlcmd.CommandType = CommandType.Text;
db1.sqlcmd.Parameters.AddWithValue("@UserId", userId);
db1.sqlcmd.Parameters.AddWithValue("@ActivationCode", activationCode);
db1.sqlcmd.Connection = db1.sqlcon;
db1.sqlcon.Open();
db1.sqlcmd.ExecuteNonQuery();
db1.sqlcon.Close();
}
but when i do this using store procedure it doesn't work
Abinash_Sahoo 11-Apr-14 16:11pm    
Are you getting any exception? Because in my sample application both the approaches are working just fine! Though you don't need a DataAdapter here. Only this code should work

string activationCode = Guid.NewGuid().ToString();

SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

using (SqlCommand sqlcmd = new SqlCommand("USP_CandidateActivation"))

{
sqlcmd.CommandType = CommandType.StoredProcedure;

sqlcmd.Parameters.AddWithValue("@UserId", 3);

//sqlcmd.Parameters.AddWithValue("@ActivationCode", activationCode);

sqlcmd.Parameters.Add("@ActivationCode", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();

sqlcmd.Connection = sqlcon;
sqlcon.Open();

sqlcmd.ExecuteNonQuery();
sqlcon.Close();
}

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900