ASP.NET Conversion failed when converting the nvarchar value

Question

I'm trying to display the username in login session in the master page, I got this error after i log in it says

Conversion failed when converting the nvarchar value to data type int.

Here's my MasterPage.cs

public partial class HRPortal_HRPortalMaster : System.Web.UI.MasterPage
{
  protected void Page_Load(object sender, EventArgs e)
  {

    if (Session["Username"] != null)
    {
        if (!IsPostBack)
        {
            GetName();
        }
    }

  }
  void GetName()
  {
    using (SqlConnection con = new SqlConnection(Helper.GetCon()))
    {

        string query = @"SELECT Username FROM Users WHERE UserID=@UserID";
        con.Open();
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@UserID", Session["Username"].ToString());
            using (SqlDataReader dr = cmd.ExecuteReader())
            {


                    while (dr.Read())
                    {
                        ltName.Text = dr["Username"].ToString();

                    }

            }
        }
    }
  }
}

And here is my Login.cs

protected void btnLogin_Click(object sender, EventArgs e)
{

    using (SqlConnection con = new SqlConnection(Helper.GetCon()))
    {
        con.Open();
        string query = @"SELECT u.UserID, u.Username, u.Password, t.UserType FROM Users u INNER JOIN UserType t ON t.TypeID = u.TypeID WHERE Username=@Username AND Username=@Username";

        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.AddWithValue("@Username", txtUsername.Text);
            cmd.Parameters.AddWithValue("@Password", Helper.CreateSHAHash(txtPassword.Text));
            //DataTable dt = new DataTable();
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                if (dr.HasRows)
                {

                    while (dr.Read())
                    {

                        string Utype;
                        Utype = dr["UserType"].ToString();
                        if (Utype == "Employee")
                        {
                            Session["Username"] = txtUsername.Text;
                            Response.Redirect("~/HrPortal/Home.aspx");
                        }
                        else
                        {
                            Session["Username"] = txtUsername.Text;
                            Response.Redirect("~/Administrator/Home.aspx");
                        }
                    }

                }
                else
                {
                    error.Visible = true;
                }
            }
        }
    }

Answer 1

You wrote

SELECT Username FROM Users WHERE UserID=@UserID

yet for the @UserID parameter you pass in the username.

Are you sure you didn't mean to write

SELECT Username FROM Users WHERE Username = @UserID

That would make more sense. The UserID and the username are unlikely to match, I would expect. It would also help to explain the error, since I assume that UserID in the database is an integer column, whereas the username is a string (i.e. nvarchar in SQL Server parlance) - and logically you can't compare a number to a string.

Answer 2

profiously UserID datatype is int and not nvarchar on your Database. Try:

 cmd.Parameters.Add("@UserID",SqlDbType.Int).Value = int.Parse(Session["Username"].ToString());

Answer 3

You are passing username as userid here:

 cmd.Parameters.AddWithValue("@UserID", Session["Username"].ToString());

pass Userid instead and there should be no problem.

Answer 4

You are using your Username as your UserID. Here:

cmd.Parameters.AddWithValue("@UserID", Session["Username"].ToString());

It should be:

cmd.Parameters.AddWithValue("@UserID", Session["UsedID"].ToString());

if you have stored the UserId in you session. or SELECT Username FROM Users WHERE Username=@UserID ?

You also have a mistake in your other select, it should be

SELECT u.UserID, u.Username, u.Password, t.UserType FROM Users u INNER JOIN UserType t ON t.TypeID = u.TypeID WHERE Username=@Username AND **Password=@Password**

Answer 5

I think you must set session["UserID"] in login and use that in this query

string query = @"SELECT Username FROM Users WHERE UserID=@UserID";

base of your select in login, UserID is diffrent from Username