Read Stored Procedure Return

Hi,
Using Chandu Thota's book Mappoint.net I attempted to set up a findnearby query using his example Implementing Spatial Search Using SQL Server.


The book uses C# I have attempted to convert the code to call the stored procedure as follows:

TryDim units As Int16 = 0

Dim cmd As SqlCommand = New SqlCommand("FindNearby")

'Assign input values to the sql command

cmd.CommandType = CommandType.StoredProcedurecmd.Parameters.AddWithValue("@CenterLat", latitude)

cmd.Parameters.AddWithValue("@CenterLon", longitude)cmd.Parameters.AddWithValue("@SearchDistance", distance)

cmd.Parameters.AddWithValue("@Units", units)Dim con As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("VehicleMarshallConnectionString").ConnectionString)

If con.State = ConnectionState.Closed Then con.Open()

If con.State = ConnectionState.Open Then

cmd.Connection = conDim dreader As SqlDataReader = cmd.ExecuteReader()

If dreader.Read() Then

If Not dreader.IsDBNull(1) ThenDim ordnum As String = dreader(1).ToString

End If

End If

End IfIf con.State = ConnectionState.Open Then con.Close()

Catch ex As Exception

Finally

End Try

 

The code returns no values but when I execute the stored procedure from the server explorer in visual studio I get the records I would expect in the output window. How would I read the returned values from my code.

Output Window

Running [dbo].[FindNearby] ( @CenterLat = 53.10531, @CenterLon = -2.4769, @SearchDistance = 100, @Units = 0 ).

ID ordnum ProxDistance

----------- ------------------------- -------------------------

1 009999/USP 0

2 109999/USP 14.5971373147639

3 119999/USP 57.7144756947325

No rows affected.

(3 row(s) returned)

@RETURN_VALUE = 0

Finished running [dbo].[FindNearby].

Regards,

JoeBo