CODEWITHSUNDEEP
c#sqlasp.net
HEEN
HEEN

Reputation: 4721

Conversion failed when converting the nvarchar value to data type int

I am getting the error of conversion failed in my codebehind. My code is below:

using (SqlCommand cmd = new SqlCommand((tempUsertype == "0" ? "Select * from tbl_students" : "Select * from tbl_students where Id in (Select Id from tbl_students where NgoId=@NgoId)"), conn))
{
    cmd.Parameters.AddWithValue("@NgoId", Convert.ToString(Session["User"]));
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    ddlStudent.DataValueField = ds.Tables[0].Columns["Id"].ToString();
    ddlStudent.DataTextField = ds.Tables[0].Columns["first_name"].ToString();
    ddlStudent.DataSource = ds.Tables[0];
    ddlStudent.SelectedIndex = 0;

    if (Session["UserType"] == "1" || Session["UserType"] == "2")
    {
        ddlStudent.Enabled = false;
    }
    else
    {
        ddlStudent.Items.Insert(0, new ListItem() { Text = "--Select NGO--", Value = "0" });
        ddlStudent.Enabled = true;
    }
}

I'm getting following error:

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

What changes has to be done here?

Upvotes: 0

Views: 9847

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460058

I guess that NgoId is an int but you're assigning a string. So this might fix it:

var p = new SqlParameter("@NgoId", SqlDbType.int).Value = int.Parse(Convert.ToString(Session["User"]));
cmd.Parameters.Add(p);

Edit: since you have commented that the session stores the username but the NgoId is an int-column you have three options:

  1. change the session to store the user-id int instead of the name
  2. change the column to store the username
  3. select the user-id from the table where the username is stored.

I would either prefer the first or the last option.

This works if you also prefer the last approach:

string sql = "Select * from tbl_students";
if(tempUsertype != "0")
{
    sql = @"Select s.* 
            from tbl_students s 
            where s.NgoId in (Select u.NgoId 
                              from tbl_User u 
                              where u.username = @Username)";
}
using (var cmd = new SqlCommand(sql, conn))
{
    var p = new SqlParameter("@Username", SqlDbType.varchar);
    p.Value = Convert.ToString(Session["User"]);
    cmd.Parameters.Add(p);
    // ...
}

Upvotes: 1

Polyfun
Polyfun

Reputation: 9639

Try this:

cmd.Parameters.Add("@NgoId", SqlDbType.Int).Value = Convert.ToString(Session["User"]);

This will coerce the NgoId parameter to the SQL Server int data type.

Upvotes: 0

Related Questions