locked
ExecuteReader: Connection property has not been initialized RRS feed

  • Question

  • User-1877113880 posted

    Hello,

    Im trying to learn MVC in short bursts, so i thought ill learn how to retrieve information from the database using a Select Statement and fill a datatable and then build the view etc, but when i step through the code i get the above error, im not sure what im doing wrong here heres my code within the model

    public class DefaultManagement
        {
    
            private DataTable dataTable;
    
            // your method to pull data from database to datatable   
            public void PullData()
            {
                string query = "select Addressline1, Addressline2, City from Person.Address";
                SqlParameter[] spparams = new SqlParameter[0];
                SqlDataReader reader;
                reader = ExecuteReader(CommandType.Text, query, spparams);
                dataTable.Load(reader);
            }
    
            private static SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] commandParameters)
            {
                string connString = @"Data Source=SCOTTS-PC\SQLEXPRESS;database=AdventureWorks; Trusted_Connection=True;";
                SqlConnection conn = new SqlConnection(connString);
                //create a command and prepare it for execution
                SqlCommand cmd = new SqlCommand(commandText, conn);
                conn.Open();
                //create a reader
                SqlDataReader dr;
                dr = cmd.ExecuteReader();
                // detach the SqlParameters from the command object, so they can be used again.
                cmd.Parameters.Clear();
                conn.Close();
                return dr;
            }
    }

    inside my Controller im just calling this above method like so

     public ActionResult Index()
            {
    
                DefaultManagement objManagment = new DefaultManagement();
                objManagment.PullData();
    
    
                return View();
            }

    Can someone help me out please.



    Monday, April 1, 2013 12:07 PM

Answers

  • User-1877113880 posted

    Got it working, i was missing dataTable = new DataTable(); just before it gets populated with the values.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 1, 2013 12:22 PM

All replies

  • User1291589676 posted

    Since SqlDataReader is connected in nature you have to use the active connection to read the data here you were closing your connection in you ExecuteReader function.

    Remove the conn.Close() and then try.

    Otherwise you can try with disconnected objects like SqlDataAdapter and fill the data to datatable and then return that datatable and use this datatable instead of SqlDataReader.

    Monday, April 1, 2013 12:13 PM
  • User-1137493631 posted

    try this,

    SqlCommand cmd = new SqlCommand(commandText, conn);
    cmd.Connection = conn;
    cmd.Connection.Open()

    Monday, April 1, 2013 12:17 PM
  • User-1877113880 posted

    hello, just commented out conn.Close() now im getting Object reference not set to an instance of an object.

    Monday, April 1, 2013 12:18 PM
  • User-1877113880 posted

    Got it working, i was missing dataTable = new DataTable(); just before it gets populated with the values.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 1, 2013 12:22 PM