none
Sql query showing same data for each interatation RRS feed

  • Question

  • Hi,

    I am trying to run to a SQL query and us a value from it in another query, so I can get more data. However, I am getting the same result each time.

    db.Database.Connection.Open();

    decimal count = 0; var Data1 = new List<master_history>(); var command = db.Database.Connection.CreateCommand(); command.CommandText = "SELECT master_history.id , master_history.department , master_history.no , master_history.order , master_history.status , master_history.paxa , master_history.day , master_history.open , master_history.close , master_history.res , master_history.user , master_history.op, departments.department, departments.departname, op.op_id, op.op_name FROM master_history LEFT JOIN departments ON master_history.department= departments.department LEFT JOIN op ON master_history.op = op.op_id WHERE master_history.status='O' ORDER By master_history.order DESC "; using (var retrevedData = command.ExecuteReader()) { foreach (var row in retrevedData) { Data1.Add(new master_history() { id = Convert.ToString(retrevedData["id"]), depart = Convert.ToString(retrevedData["department"]), deprtname = Convert.ToString(retrevedData["department_name"]), ...etc }); count = count + 1; var orderData = new List<id_order>(); var command2 = db.Database.Connection.CreateCommand(); command2.CommandText = "SELECT analysis_history.analysis_id , analysis_history.unit_id , analysis_history.item , analysis_history.qty , analysis_history.price , analysis_history.amount , analysis_history.itemXd , analysis_history.remarks , analysis_history.taxvalue , analysis_history.sequence , analysis_history.time , analysis_history.discount , analysis_history.department , analysis_history.operator , analysis_history.arrangement FROM analysis_history WHERE analysis_history.unit_id ='" + retrevedData["id"] + "'"; using (var orderedItems = command2.ExecuteReader()) { foreach (var item in orderedItems) { orderData.Add(new pos_id_order() { analysis_id = Convert.ToDecimal(orderedItems["analysis_id"]), unit_id = Convert.ToDecimal(orderedItems["pos_id"]), item_id = Convert.ToDecimal(orderedItems["item_id"]),

    ....etc }); ViewData["OrderItemsOutput"] = orderData; } } } var order = Data1.OrderByDescending(c => c.order); ViewData["HisListOutput"] = order; db.Database.Connection.Close(); ViewBag.rowcount = count; return View(); }

    I've tried moving the second query to a different part in the "foreach (var row in retrevedData)" loop, but I get the same result.

    Each POS_id should have 1 (or more) item(s) depending on how many where on the order (Pos_id).

    The above code it's getting the (only) item for order (pos_id) 539 (not shown on the screen snippet) and outputting it to every order (instead of showing there own order item(s).

    Can someone point out what I need to do?

    thank you

    Friday, May 18, 2018 4:19 PM

Answers

  • Hi,

    I managed to get it working by using

           public ActionResult Index()
            {
                db.Database.Connection.Open();
    
                decimal count = 0;
    			
                var POSData = new List<pos_master_history>();
                var command = db.Database.Connection.CreateCommand();
                command.CommandText = "SELECT master_history.id , master_history.department , master_history.no , master_history.order , master_history.status , master_history.paxa , master_history.day , master_history.open , master_history.close , master_history.res , master_history.user , master_history.op, departments.department, departments.departname, op.op_id, op.op_name  FROM master_history LEFT JOIN departments ON master_history.department= departments.department   LEFT JOIN op ON master_history.op = op.op_id WHERE master_history.status='O' ORDER By master_history.order DESC ";
    
                using (var retrevedData = command.ExecuteReader())
                {
                    foreach (var row in retrevedData)
                    {
    					Data1.Add(new master_history()
    					 {
    					   id = Convert.ToString(retrevedData["id"]),
    					   depart = Convert.ToString(retrevedData["department"]),
    					   deprtname = Convert.ToString(retrevedData["department_name"]),
    					   ...etc
    					  });
    					count = count + 1;
    
    
                        var command2 = db.Database.Connection.CreateCommand();
                        command2.CommandText = "SELECT pos_analysis_history.analysis_id , pos_analysis_history.pos_id , pos_analysis_history.item_id , pos_analysis_history.quantity , pos_analysis_history.price , pos_analysis_history.amount , pos_analysis_history.item_cancelled , pos_analysis_history.remarks , pos_analysis_history.main_tax_value , pos_analysis_history.order_sequence , pos_analysis_history.order_time , pos_analysis_history.discount , pos_analysis_history.department_id , pos_analysis_history.operator_id , pos_analysis_history.arrangement FROM pos_analysis_history WHERE pos_analysis_history.pos_id ='" + retrevedData["pos_id"] + "'";
    
                        using (var orderedItems = command2.ExecuteReader())
                        {
                            foreach (var item in orderedItems)
                            {
                                orderData.Add(new pos_id_order()
                                {
                                    analysis_id = Convert.ToDecimal(orderedItems["analysis_id"]),
                                    pos_id = Convert.ToDecimal(orderedItems["pos_id"]),
                                    item_id = Convert.ToDecimal(orderedItems["item_id"]),
    							....etc
                                });
                            }
    
                        }
                    }
    
                    var order = POSData.OrderByDescending(c => c.order_no);
                    ViewData["HisListOutput"] = order;
                    ViewData["OrderItemsOutput"] = orderData;
                    db.Database.Connection.Close();
    
                    ViewBag.rowcount = count;
                    return View();
                }
            }

    and then this in my cshtml

            foreach (var item2 in ViewData["OrderItemsOutput"] as IEnumerable<tableDisplayTest2.Models.pos_id_order>)
            {
                if (item.posid == item2.pos_id.ToString())
                {
            <tr>
                <td width="90px"></td>
                <td width="90px">@item2.pos_id</td>
                <td width="90px">@item2.item_id</td>
                <td width="90px">@item2.quantity</td>
                <td width="90px">@item2.item_cancelled</td>
            </tr>
                }
            }

    • Marked as answer by G-Oker Thursday, May 31, 2018 2:47 PM
    Thursday, May 31, 2018 2:47 PM

