I recently did an online support session with a customer with a very curious problem. Apparently, the drop-down lists, radio button lists, and other list controls didn’t work in XMod Pro when editing a record.
So, an XMod Pro customer submitted a help desk ticket in which he couldn’t seem to get the list controls in his edit forms to work. It seemed that no matter what control he used – radio button list, drop-down list, checkbox list, etc. – the controls simply would not reflect their corresponding value in the database.
After some email conversation and a look at his form, I tried to replicate the issue locally on my development machine. I couldn’t do it. Everything seemed to work flawlessly and, of course, XMod Pro had been passing its unit tests with no problem. But, I kept looking at the form and trying to figure out what the problem possibly could be. Everything looked correct. He was just trying to match up simple values like 1 or 2.
Finally, I asked if the customer would share his screen with me so I could start poking around… He did a demonstration for me and the form didn’t appear to work. I looked at the template and it was passing the values correctly too. I even looked at the data in the database – all good.
The next step was to verify the values were coming in correctly to the Edit form so I had him put some <Text> tags in the form to display values in the record as plain text. I made sure to surround those values with single quotes (‘). This is a valuable tool when debugging because if you have an empty value, you won’t be able to see that it’s blank unless you surround it with something visible. Single quotes are un-obtrusive and work well for this.
After editing the record, the form was showing: ‘20’ ‘2 ‘ and ‘2 ‘. The first value was the record ID and indicated the correct record was being retrieved. While the following values appeared to be what the list controls should be matching, on closer inspection, I noticed what appeared to be spacing after the 2 and before the closing quote. These should have been just: ‘2’ and ‘2’.
My first thought was that the Add form was saving the incorrect values to the database. Nope. Everything was fine.
I had the customer right-click on the first ‘2 ‘ and choose “Inspect Element”. This allows you to go directly to the HTML source code for that element. Once he did that then I had my answer. The browser was displaying ‘2 ‘ but the HTML source code was actually: ‘2 ‘. Immediately I knew where to look.
I instructed the customer to fire up SQL Server Management Studio and select “Design” for the table in question. Sure enough, those columns weren’t numeric fields and they weren’t plain text fields. They were “char(50)” fields.
Database programmers and application developers already know where I’m going with this but XMod Pro opens up database programming to non-DB programmers so this is worth pointing out. The “char” data type in the database is a fixed-length data type. That means if you set your column to “char(50)” then the column will reserve space for 50 characters, regardless of whether you use them or not. So, if you use only 1 character, the database will essentially pad the value with 49 spaces. That’s what was happening in this case. The Add Form was saving the value of “2” to the database, but the database was storing “2” plus 49 more spaces. Then, when the Edit Form went to try and match “2 (plus 49 spaces)” with “2” it couldn’t find a match because “2” is not the same as “2 “.
The moral(s) of the story:
- If you are new to database programming, you usually should stay away from fixed-length text data types unless you really require them. They take up more space in your database and can cause problems like the one I described. Instead, you should use “varchar” data types or, if you will be storing unicode values – nvarchar.
- Even if you’re an old hat with databases and programming, be sure not to overlook the obvious. Sometimes, the reason the computer isn’t working is because it’s not plugged-in. Check your data types and the actual values coming from the database.