Friday Flurry


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.

-jdt-

Advertisements

2 thoughts on “Friday Flurry

  1. Isn’t it just one query to set all those wrong values? I can’t remember, but I thought you could run SQL-looking commands at your db.

    Access understands basic SQL, yeah. What happens, however, is either the query in place on the page now will stop working completely when the values in that field all become NULL, or the customers in the search all appear multiple times because of the ridiculous report we get which has all those incorrect values in the first place. A choice of poisons. Either way I have to normalize the data.

    If that’s true, you just need something like ‘UPDATE your_table SET customer_number = NULL WHERE customer_number = “” ‘

    I did that in my test system and you’re right, that’s all it takes to get them all to be NULL (there’s no way, apparently, to set them all to a zero-length string), but see above. In either case I have to figure out a way to normalize the data and make sure there’s nothing wrong with it going forward when we have to load more customers.

    Good stuff, B! Thanks!

  2. Woah, dude, nice problem-solving! I am thoroughly impressed.

    Thanks, but I’m sure you’re only being kind. I bet you’d have solved this within moments of the discovery made by the other programmer weeks ago. Still, progress is progress, right? 🙂

Hey, what's up? Tell me whatcha think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s