calling procedure as per an online article

[cmsbuffet]

What is wrong with this code? It produces 7 errors, but I copied it almost word for word from an online article.

Imports System.Xml
Imports System.Xml.XmlReader

Dim SQLXMLReader As XmlReader

        SqlCommand.CommandText = "GetZipcodesForDistance"
        SqlCommand.CommandType = CommandType.StoredProcedure
        SqlCommand.Connection = SqlConnection
        SqlCommand.Parameters.Add(New SqlClient.SqlParameter("Zipcodes in range", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 30, 0, "%", DataRowVersion.Current, "%"))
        SqlDataReader = SqlCommand.ExecuteReader()

        SQLXMLReader = SqlCommand.ExecuteXmlReader()
        While (SQLXMLReader.Read)
            Page.Response.Write(SQLXMLReader.ReadOuterXml())
        End While

Here is the procedure code that I am calling.

ALTER Procedure GetZipcodesForDistance(
     @OriginalZipCode nVarChar(7),
     @Distance Float)
AS
SET NOCOUNT ON

-- Declare some variables that we will need.
Declare @Longitude Float,
        @Latitude Float,
        @MinLongitude Float,
        @MaxLongitude Float,
        @MinLatitude Float,
        @MaxLatitude Float

-- Get the lat/long for the given zip
Select @Longitude = Longitude_West,
       @Latitude = Latitude_North
From   zipcode
Where  ZipcodeID = @OriginalZipCode

-- Calculate the Max Lat/Long
Select @MaxLongitude = dbo.LongitudePlusDistance(@Longitude, @Latitude, @Distance),
       @MaxLatitude = dbo.LatitudePlusDistance(@Latitude, @Distance)

-- Calculate the min lat/long
Select @MinLatitude = 2 * @Latitude - @MaxLatitude,
       @MinLongitude = 2 * @Longitude - @MaxLongitude

-- The query to return all zips within a certain distance
Select ZipcodeID
From   zipcode
Where  Longitude_West Between @MinLongitude And @MaxLongitude
       And Latitude_North Between @MinLatitude And @MaxLatitude
       And dbo.CalculateDistance(@Latitude, @Longitude,  Latitude_North, Longitude_West) <= @Distance FOR XML AUTO
       
       RETURN

 

[markros]

Before checking your SP, something is wrong her

SqlCommand.Parameters.Add(New SqlClient.SqlParameter("Zipcodes in range", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 30, 0, "%", DataRowVersion.Current, "%"))

Can you add your two parameters separately?
I use Parameters.AddWithValue syntax as the simpliest one.

 

[cmsbuffet]

I don't see two parameters, I thought I added only one. Where is the second one? It's a code of an article slightly modified by me. Thanks.

 

[markros]

You stored procedure expects two parameters. Why do you add only one then? Add them both.

 

[markros]

Also your second parameter is not specified as OUT in your SP definition.

 

[cmsbuffet]

I will try to debug this code line by line. Most of the errors should have been fixed when I wrote the code, but they weren't.

 SqlCommand.CommandText = "GetZipcodesForDistance"

Is a reference to a non-shared member that requires an object reference.


In this code I try to add parameters, but the line says that the line is not referenced.

Here is modified code, basically, the same as it was before.

 Dim SQLXMLReader As XmlReader

        SqlCommand.CommandText = "GetZipcodesForDistance"
        SqlCommand.CommandType = CommandType.StoredProcedure
        SqlCommand.Connection = SqlConnection
        SqlCommand.Parameters.AddWithValue(New SqlClient.SqlParameter("Zipcodes in range", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 30, 0, "%", DataRowVersion.Current, "%"))
        SqlCommand.Parameters.AddWithValue(New SqlClient.SqlParameter("Radius distance range", SqlDbType.VarChar, 100, ParameterDirection.Output, False, 30, 0, "%", DataRowVersion.Current, "%"))

        SqlDataReader = SqlCommand.ExecuteReader()

        SQLXMLReader = SqlCommand.ExecuteXmlReader()
        While (SQLXMLReader.Read)
            Page.Response.Write(SQLXMLReader.ReadOuterXml())
        End While

