We just wrapped up teaching our sessions on ADO.net in ODU's ITPro curriculum, and [yet again] the subject of the CommandBuilder came up. While I first mentioned this in this post from 2003, it's a topic of confusion and warrants revisiting.
Paul Laudeman first pointed out this MSDN article on Weaning Developers from the CommandBuilder last year; Paul was in the midst of a data-access architecture debate at one of his client's sites and we were going back and forth about how to address the issue. I think Darrell Norton, Mark DiGiovanni, and Brendan Thompkins (among others) were included in this email thread from Spring 2003. The moral of the email-story is that even if you provide backup and documentation supporting your informed opinion, customers are under no obligation to take the advice! If I'm a painter and somebody pays me to paint their house black, once I've confirmed that's what they really want to do and I've explained how uncomfortable a black painted house may be, my job is still to paint; how badly do I need the painting business? But I digress . . .
Bottom line: the CommandBuilder is effective only with a very simple SELECT query. See the full article for some alternatives.
Also, rumours abound that Whidbey (now scheduled for a 2005 release) improves DataAdapter behaviour and addresses some of the CommandBuilder shortcomings. I haven't had time to drink much Whidbey kool-aid, so I can't speak from experience.