In VS2008, I set up a SQLDataSource and bound it to a GridView. The Gridview row had five editable fields and several read-only fields. I set up the SQLDataSource to use a stored procedure for database updates. I had some trouble binding the fields to the stored procedure, checking data types etc but eventually that was all resolved and the sp stopped returning errors such as ‘Too many parameters’.
All seemed to be going well, but the sp was not actually updating my record.
The problem was (ahem) that the sp was a shell I’d thrown together earlier and didn’t actually have an update statement yet…
Once that was fixed I assumed my troubles were over, but no, still no update visible in Management Studio.
I only used this sodding data binding because it’s suppose to save trouble, I thought, should’ve hand-coded the blooming thing like in the old days.
However, I fired up SQL Server Profiler. Things rapidly improved. I realised that my sp was indeed being called, but all my read-only values except one were being passed in as NULL. (Earlier, I had resorted to passing in all my grid columns to the sp to get around the ‘too many parameters’ problem. The unneeded ones were simply ignored).
After some more poking I discovered that the lucky field that was being passed correctly had been set as the DataKeyName on my GridView.
I changed the DataKey name on my GridView to the two fields that I actually needed for my sp’s where clause, and bingo!
I think the DataKey may have been set automatically. The select might have picked up the issue, except I was using an sp for that too, with parameters read from textboxes.
I know, I know, RTFM.