none
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

    Friday, December 12, 2008 2:46 PM

Answers

  • 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/
    • Proposed as answer by Guo Surfer Tuesday, December 16, 2008 10:27 AM
    • Marked as answer by Guo Surfer Wednesday, December 17, 2008 10:56 AM
    Friday, December 12, 2008 2:55 PM
    Moderator
  • 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();

    • Proposed as answer by Guo Surfer Tuesday, December 16, 2008 10:28 AM
    • Marked as answer by Guo Surfer Wednesday, December 17, 2008 10:56 AM
    Friday, December 12, 2008 2:56 PM

All replies

  • 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/
    • Proposed as answer by Guo Surfer Tuesday, December 16, 2008 10:27 AM
    • Marked as answer by Guo Surfer Wednesday, December 17, 2008 10:56 AM
    Friday, December 12, 2008 2:55 PM
    Moderator
  • 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();

    • Proposed as answer by Guo Surfer Tuesday, December 16, 2008 10:28 AM
    • Marked as answer by Guo Surfer Wednesday, December 17, 2008 10:56 AM
    Friday, December 12, 2008 2:56 PM
  •  

    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();


            }



        }
    }

                                                                     
    Monday, May 28, 2012 5:43 AM