Visibility in the St. Lawrence river can be spectacular in October. I have experienced 100+ foot vis in the river near Brockville in October. However it is usually not that good. Most of the charter operators are still running then but it certainly slows down. Check the Upper St. Lawrence scuba charter association web page for links to charter operators and dive site descriptions. Depending on your level of training and experience, a trip to the Lillie Parson's and Robert Gaskin wrecks might be good for you.
Wayne A is correct about the water temperature. At that time of year it is however the air temperature that makes the difference in the diving day. If it is a bright sunny day then its just like diving in the summer except the leaves on the trees are red and orange not green. If it is a cold day then the surface interval can be miserable (especially if you are diving wet).
If you just want to do shore dives then the Rothesay, Connestoga and/or Lock 21 are easily accessible dive sites within an hour's drive of Ottawa.
I'm going to disagree with TomR about Kingston. Diving Kingston in October can be really ugly if the weather is uncooperative. It is a bigger gamble if you select Kingston in October and bottom temps are around 45F (the usual).
Enjoy your dives
Adrian