If you put the curtain rod up, will it cover the hole?
If so, what you want are some toggle bolts. They’re what you should have used in the first place. Basically, you thread the screw through your curtain rod fitting, then through the toggle bolt. You collapse the wings of the toggle bolt and push it through the hole. When it goes through, the wings snap open again. Then you tighten the screw, which pulls the now-open toggle bolt wings against the back of the drywall. This is a very strong mounting method.
Plastic drywall anchors are useless for anything but hanging pictures. Never use them in a place where there’s a force that pulls the screw away from the wall - they’ll eventually just pull out. Toggle bolts or metal screw-in anchors are the only solution for things like curtain rods, towel holders, and the like. Often, because you have no leeway over where they are positioned you are forced to go into the drywall without a stud. That’s when you use the toggle bolts. If you can, try to position at least one end so that there’s a stud behind the screws, then support the other end with the toggle bolts.
Whoever built our house put in all our towel holders and toilet paper holders with drywall anchors. And ALL of them failed and pulled out of the wall over time. I just took the last one down today, removed the loose drywall achors and replaced them with toggle bolts.
If you don’t have a stud finder, you can often spot where the stud is by shining a light down the wall (put the light close to the wall so it sweeps across the surface). Doing this will highlight any imperfections - inluding dimples where nails or screws are holding the drywall to the stud.
You can also figure out the position of a stud by looking at your electrical boxes. They will be nailed to studs. Measure the distance between two of them, and see if it’s a multiple of 12 or 16 - studs are using positioned 12" apart on center or 16" apart on center. Remember that the edge of an electrical box will be on the side of the stud, so the center of the stud is 1 1/4 inches back from that (2 x 4 studs are actually 1 1/2 by 3 1/2). So take a measurement along the bottom of the wall from the nearest electrical box to where you want to attach the curtain rod. Every 12 or 16 inches, make a mark. Then you can line up with the mark by dropping a plumb bob from where you want to hang the curtain rod to the mark.
This sounds like a lot, but it’s really fast. The trick is to make sure you know which side of the electrical box the stud is on. But you can usually figure that out by tapping around it, or even by removing the faceplate and shining a light inside and looking through the holes. Or you can look at other things attached to the wall, or measure out the distance from the electrical box to a known stud location like the end of the wall.
BTW, if you went through with the screw and met no resistance, but a longer one hit something, you’re not hitting the wall stud at all, unless the short screw wasn’t even long enough to penetrate the drywall. There should be no gap whatsoever between the stud and the drywall, obviously. So either you were using very tiny, inappropriate screws, or you were hitting something else in the wall with the longer screws - a fire break, an electrical wire, or something else. If the screw was really long, you might even have been going into drywall on the other side of the wall or the external plywood sheathing of the house. Eiither way, I would not continue to screw into whatever it was.
As for spackling… How big is the hole? If it’s just 3/8" or something, that’s the size of hole you’d need for a toggle bolt anyway. If it’s much bigger than that (i.e. a 1" hole), then you can’t spackle it closed - you need to make a plug first, attach it to the drywall, then use spackle to smooth it over and blend in the damage. But spackle isn’t structural, and it will shrink and crack over time if you use too much of it. And you can never screw into it - it will just crumble and break. It has no real strength of its own.
Hope this helps.