Parameter.addwithvalue - ExecuteReader: CommandText property has not been initialized

Question

I get the error ExecuteReader: CommandText property has not been initialized at the adapter.fill(ds) line. The weird thing is that if I replace @user with an actual string (e.g. 'name') it works perfectly fine, so something seems to be broken in the part that sets the parameter.

I've tried to set the string both with and without ''s (i.e. @user/'@user'). I've also tried using both = and like. User.Identity.Name.ToString() has been tested to return the logged in user correctly by setting a textbox to it.

Sorry for the non-English database variables and if this question has been answered somewhere. I've almost given up after half a dozen hours of searching, though (maybe I just suck at it).

Relevant code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


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

    String conn = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
    SqlConnection sql = new SqlConnection(conn);
    sql.Open();

    SqlCommand command = new SqlCommand(conn);
    command.CommandText = "SELECT klubb.KlubbNavn FROM Klubber klubb inner join User_Klubber_Connection conn on Klubb.Klubb_Id = conn.Klubb_Id inner join aspnet_Users bruker on bruker.UserId = conn.UserId WHERE bruker.UserName = @user";
    command.Parameters.AddWithValue("@user", User.Identity.Name.ToString());
    command.Connection = sql;
    SetDropDownList(command);
    DropDownList1.SelectedIndex = 0;
    ChangeGridView(GetMembersOfClub(), sql);

    sql.Close();

}

protected void SetDropDownList(SqlCommand command)
{

    SqlDataAdapter adapter = new SqlDataAdapter(command);
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

    DataSet ds = new DataSet();
    adapter.Fill(ds);

    DropDownList1.DataSource = ds;
    DropDownList1.DataTextField = "KlubbNavn";

    DropDownList1.DataBind();

}
}

Solution 1

Edit Forget everything just do following on page_load

 Response.Write(String.Format("user name is {0}",  User.Identity.Name));

And see the output

its running fine

  protected void Page_Load(object sender, EventArgs e)
    {


        SqlConnection sql = new SqlConnection( ConfigurationManager.ConnectionStrings["yourConnectionName"].ConnectionString);
        sql.Open();

        SqlCommand command = new SqlCommand("Select * from userinfo where uloginid=@user", sql);
        command.Parameters.AddWithValue("@user", User.Identity.Name.ToString());
        SetDropDownList(command);
        DropDownList1.SelectedIndex = 0;

        sql.Close();

    }

    protected void SetDropDownList(SqlCommand command)
    {

        SqlDataAdapter adapter = new SqlDataAdapter(command);
        SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

        DataSet ds = new DataSet();
        adapter.Fill(ds);

        DropDownList1.DataSource = ds;
        DropDownList1.DataTextField = "uFirstName";

        DropDownList1.DataBind();

    }

Solution 2

Probably you're getting null in command.Parameters.AddWithValue("@user", User.Identity.Name.ToString());

Try:

command.Parameters.AddWithValue("@user", User.Identity.Name ?? String.Empty);

Edit: (after comments)

Have you tried to create a parameter manually?

Instead of using AddWithValue(...) try :

SqlParameter param  = new SqlParameter();
param.ParameterName = "@user";
param.Value         = User.Identity.Name.ToString();
param.DbType        =  SqlDbType.VarChar;
command.Parameters.Add(param);

Comments

[Igor Mironenko]

I don't understand this answer - what is the actual solution exactly?

[user1339253]

@PandaWood It's years since I did this so I can't be sure, but I assume it was something like the parameter not being set correctly, and running the line he suggests would let you see so. So the fix is to make sure the value you're setting ´@user´ to actually has a value. But I don't really remember this at all... :'D