There's a conflict of interests, for lack of a better term. It's nice that the President is advocating more science education and science literacy for the general population (with which I personally agree). But there's also the state-mandated testing systems, some of which require science tests (thanks to NCLB, all states require math and language arts tests, but some states went above and beyond).
If a state requires science testing, chances are that many of its teachers will teach to that test in an attempt to keep the school afloat. Yes, there are some teachers who do amazing projects and truly inspire students, but many will not. Many teachers will feel (and are feeling) pressure to just get good scores. This atmosphere is not at all condusive to making science (or any other subject taught this way) cool.
As far as getting the public interested in science, the media has to start taking an active interest in science and making it accessible to the general public. Let's face it: a lot of new discoveries are not very simple (LHC, anyone?). Explaining why it's an amazing project and worth funding should be part of a science reporter's job. When I worked at a large public science museum, our job was to take material and bring it down to a 5th-8th grade level, which would help compensate for kids, non-native-English speakers, and non-science-literate parents. Even TV shows like CSI do not make science accessible: the fancy-schmancy machines and lab-coat-clad workers are the ones to determine identities of mysterious materials or vials of evidence.
Which is another reason that kids don't want to go into science... "dude, you'll be a NERD!"