Operation is not allowed when the object is closed. 1

[SWTpaul]

I get the following error: Operation is not allowed when the object is closed.

It points the the line that reads: While Not Rs.EOF

Sort of baffled, I don't know why the recorset would be closed?!

  Dim sZipCode, sMiles
  
  sZipCode	= "91403"
  sMiles	= 5

  Set MyConn = Server.CreateObject("ADODB.Connection")
  MyConn.Open "DSN=WEBSITE;UID=XXXX;PW=XXXX"
  sSQL = "Declare @HighLatitude float "&_
  	   "Declare @LowLatitude float "&_
		 "Declare @HighLongitude float "&_
		 "Declare @LowLongitude float "&_
		 "Declare @StartLatitude float "&_
		 "Declare @StartLongitude float "&_
		 "Declare @LatitudeRange float "&_
		 "Declare @LongitudeRange float "&_
		 
		 "SELECT @StartLatitude = Latitude, @StartLongitude = Longitude "&_
		 "FROM zipcd "&_
		 "WHERE zip = '"&sZipCode&"' "&_
		 "Set @LongitudeRange = '"&sMiles&"' / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60) "&_
		 "Set @LatitudeRange = '"&sMiles&"' / 69.045454545454545454545454545455 "&_
		 "Set @LowLatitude = @StartLatitude - @LatitudeRange "&_
		 "Set @HighLatitude = @StartLatitude + @LatitudeRange "&_
		 "Set @LowLongitude = @StartLongitude - @LongitudeRange "&_
		 "Set @HighLongitude = @StartLongitude + @LongitudeRange "&_
		 
		 "SELECT cmcsnm, cmcad1, cmcad2, cmcity, cmstat, cmzip4 "&_
		 "FROM cusms "&_
		 "WHERE cmzip4 IN(SELECT zip FROM zipcd WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude)  AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude)) "&_
		  "  AND cmslly > 3000"

  Set Rs = MyConn.Execute(sSQL)
  
  While Not Rs.EOF
    Response.Write(Rs("cmcsnm"))
    Rs.Movenext
  Wend

 

[gmmastros]

Change...
[tt] sSQL = "Declare @HighLatitude float "&_
"Declare @LowLatitude float "&_
"Declare @HighLongitude float "&_
[/tt]

To...

[tt]
sSQL = "[!]SET NOCOUNT ON[/!] " & _
"Declare @HighLatitude float "&_
"Declare @LowLatitude float "&_
"Declare @HighLongitude float "&_
[/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SWTpaul]

Oh dear... how silly. Thank!

 

[gmmastros]

You may find this thread interesting. thread183-1197121

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SWTpaul]

Excellent... thank you... that was the next step.