SelectCommand.Connection Property has not been initialized

Question

I'm getting an error that says Fill:  SelectCommand.Connection Property has not been initialized.

I have a class for my Database connection that looks like this:

namespace ConsensusAdmin

{

class DBConn

{

public static SqlConnection CorporateConnection;

static void DBConnectionClass()

{

// Corporate Connection String

string CorporateConnectionString =

"Data Source=DEVServer;Initial Catalog=DatabaseName;Integrated Security=False;UID=user;PWD=password;";

CorporateConnection = new SqlConnection(CorporateConnectionString);

}

}

}

and another class that returns a DataSet that looks like this:

namespace ConsensusAdmin

{

class PeopleDataRetriever

{

public DataSet GetPeople(string LastName)

{

SqlCommand PeopleCmd = new SqlCommand();

PeopleCmd.CommandType = CommandType.Text;

PeopleCmd.Connection = DBConn.CorporateConnection;

PeopleCmd.Parameters.AddWithValue("@LastName", LastName);

PeopleCmd.CommandText = @"

Select

Emplid, Name

From

Person_TBL

Where

Emplid=@LastName

Order By

Name";

// Use a DataAdapter to connect the DataSet to the DataBase

SqlDataAdapter daPeople = new SqlDataAdapter(PeopleCmd);

// Use a DataSet to store the Data in

DataSet dsPeople = new DataSet();

try

{

// Try to Fill the DataSet

daPeople.Fill(dsPeople);

}

catch (Exception ex)

{

// Catch the Error if there is one

MessageBox.Show(ex.Message);

}

// return the DataSet to the calling method.

return dsPeople;

}

}

}

But when I try to fill the DataSet execution falls to the catch statement and I get the error. Can someone see what I could be doing wrong?

Thanks,
Rich

Answer 1

You're not opening the connection. 

DBConn.CorporateConnection.Open();

By the way, creating some kind of static class to hold a single open connection to SQL Server is not recommended.  SQL Server connections should be opened, used and then disposed all within using statements.  I posted a blog the other day on formatting variables, but in it, there's a good pattern you can use to open and use SQL Server Connections.  The post is here:

http://blog.davemorton.net/2008/12/on-return-values-and-readability.html

---

David Morton - http://blog.davemorton.net/

Answer 2

You set your PeopleCmd.Connection = DBConn.CorporateConnection but in the class DBConn the method DBConnectionClass() is never called to initalize your object. You are returning a null CorproateConnection object.

With your current code change DBConnectionClass to return a CorporateConnection object and try:
PeopleCmd.Connection = DBConn.DBConnectionClass();

Answer 3

 

please help me with this i am getting this error, thanks in advance

Fill: SelectCommand.Connection property has not been initialized.

public partial class ViewApplicantDetails : System.Web.UI.Page
    {
        SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicantConStr"].ToString());
        SqlCommand sqlcmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();

        protected void Page_Load(object sender, EventArgs e)
        {

            Button1_Click(this, new EventArgs());
        }

        protected void Button1_Click(object sender, EventArgs e)
        {

            SqlCommand cmd = new SqlCommand("sp_GetApplicantDetails", sqlcon);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ApplicantID", TextBox1.ToString());
            sqlcon.Open();
            da = new SqlDataAdapter(sqlcmd);

            da.Fill(dt);
            ReportDocument RptDoc = new ReportDocument();
            RptDoc.Load(Server.MapPath("~/CrystalReport1.rpt"));
            RptDoc.SetDataSource(dt);
            CrystalReportViewer1.ReportSource = RptDoc;
            CrystalReportViewer1.DataBind();
            cmd.ExecuteNonQuery();
            sqlcon.Close();


        }



    }
}