Actually, in my SP I don't see where even on of parameters is specified as out. On which line it should be specified?

 

[cmsbuffet]

Every single line of this code gives an error - reference to a non-shared member requires an object reference.

 

[jbenson001]

This is not a sql programming issue and should be posted in the ASP.NET forum

 

[markros]

SqlCommand.Parameters.AddWithValue('@OriginalZipCode', myValue)
SqlCommand.Parameters.AddWithValue('@Distance',myValueToGet)
SqlCommand.Parameters('@Distance').Direction = ParameterDirection.Output

In your SP add OUT here

ALTER Procedure GetZipcodesForDistance(
@OriginalZipCode nVarChar(7),
@Distance Float OUT)

Also it's better to use DECIMAL type instead of FLOAT.

I wrote this from the top of my head, so it may still be incorrect.

If it is, I suggest you to check this forum

http://forums.asp.net/54.aspx

The question like this is asked about 5 times each day.

 

[gmmastros]

markros,

There should not be any output parameters. Basically.... you pass in a center zip code, and a radius. You get a list of zipcodes that are within the specified distance.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cmsbuffet]

Imports System.Xml
Imports System.Xml.XmlReader

Dim SQLXMLReader As XmlReader

        SqlCommand.CommandText = "GetZipcodesForDistance"
        SqlCommand.CommandType = CommandType.StoredProcedure
        SqlCommand.Connection = New SqlConnection("Data Source=ANASTASIA-PC\SQLEXPRESS;Initial Catalog=ZipCodeAndDistance;Integrated Security=True;Pooling=False")
        SqlCommand.Parameters.AddWithValue('@OriginalZipCode', myValue)
        SqlCommand.Parameters.AddWithValue('@Distance',myValueToGet)

        SqlDataReader = SqlCommand.ExecuteReader()

        SQLXMLReader = SqlCommand.ExecuteXmlReader()
        While (SQLXMLReader.Read)
            Page.Response.Write(SQLXMLReader.ReadOuterXml())
        End While

still gives out those errors.

gmmastros, what would you recommend instead of the line

SqlCommand.Parameters('@Distance').Direction = ParameterDirection.Output

? You understood correctly that the output should be a bunch of zipcodes within the given distance.

 

[markros]

George is right, I didn't pay a close attention. Leave both parameters without OUT clause and don't specify ParameterDirection at all (it's input by default).

 

[gmmastros]

I understand what you are trying to do, but I don't know the best way to do it. Like jbenson001 suggests... this is not a SQL issue, but an asp.net issue. Posting in the asp.net forum would be your best bet. Alternatively... I'm pretty sure markros knows asp.net pretty well. He/she may be able to help.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Nope, as I said, I just like to pretend to know everything But google knows everything, so this is the guy I usually rely on

 

[cmsbuffet]

So there should not be an OUT clause after @OriginalZipCode and @Distance?

Also what myvalue and myvaluetoget could be? What do they represent?

 

[MDXer]

If your using ASP.NET 2008 your best and easiest way would be to use LINQ to SQL.

 

[cmsbuffet]

And could any one explain how to reference variables properly here?

 

[markros]

myValue and myOtherValue should be taken from the controls you're getting the value from, say, textboxes.

so, it would be in VB.NET

Me.txtZipCode.Value

and

Me.txtDistance.Value

(use your names of controls).

You don't need OUT in your SP, sorry for the confusion.

 

[markros]

BTW, seems like the same topic is discussed here

http://forum.lessthandot.com/viewtopic.php?f=17&t=4659

 

[cmsbuffet]

I have the SQL part behind that is discussed in the last link you posted. At least, I think so.

But what is

reference to a non-shared member requires an object reference

error?

http://msdn.microsoft.com/en-us/library/zwwhc0d0(VS.80).aspx

I could not relate to the msdn explanation in the above link.