All replies

  • Hello G-Oker,

    Have a look at the following: https://msdn.microsoft.com/en-us/library/9kcbe65k%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    This shows a simple way of looping through a SQLDataReader. Notice how the Read() method is used to retrieve each row one at a time. Notice how in your code you are not looping through the rows but through the cells. Also note that you are not using the object row in your query. 


    Cheers, Jeff

    Saturday, May 19, 2018 5:31 AM
  • Note that each time you get an orderData, you overwrite the previous object, so that ViewData["OrderItemsOutput"] only contains the last data. Maybe you should keep the orderData list in some member of the corresponding parent master_history object?


    Saturday, May 19, 2018 6:51 AM
  • Hello,

    I've been trying to follow the URL you posted (thank you) and am trying to use the code

    public ActionResult Index() { SqlConnection connection = new SqlConnection(@"data source=server\dbname;initial catalog=database;user id=sa;password=mypassword;MultipleActiveResultSets=True;App=EntityFramework; "); DataTable dt = new DataTable(); List<string> result = new List<string>(); connection.Open(); //Read from the database SqlCommand command = new SqlCommand("SELECT ...this is my sql string ", connection); SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { result.Add(Convert.ToString(dataReader["analysis_id"])); result.Add(Convert.ToString(dataReader["pos_id"]));

    ...etc } connection.Close(); return View (result); }

    and I've changed my cshtml, I have change

    @foreach (var item in ViewData["HisListOutput"] as IEnumerable<tableDisplayTest2.Models.master_history>)

    to

    @foreach (var item in Model.result)

    but when I access the View, I get an error at the above line at reads


    Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: ''System.Collections.Generic.List<string>' does not contain a definition for 'result''


    can someone please help ? Thank you

    Wednesday, May 30, 2018 2:19 PM
  • Hello G-Oker, the model you are returning is now a list of strings. This means you can iterate through the items by just specifying the Model (List<string> does not have a result property):

    @foreach (var item in Model)


    Cheers, Jeff

    Wednesday, May 30, 2018 8:39 PM
  • Hi Jeff,

    I tried that and got this message back

    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    Thursday, May 31, 2018 6:46 AM
  • My code now looks like this (so, I am getting the multiple lines per order), but I still only get the data from the first iteration of the second SELECT query (see the image from original post).

    If I change 

    WHERE analysis_history.unit_id ='" + retrevedData["id"] + "'"

    to read

    WHERE analysis_history.unit_id ='16688'"

    I get those orders items under each other order.

    it's like the second query only gets run once, while the first query is run the correct amount of times.

    I tried moving

    ViewData["OrderItemsOutput"] = orderList;

    so it sat under

    ViewData["HisListOutput"] = order;

    after first moving 
    var orderList = new List<id_order>();

    ..but that made no difference to the data coming out.

           public ActionResult Index()
            {
                db.Database.Connection.Open();
    
                decimal count = 0;
    			
                var POSData = new List<pos_master_history>();
                var command = db.Database.Connection.CreateCommand();
                command.CommandText = "SELECT master_history.id , master_history.department , master_history.no , master_history.order , master_history.status , master_history.paxa , master_history.day , master_history.open , master_history.close , master_history.res , master_history.user , master_history.op, departments.department, departments.departname, op.op_id, op.op_name  FROM master_history LEFT JOIN departments ON master_history.department= departments.department   LEFT JOIN op ON master_history.op = op.op_id WHERE master_history.status='O' ORDER By master_history.order DESC ";
    
                using (var retrevedData = command.ExecuteReader())
                {
                    foreach (var row in retrevedData)
                    {
    					Data1.Add(new master_history()
    					 {
    					   id = Convert.ToString(retrevedData["id"]),
    					   depart = Convert.ToString(retrevedData["department"]),
    					   deprtname = Convert.ToString(retrevedData["department_name"]),
    					   ...etc
    					  });
    					count = count + 1;
    
    
                        var orderList = new List<id_order>();
                        using (SqlConnection conn = new SqlConnection(@"data source = server\sqlname; initial catalog = dbase; user id = sa; password = myPassword; MultipleActiveResultSets = True; App = EntityFramework;"))
                        {
                            conn.Open();
                            SqlCommand dCmd = new SqlCommand(@"SELECT analysis_history.analysis_id , analysis_history.unit_id , analysis_history.item , analysis_history.qty , analysis_history.price , analysis_history.amount , analysis_history.itemXd , analysis_history.remarks , analysis_history.taxvalue , analysis_history.sequence , analysis_history.time , analysis_history.discount , analysis_history.department , analysis_history.operator , analysis_history.arrangement FROM analysis_history WHERE analysis_history.unit_id ='" + retrevedData["id"] + "'", conn);
                            SqlDataAdapter da = new SqlDataAdapter(dCmd);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                            {
                                var model = new id_order();
                                model.analysis = Convert.ToDecimal(ds.Tables[0].Rows[i]["analysis"]);
                                model.id = Convert.ToDecimal(ds.Tables[0].Rows[i]["id"]);
                                model.itemid = Convert.ToDecimal(ds.Tables[0].Rows[i]["itemid"]);
    							.....etc
                                orderList.Add(model);
                            }
                            conn.Close();
                        }
                        ViewData["OrderItemsOutput"] = orderList;
    
                    }
    
                    var order = Data1.OrderByDescending(c => c.order_no);
                    ViewData["HisListOutput"] = order;
    
                    db.Database.Connection.Close();
    
                    ViewBag.rowcount = count;
                    return View();
                }
            }


    Thursday, May 31, 2018 12:07 PM
  • Hi,

    I managed to get it working by using

           public ActionResult Index()
            {
                db.Database.Connection.Open();
    
                decimal count = 0;
    			
                var POSData = new List<pos_master_history>();
                var command = db.Database.Connection.CreateCommand();
                command.CommandText = "SELECT master_history.id , master_history.department , master_history.no , master_history.order , master_history.status , master_history.paxa , master_history.day , master_history.open , master_history.close , master_history.res , master_history.user , master_history.op, departments.department, departments.departname, op.op_id, op.op_name  FROM master_history LEFT JOIN departments ON master_history.department= departments.department   LEFT JOIN op ON master_history.op = op.op_id WHERE master_history.status='O' ORDER By master_history.order DESC ";
    
                using (var retrevedData = command.ExecuteReader())
                {
                    foreach (var row in retrevedData)
                    {
    					Data1.Add(new master_history()
    					 {
    					   id = Convert.ToString(retrevedData["id"]),
    					   depart = Convert.ToString(retrevedData["department"]),
    					   deprtname = Convert.ToString(retrevedData["department_name"]),
    					   ...etc
    					  });
    					count = count + 1;
    
    
                        var command2 = db.Database.Connection.CreateCommand();
                        command2.CommandText = "SELECT pos_analysis_history.analysis_id , pos_analysis_history.pos_id , pos_analysis_history.item_id , pos_analysis_history.quantity , pos_analysis_history.price , pos_analysis_history.amount , pos_analysis_history.item_cancelled , pos_analysis_history.remarks , pos_analysis_history.main_tax_value , pos_analysis_history.order_sequence , pos_analysis_history.order_time , pos_analysis_history.discount , pos_analysis_history.department_id , pos_analysis_history.operator_id , pos_analysis_history.arrangement FROM pos_analysis_history WHERE pos_analysis_history.pos_id ='" + retrevedData["pos_id"] + "'";
    
                        using (var orderedItems = command2.ExecuteReader())
                        {
                            foreach (var item in orderedItems)
                            {
                                orderData.Add(new pos_id_order()
                                {
                                    analysis_id = Convert.ToDecimal(orderedItems["analysis_id"]),
                                    pos_id = Convert.ToDecimal(orderedItems["pos_id"]),
                                    item_id = Convert.ToDecimal(orderedItems["item_id"]),
    							....etc
                                });
                            }
    
                        }
                    }
    
                    var order = POSData.OrderByDescending(c => c.order_no);
                    ViewData["HisListOutput"] = order;
                    ViewData["OrderItemsOutput"] = orderData;
                    db.Database.Connection.Close();
    
                    ViewBag.rowcount = count;
                    return View();
                }
            }

    and then this in my cshtml

            foreach (var item2 in ViewData["OrderItemsOutput"] as IEnumerable<tableDisplayTest2.Models.pos_id_order>)
            {
                if (item.posid == item2.pos_id.ToString())
                {
            <tr>
                <td width="90px"></td>
                <td width="90px">@item2.pos_id</td>
                <td width="90px">@item2.item_id</td>
                <td width="90px">@item2.quantity</td>
                <td width="90px">@item2.item_cancelled</td>
            </tr>
                }
            }

    • Marked as answer by G-Oker Thursday, May 31, 2018 2:47 PM
    Thursday, May 31, 2018 2:47 PM