Apparently it's been too long since I've played with SQL Server databases. I say this because I made a boneheaded mistake today that obliged me to spend some quality time in the debugger.
Someone asked me whether I could convert the good ol' cascading drop-down example from using an Access database to using SQL Server. My first reaction is "C'mon, how hard can it be? You just swap data sources."
This is like a teenager in a horror film asking "Hey, I wonder what's in the basement?" Cue the ominous music.
The hard-won wisdom of experience stays my hand from actually replying "C'mon, how hard can it be?" I have to assume that if it were easy, the person wouldn't have contacted me.
So I set about converting. Within seconds I see that it isn't just a matter of swapping data sources. There is the small matter of actually creating the SQL Server database to match the Access one. There are only a few tables, so it's not hard. Dee-dee-dee, zip thru that little exercise, take a bunch of defaults, there, done.
Then into the code to set up new SqlDataSource controls to match the Access ones, point the drop-down list controls to the new data sources, and tweak some code.
Ready? Test. Bam. YSOD. Fail.
(Update 28 Oct I updated the screenshot -- was showing the wrong error, oops. Error about the error.)
I eventually track this down to a problem in the markup, here:
<asp:DropDownList ID="DropDownList1"
runat="server"
AutoPostBack="True"
DataSourceID="ManufacturersDataSource_SQL"
DataTextField="Manufacturer"
DataValueField="Manufacturer"
SelectedValue='<%# Bind("ManufacturerName") %>'
OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList>
This worked fine in Access, but something about converting to SQL Server horks it up. (There is indeed a ManufacturerName field, I checked that.)
Anyway, long story short, I got this logic into code, where I could look at it in the debugger. I finally got it in the Immediate window:
See what happened? The data is padded with spaces. Now, why would that be? Because someone who was zipping thru the process of setting up new database tables took a default data type of nchar(25) instead of nvarchar(25). A subtle error, but also a beginner's error.
Moral: Data types matter! Wait, we already know that.
Moral: A person who spends too little time in SQL Server gets to pay a get-back-up-to-speed tax.
Oh, and another moral: Anytime you hear me say "C'mon, all you need to do is ...", better settle in for the long run, coz we're probably going to be here for a while.
Anyway, if for some reason the results of all this interest you, you can download the updated page and the Cars.mdf file here:
CascadingDropDown_SQLServer.zip