Along the lines of Cedar Point etc., I would consider Knoebels and/or HersheyPark, both in PA and both with associated campgrounds. HersheyPark has a sizable water park area, and shuttle service from their campground. Knoebels has a big pool with a few water slides but not really a large scale water park, but it's an awesome place none the less. They have one campground (with basic ammenities and some pretty small sites) that is right at the park and another (bigger sites and a bit nicer overall) that is a few miles away with periodic shuttle service included.
In the opposite direction, Disney's Fort Wilderness gets lots of rave reviews, though it isn't exactly inexpensive to say the least.
