certain rows not updating data in a DGV?

[Jfisher387]

Hello all,

Working on inventory software for in house use at my machine shop i work at. I have been working on this and learning c# for over a year now. Still a long road to go. Currently I have a DGV that we perform transactions on to change the qty of our in house tooling. and if it falls below my stock qty then we add to our order qty. pretty simple.

this has been in use for months now, not to many notable issues. today i noticed some of the newer tooling in the list wont update the qtys no matter what i try. I'm using a MongoDB to store all the data. I cannot find a reason some lines would work and some wont.

the only differences i can think of is that the newer tooling i added in with a in app feature (called "add tool"), whereas older tooling was added directly into a json file. i see no difference in the structure of the two different input methods.

My hunch is there is a setting for the DGV itself in visual studio that is limiting access or something to the lower rows. But a quick look there and i dont see much of an option to look into more.

I can post pictures, code etc. but not sure where to start. ill attach a few possible culprits below just in case.

private void ToolPull_Click(object sender, EventArgs e)
        {

            if (transactionSerial_Box.Text.Length != 4)
            {
                MessageBox.Show("Serial number must be 4 digits. Please try again.", "invalid serial number", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {

                //define transaction variables
                string serialSearch = transactionSerial_Box.Text;
                const bool returnTool = false;
                const bool toolBreak = false;
                int transQty = Convert.ToInt32(numericUpDown1.Value);
                DateTime timeNow = DateTime.Now;
                int rowIndex = -1;
                Object onHand;

                //searching for a serial match in each row
                foreach (DataGridViewRow row in toolingGridView.Rows)
                {
                    if (Convert.ToString(row.Cells[1].Value) != serialSearch) continue;

                    //qty too low.. can't pull
                    if (Convert.ToInt32(row.Cells[5].Value) < transQty)
                    {
                        MessageBox.Show("On hand Quantity is less than transaction Quantity. Cannot pull Tool");
                        return;
                    }
                    toolingGridView.ClearSelection();
                    rowIndex = row.Index;
                    toolingGridView.Rows[rowIndex].Selected = true;

                    onHand = Convert.ToInt32(row.Cells[5].Value) - transQty;
                    row.Cells[5].Value = onHand;
                    int order = Convert.ToInt32(row.Cells[6].Value) - Convert.ToInt32(row.Cells[5].Value);

                    if (order <= 0)
                    {
                        order = 0;
                    }

                    var filter = Builders<Tool>.Filter.Eq("Serial", Convert.ToString(serialSearch));
                    var update = Builders<Tool>.Update.Set("On Hand Qty", Convert.ToInt32(onHand));
                    var orderUpdate = Builders<Tool>.Update.Set("Order Qty", order);

                    var result = toolCollection.UpdateOne(filter, update);
                    var orderResult = toolCollection.UpdateOne(filter, orderUpdate);

                    var trans = new Transaction(
                        (ObjectId)(row.Cells[0].Value),
                        Convert.ToInt32(row.Cells[1].Value),
                        Convert.ToDouble(row.Cells[2].Value),
                        CurrentEmployee.GetEmployee(),
                        Convert.ToString(row.Cells[8].Value),
                        Convert.ToString(row.Cells[9].Value),
                        Convert.ToString(row.Cells[10].Value),
                        Convert.ToString(row.Cells[11].Value),
                        Convert.ToDouble(row.Cells[12].Value),
                        Convert.ToDouble(row.Cells[13].Value),
                        timeNow,
                        returnTool,
                        toolBreak,
                        transQty);

                    transactionsCollection.InsertOne(trans);
                }
            }
            //clear the input boxes after each entry. regardless if it was a valid transaction or not.
            ClearInputBoxes();
            this.ActiveControl = transactionSerial_Box;
            employeeToolCostLabel.Text = Convert.ToString(EmployeeBalance());

            UpdateFunction();

        }

 public void UpdateFunction()
    {
        ReadData();
        UpdateOrderDGV();
        ResizeColumns();
        TotalStockCosting();
    }
    public void ReadData()
    {
        List<Tool> list = toolCollection.AsQueryable().ToList();
        toolingGridView.DataSource = list;
    }

**EDIT**
it is working to alter the qty's directly in the DB. and it will update as it should in the software based on the DB so it is reading all the data in properly. just isnt liking manipulating some of the info from the prompts..

 

[cjard]

int order = Convert.ToInt32(row.Cells[6].Value) - Convert.ToInt32(row.Cells[5].Value);

Painful, isn't it? There are easier ways..

Couldn't quite get the problem though; are you saying that on some rows you type into the DGV and cannot retrieve the data? or that you go to save the data in mongo and it refuses?

 

[Jfisher387]

Painful, isn't it? There are easier ways..

Couldn't quite get the problem though; are you saying that on some rows you type into the DGV and cannot retrieve the data? or that you go to save the data in mongo and it refuses?

Yea I know it would be best to get to that data by using the datasource instead. For now it works. but I plan to go back through and repair sloppy sections of code. I have done several iterations so far and its loads better. But again its a long road.

lets look at an example of my issue though.

in the first screenshot. everything works as expected i type in 1073, click pull. it then updates my onhandqty from 3 to 2. if i do this again it falls below the stock qty and updates orderqty as well to 1.

in the second screenshot i click pull on 9511, and it appears nothing happens. i also keep track of all transactions, and i can see that the transaction occured. but my qtys dont reflect that.
below 9511 has a onhandqty of 2. when i click pull it stays at 2.

 

[Jfisher387]

Painful, isn't it? There are easier ways..

Couldn't quite get the problem though; are you saying that on some rows you type into the DGV and cannot retrieve the data? or that you go to save the data in mongo and it refuses?

The type of serial changed it looks like. i just noticed in my DB that serial on 9511 is a int32 where 1073 is a string.

 

[Jfisher387]

The issue was that I had my serialSearch variable as a string, so it never found a match in the database on the newer tools that are now a type of int32. i will now have to go in and change all the old tooling to int32 as they dont work now.

 

[cjard]

Ahh.. Mongo

 

[Jfisher387]

Ahh.. Mongo

but now to address your concern with how i accessed my data. What would you do differently?
Am I to assume you expect to see a foreach looping through the collection, instead of the datagridview? then update the DGV afterwards?
then instead of

                    int order = Convert.ToInt32(row.Cells[6].Value) - Convert.ToInt32(row.Cells[5].Value);

we would have something like

                    int order = collection.stockqty - collection.onhandqty;

EDIT**

Here I changed the code you were concerned with. I'd like to know if this is the better approach.

private void ToolPull_Click(object sender, EventArgs e)
        {

            if (transactionSerial_Box.Text.Length != 4)
            {
                MessageBox.Show("Serial number must be 4 digits. Please try again.", "invalid serial number", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {

                //define transaction variables
                int serialSearch = Convert.ToInt32(transactionSerial_Box.Text);
                const bool returnTool = false;
                const bool toolBreak = false;
                int transQty = Convert.ToInt32(numericUpDown1.Value);
                DateTime timeNow = DateTime.Now;
                //int rowIndex = -1;
                int onHand;

                List<Tool> list = toolCollection.AsQueryable().ToList();

                foreach (var tool in list)
                {
                    if (tool.ProductLink != serialSearch) continue;

                    //qty too low.. can't pull
                    if (tool.OnHandQty < transQty)
                    {
                        MessageBox.Show("On hand Quantity is less than transaction Quantity. Cannot pull Tool");
                        return;
                    }

                    onHand = tool.OnHandQty - transQty;
                    tool.OnHandQty = onHand;
                    int order = tool.StockQty - onHand;

                    if (order <= 0)
                    {
                        order = 0;
                    }

                    var filter = Builders<Tool>.Filter.Eq("Serial", serialSearch);
                    var update = Builders<Tool>.Update.Set("On Hand Qty", onHand);
                    var orderUpdate = Builders<Tool>.Update.Set("Order Qty", order);

                    var result = toolCollection.UpdateOne(filter, update);
                    var orderResult = toolCollection.UpdateOne(filter, orderUpdate);

                    var trans = new Transaction(
                        (ObjectId)tool.Id,
                        tool.ProductLink,
                        tool.Cost,
                        CurrentEmployee.GetEmployee(),
                        tool.Type,
                        tool.Vendor,
                        tool.ProductId,
                        tool.Description,
                        tool.GeometryOal,
                        tool.GeometryDc,
                        timeNow,
                        returnTool,
                        toolBreak,
                        transQty);

                    transactionsCollection.InsertOne(trans);
                }
            }
            //clear the input boxes after each entry. regardless if it was a valid transaction or not.
            ClearInputBoxes();
            this.ActiveControl = transactionSerial_Box;
            employeeToolCostLabel.Text = Convert.ToString(EmployeeBalance());

            UpdateFunction();

        }

 

[cjard]

Typically I've always used my datagridview controls bound to a strongly typed datatable, usually through a BindingSource. One opens the dataset designer in VS(add a dataset type of file and double click it) and adds tables, which are analogous to classes - they have columns analogous to properties and columns have types, lengths etc so I would e.g. have a Person datatable with a string Name column, an int Age column etc. Creating the datagrid on the form is usually a matter of dragging a node out of the datasources window and onto a form - a datagridview, bindingsource, dataset and some other fluff appears, and everything is wired up for you. Note that this doesn't work in .net core winforms projects; MS just turned the functionality off rather than work out all the bugs with it

In the code side you then deal exclusively (usually) with the dataset and its inherent Person datatable/ person rows, which makes life a lot nicer:

    foreach(var p in _someDataSet.Person){

      if(p.Age < 10) p.Style = p.Style + " Junior";

    }

rather than some code thet pulls data out as objects via the grid's cells collection, and casts/converts it..

DataTables also have support for primitive calcs so you could e.g. has a DataColumn for "resulting qty" that has an Expression of "[StockQty] - [RequestQty]", and the user typing 10 in the requestQty column will show the relevant sum - no need to react to the change, and do the math yourself, update the grid, etc etc

---

Database wise, I tend to use SQL Server these days; the chances of a dev being surprised by an accidental flipping of a column datatype should be impossible there..

 

[Jfisher387]

I used to use data tables on this project. but I didn't like how it worked on the code end. to me it was to much work adding columns and building the DGV in the way I wanted. this way I can just create a class in order of how I want data shown.

see my edit above. I already went in and changed anywhere I could find that accessed the DGV and changed it to the underlying data source. aligns closely with what your saying just taking a different approach.

Also side note I tried setting up mySQL database and could not get it to work, then i found mongo and it worked out pretty simply for me.

 

[cjard]

It sounds like you had code like:

  var dt = new DataTable();
  dt.Columns.Add("Name");
  dt.Columns.Add("Style");
  dt.Columns.Add("Age", typeof(int));
  dgv.DataSource = dt;

It'll work, but it's still tedious, because there is a lot of casting and conversion involved in using the table:

  foreach(DataRow r in dgv.DataSource As DataTable){
    if((int)(r["Age"]) < 20) r["Style"] = ((string)r["Style"]) + " Junior";
  }

When you create a dataset visually, the tables therein have rows of a particular class that act like the objects you prefer. Most of the components are written to understand and enable enhanced functinality when working with binding to datatables so you get some QOL improvements intrinsically..

..I've had similar frustrations with mySQL in the past too, and hence one of the reasons SQLS is my go-to DB these days. MySQL works, and nicely when abstracted away underneath something like EF, but I got very annoyed trying to use it in more primitive ways with datasets, or straight readers. If I was forced to use it these days I'd use Dapper or EF to hide it, but choice be given I go for SQLS because it Just Works Mongo's design intent is slightly different, and some argue it's better suited to OO development but it's always felt a little bit lacking in strict structure for my liking; the reason I code in C# rather than javascript is because I prefer the structure

 

[Jfisher387]

It sounds like you had code like:

  var dt = new DataTable();
  dt.Columns.Add("Name");
  dt.Columns.Add("Style");
  dt.Columns.Add("Age", typeof(int));
  dgv.DataSource = dt;

It'll work, but it's still tedious, because there is a lot of casting and conversion involved in using the table:

  foreach(DataRow r in dgv.DataSource As DataTable){
    if((int)(r["Age"]) < 20) r["Style"] = ((string)r["Style"]) + " Junior";
  }

When you create a dataset visually, the tables therein have rows of a particular class that act like the objects you prefer. Most of the components are written to understand and enable enhanced functinality when working with binding to datatables so you get some QOL improvements intrinsically..

..I've had similar frustrations with mySQL in the past too, and hence one of the reasons SQLS is my go-to DB these days. MySQL works, and nicely when abstracted away underneath something like EF, but I got very annoyed trying to use it in more primitive ways with datasets, or straight readers. If I was forced to use it these days I'd use Dapper or EF to hide it, but choice be given I go for SQLS because it Just Works Mongo's design intent is slightly different, and some argue it's better suited to OO development but it's always felt a little bit lacking in strict structure for my liking; the reason I code in C# rather than javascript is because I prefer the structure

I still have a branch with the old code. it looked like this

 //clears the dgv source when we update
            tooldataSet.Tables["Tool"].Clear();

            //DataTable with something in it, do the binding
            BindingSource SBind = new BindingSource
            {
                DataSource = tooldataSet.Tables["Tool"]
            };
            toolDataGridView.DataSource = SBind;
            if (DGVFilterByType.Text != "All")
            {
                foreach (var datum in ToolJson.Datums)
                {
                    string description = datum.Description;
                    string vendor = datum.Vendor;
                    double cost = datum.Cost;
                    string serial = datum.ProductLink;
                    int onHand = datum.onHandQty;
                    int stockQty = datum.stockQty;
                    int orderQty = ToolJson.Order(datum.stockQty, datum.onHandQty); //stockQty - onHand = orderQty, if value is less than 0, set the value of orderQty to 0.
                    string toolType = datum.Type;
                    string productID = datum.ProductId;
                    double diameter = datum.GeometryDc;
                    double oal = datum.GeometryOal;
                    string location = datum.location;
                    string employee = datum.employee;

                    if (toolType == DGVFilterByType.Text)
                    {
                        //Populate the DataTable with rows of data
                        DataRow dr = tooldataSet.Tool.NewRow();

                        // Fill the values
                        dr["product-link"] = serial;
                        dr["Type"] = toolType;
                        dr["ProductId"] = productID;
                        dr["Description"] = description;
                        dr["Vendor"] = vendor;
                        dr["DC"] = diameter;
                        dr["OAL"] = oal;
                        dr["Cost"] = cost;
                        dr["OnHandQty"] = onHand;
                        dr["StockQty"] = stockQty;
                        dr["OrderQty"] = orderQty;
                        dr["location"] = location;
                        dr["employee"] = employee;

                        //once all data is added to the row, add the row, and loop untill all data is loaded.
                        tooldataSet.Tool.Rows.Add(dr);
                    }
                }
            }
            if (DGVFilterByType.Text == "All")
            {
                foreach (var datum in ToolJson.Datums)
                {
                    string description = datum.Description;
                    string vendor = datum.Vendor;
                    double cost = datum.Cost;
                    string serial = datum.ProductLink;
                    int onHand = datum.onHandQty;
                    int stockQty = datum.stockQty;
                    int orderQty = ToolJson.Order(datum.stockQty, datum.onHandQty); //stockQty - onHand = orderQty, if value is less than 0, set the value of orderQty to 0.
                    string toolType = datum.Type;
                    string productID = datum.ProductId;
                    double diameter = datum.GeometryDc;
                    double oal = datum.GeometryOal;
                    string location = datum.location;
                    string employee = datum.employee;
                    //Populate the DataTable with rows of data
                    DataRow dr = tooldataSet.Tool.NewRow();

                    // Fill the values
                    dr["product-link"] = serial;
                    dr["Type"] = toolType;
                    dr["ProductId"] = productID;
                    dr["Description"] = description;
                    dr["Vendor"] = vendor;
                    dr["DC"] = diameter;
                    dr["OAL"] = oal;
                    dr["Cost"] = cost;
                    dr["OnHandQty"] = onHand;
                    dr["StockQty"] = stockQty;
                    dr["OrderQty"] = orderQty;
                    dr["location"] = location;
                    dr["employee"] = employee;

                    //once all data is added to the row, add the row, and loop untill all data is loaded.
                    tooldataSet.Tool.Rows.Add(dr);

                }

                toolDataGridView.DataSource = SBind;
            }
            ResizeColumns();
        }

 

[cjard]

Actually, that looks like a hybrid of "stringly typed" datatables, and strongly typed ones, because you've got tooldataset.Tool, which indicates a strongly typed dataset but most of the code accesses it in weakly typed ways. Not many web resources are well up on ST datasets so they usually give crap advice related to stringly (weakly) typed ones, which is perhaps where all the dr["ColumnNameAsAStringHere"] comes from.

A strongly typed dataset derives (inherits) from a weak one so it does support e.g. string pn = toolDataSet.Tables[0].Rows[0]["ProductName"] as string
but it's intended to be used like string pn = toolDataSet.Tool.First().ProductName - it has named typed props for columns and tables, it works with LINQ out of the box and so on. If you're putting column names in strings something has gone wrong somewhere (usually doing e.g. toolDataSet.Tool.Rows.... - as soon as you access .Rows you're ruined; youre supposed to just treat the datatable as an array of objects e.g. toolDataSet.Tool[3].ProductName = "Screwdriver" or foreach(var r in toolDataset.Tool) r.ProducName = "Screwdriver"

Strongly typed would probably have boiled all your code there down to just:

    foreach (var d in ToolJson.Datums)
      toolDataSet.Tool.AddToolsRow(
               d.Description, d.Vendor, d.Cost, d.ProductLink, d.onHandQty, d.stockQty, ToolJson.Order(datum.stockQty, datum.onHandQty), d.Type, d.ProductId, d.GeometryDc, d.GeometryOal, d.location, d.employee
      );

If you were to teach a mapper tool like AutoMapper the mapping from a ToolDatum to a ToolDataRow then it's probably more like:

    foreach (var d in ToolJson.Datums)
      toolDataSet.Tool.AddToolsRow(mapper.Map(d, someDataSet.Tools.NewToolsRow()));

(I never used strongly typed DTs with automapper; it might even know how to map from a collection of X to a datatable, like mapper.Map(ToolJson.Datums, toolDataSet.Tool

In any of these approaches there isn't any messing with rebinding the datasource, or resizing the columns; you just set the auto size strategy and the datasource binding once (in the designer) and then if you clear and fill the table anew, the grid updates to reflect it

---

Also, most json libs have ways of divorcing the JSON propensiity for namesThatLookLikeThis from C# names which BeginWithAnUppercase, so your "onHandQty" etc could have correctly been OnHandQty as C# properties