Not much happening, so I guess it’s not a “flurry”, per se, but there are a few things happening.
For one thing, I found an issue with our data which has been plaguing my ASP classic pages for a few months. Every once in a while, one of the intranet pages would fail to find a customer someone looked for, or wouldn’t locate a customer to populate the pages with their orders for us, or something like that. For the ATR Coordinator, the problem manifested a couple months back when she tried to enter an ATR for a customer and couldn’t because the pages couldn’t locate the customer.
She asked me to add them to the database so she could process the request. When I tried, I found they were already in the database. But…then what gives? Why wouldn’t the customer come up in a search?
So the new IT manager for the business group with which we share the space – who is a full-on .NET developer and knows his stuff – was brought in (by the ATR Coordinator without my consent or knowledge) and offered to help. I accepted eagerly. He sat down and started working through the page methodically and when he saw the issue, he was stumped too.
Then he did something magical – he added a tiny bit of code to make the query see two different kinds of values from the database, and voila! The customer was found! But… there was a cost.
The customers each showed up two, sometimes three, times in the search results. Complete with duplicated IDs, contacts, phone numbers – you name it.
But hey! At least it worked, right?
So we’ve been like that for a bit. Then, a similar problem cropped up in a completely different area of the intranet. But the data was the same. I’ve been crawling through it for a couple of days now trying to fix the problem, but to no avail, until yesterday.
I discovered the database contained not one, but two types of data for a single field, when the field didn’t have an entry. There’s either a NULL value, which is a database standard, or a zero-length string, which is a series of characters. For example, the word “pie” is a string of length three; i.e., three characters long. Of course, “213” is also a string of length three, IF you consider the characters (digits) as characters instead of numbers. So, that’s a string, and in this particular field of this particular database, there are values stored as either a string (when there’s an entry) or a NULL (when there isn’t) or a zero-length string.
But the bizarre part? The zero-length string is the only value the web page searches the database for. So if the customer record had a null in that field, it wasn’t found. Because nothing, empty strings (zero-length strings) and nulls are all different things in computer world.
Why the old admin chose to look only for the zero-length string is anyone’s business. I can’t ask him, and there’s a story behind that, but that’s for another day. Or blog. Or blogger. But that’s what I’ve got and that’s what I found by poring over the page, the data, and crawling through line by line, cleaning up the code on that page.
Then, I used Visual Studio’s query utility to quiz the database and get all the data in that table. That’s when I found the mixed values in this particular field, which is used as one of the criteria for the query on the ASP page. Mystery solved. Now, how to fix it?
There are some 40K records in that database. I’m NOT going to walk through it by hand and change the values. I can set the default value to be NULL on the table design in Microsoft Access (which is the database we use for some reason), but that only works for new records. So I have to update the table with either one or the other. I think the best choice is NULL, but then, I’m not a dba.
Next, I lost some weight. Rolled the middle digit down. But I gained that back and then some, so I’m miffed about that.
And I’m still not able to find time to write anything. Between the programming classes and the work and everything else, there just doesn’t seem to be time during the week anymore. I can’t figure it out. Up until recently I seemed to be able to get more done when I get home at night. I suspect we have an issue with increased TV time, but can’t be sure. And I can’t stop the family from enjoying themselves just because I can’t.
Hope you all have a good weekend. I plan to!
Next week I start MVC training, I think.