Another fairly hard-core post on .Net today, so if you don’t program in C# .Net, you can probably ignore this! Recently, I have started playing with integrating databases into C# for the first time. I’ve done a little bit of data binding before, but I’ve never used the DataGridViewControl until this week.
I’ve been doing what I would consider to be very simple stuff, but had hit a brick wall recently that has sucked up hours of my time. It’s times like this that I really miss having colleagues and friends who are working on the same technologies as me, because I’m sure that anyone who’s used ADO and this control for more than a little while will probably know all about how to fix it and so on.
Anyway, I was developing a ‘lookup’ field from a grid; that is, the underlying data required a numeric id to identify another record; but no-one can remember what number identifies a supplier… so you really want an easier way to set that value. The result of the process is something like the image shows below; instead of showing a numeric ‘SupplierID’, you provide a drop-down-list of the various company names.
Now, behind the scenes, the system knows the company name and the associated Supplier ID, and it uses the latter to populate the database. Lovely stuff.
But I was doing my own project with my own data, so looking at various resources, I simply emulated their approach to replicate this effect. I rapidly started experiencing the following error messages: “The following exception occurred in the DataGridView: System.ArgumentException: DataGridViewComboBoxCell value is not valid.” and the associated dialog is here:
I was getting these messages as I closed the form! I could change data at will and save it, so logically it seemed like my code must be ‘right’ because I could prove the data updates were happening ok; and that seemed to suggest that the value of the cell was valid, whatever the message said. The error messages would repeat apparently infinitely, and the only way to stop them was to kill the debugging session.
Searching the web for help on this error resulted in many hits of enquiries on developer forums, and yet none of them seemed to apply to my situation. Many did not refer to databases, but objects, and certaily nothing I could find rang true to suit my situation. But before I cover the whole wind-up that was finding the cause of this problem, let’s take a quick look at what that cause was.
If You’re in a Rush, the Problem Was…
After many hours, attempts and re-attempts at fixing the problem, I found out that the issue was related to the AutoSizeMode property of the DataGridView. I had changed it on some of my columns to expand the column width to fit the data; this was not a problem for any column except the one(s) that had the lookup on; it was these columns that were the source of the error.
I regret that I do not have a solution as such right now, other than to leave AutoSizeMode to it’s default value on each column that you have a lookup on, and also make sure not to change the setting on the grid itself, which will have similar impact.
Solution now found! Read more on the story in the rest of this page, or take a look at my next post, which documents cause and solution.
I Guess ‘Playing’ can be a Bad Thing
So, a substantial part of my problem here was caused by me ‘playing’ to learn. I’d see settings in the IDE and just have a go at changing it, if it looked interesting. And guess what, when you are dealing with lookup columns, having the ability to resize the column to match the width of the contents is very interesting; as the text you will want to display will invariably be much longer than the number column is replaces.
Nevertheless, my bad for not sticking to the main target of getting a lookup sorted out; but in context, adjusting the AutoSizeMode property was quite natural for me to want to do for several columns.
Ah – typical! I’ve just found a support thread that documents the cause I found… and in fact notes a possible solution that seems intelligible to me! See this link.
Other resources I found were unanswered questions… the same message, but affecting the form when it opened (apparently due to an actual difference in data-types).
Somewhat by chance, I happen to have received two books in the post today, and one of them has been tantalising in hinting at an answer, but massively frustrating in a distinct lack of workable examples. Now, obviously I’ve not actually read the whole book in a day, but skipping through any number of sections, I finally found a section that hinted that setting the AutoSizeMode on a lookup cell could cause an infinite loop of some sort (no mention of an error though). The book in question then gave an example of a modification to some example code earlier in the chapter. So I went back to that earlier code, and tried to follow the instructions to implement it (for some reason, that example was not in the downloaded code solutions). Unfortunately, despite being a code example that did have step-by-step instructions right from the start of opening a new project, by the second or third line of code it was obvious that something was missing; probably just a reference or a prefix or something, but nevertheless an extreme frustration. Even reading and understanding the intention of that code example, I still could not quite see how it applied to my siuation (which was why I wanted to implement it in the first place).
Anyway, so then I went back to the ‘added’ code example later in the chapter… and re-reading the added code, realised that it can not have been for the example listing that they noted, because the addition referred to an object / variable that did not exist in that example. Worse still (perhaps) searching all the code samples for the book, it did not appear in any of it.
All in all then, this whole experience has been layer after layer of frustrations and hurdles. I hit what seems to be a bug, missed the solution in my searches online, and find numerous hints about a solution, and then errors, in the first few sections of the book that I bought almost specifically for the purpose of learning more about data binding.
It’s times like this that I think I should move into Quality Control or Proofreading. Somehow, I just seem to find these bugs – and whilst I never exclude the possibility that there has been some stupidity on my own part, I don’t think that’s the case this time (with the exception of not finding that solution post earlier, which really was a bit dumb on my part).