I spend lots of time with my kid every day, and I've never noticed any difference in behavior when he eats lots of sugary stuff (usually once a week). Also, at every birthday party some parent will always start espousing this theory while pointing to the kids playing as evidence, and I've learned not to point out that their kid acted exactly the same way last week while visiting my kid with no sugar served at all... My pet theory is that people who claim this connection are mostly exposed to children running around and acting like children do at events where incidentally there's a lot of soda, cakes and candy.
If your kid is only eating sugar once a week that's really not the norm anymore and you've got my admiration.
Here's the thing I noticed when I was a single father. I'd have my daughter with me and she'd be fine doing what toddlers do. Someone would see us and whip out a piece of candy and give it to her, and then we'd say our goodbyes. 5-10 minutes later my daughter was bouncing off the walls, every single time that happened.
It's the sugar, and most all moms who spend a lot of time with their kids will tell you the same thing. That's who I learned about it from. And when I got up the nerve to start telling people, "Please don't give her candy" I stopped having that problem, when I was successful at stopping them.
I've seen the same thing with my youngest step-kids when my wife and I met and I took over the day to day caring for them. And I spent a great deal of time with "moms" learning from them and listening to them because when I started caring for my daughter myself (at 14 months) I realized fast that I didn't know shit about momming, and I was a very active father before then.
The funny thing about those who've posted links to "studies" here that dispute this is they must know there are many studies that do make this link for both sugar and Red 40, and that Red 40 is banned in many other countries because of it's links to making kids hyper and other issues.
I'll readily concede that these may not affect all kids the same, and that it probably affects young children most, but that really makes sense when you consider doses (serving sizes) and body weight. But in my experience it affects most children. I've not met many it doesn't, but I've met a lot of adults that ignore it.
The longer term ill effects of too much sugar are pretty well documented too. If you don't raise children to eat healthy foods you're pretty much failing as a parent.