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

 

[JoeAtWork]

The error means the object must be initialized, i.e. you must declare it as "New".

SqlCommand is the name of a class. It is not a shared class, therefore you must create an instance of the class, something like:

Dim mySqlCommand As SqlCommand
mySqlCommand = New SqlCommand

BTW, this is actually an ADO.NET question, not ASP.NET as some have suggested (although the good ASP.NET coders may know this too because they probably have to use databases in their websites).

It's important to understand the difference between a shared class and one where you must create an instance. I suggest you review these topics, otherwise you will run into this problem many times.

 

[cmsbuffet]

 mySqlCommand.Parameters.AddWithValue("@OriginalZipCode", TextBoxZipcode.Value)
        mySqlCommand.Parameters.AddWithValue("@Distance", distanceTextBox.Value)

Yet, pops another error

'Value' is not a member of 'System.Web.UI.WebControls.TextBox'.

My guess is that I need to find a place in the code such that the code will be available for both textboxes - TextBoxZipcode and distanceTextBox. But where can I find such place, if each has it's own little code assigned to each textbox. I could place it outside of both codes. But I am not sure if I interpereted the error correctly.

 

[cmsbuffet]

Also, I found another piece of code. Will it help in my case?

 Dim command As String
        command = "Select ZipcodeID from zipcode where ZipcodeID = @ZipcodeID"
        Dim cmd As SqlCommand
        cmd = New SqlCommand(command)


        cmd.Parameters.AddWithValue("@ZipcodeID", TextBoxZipcode.Text)

 

[gmmastros]

You could replace that with:

Dim command As String
command = "Select @ZipcodeID As ZipcodeID"
Dim cmd As SqlCommand
cmd = New SqlCommand(command)
cmd.Parameters.AddWithValue("@ZipcodeID", TextBoxZipcode.Text)

That would probably run faster, too.



-George

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

 

[cmsbuffet]

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

I would like to connect the code above with code below.
How do I do it?

Dim command As String
        command = "Select @ZipcodeID As ZipcodeID"
        Dim cmd As SqlCommand
        cmd = New SqlCommand(command)
        cmd.Parameters.AddWithValue("@ZipcodeID", TextBoxZipcode.Text)

 

[cmsbuffet]

How can I display the content of cmd. Do I use Equals or Contains? Also cmd.parameters.equals is not a string, for example, so what is the correct syntax to display the result of the query on the page?

Here is the code to which I want to add a page.response.write ( Here syntax of the result of the query ).

Thanks.

 Dim command As String
        command = "Select @ZipcodeID As ZipcodeID"
        Page.Response.Write(" " + command + " ")
        Dim cmd As SqlCommand
        cmd = New SqlCommand(command)
        Page.Response.Write(" " + command + " ")
       
        cmd.Parameters.AddWithValue("@ZipcodeID", TextBoxZipcode.Text)
        Page.Response.Write(" " + TextBoxZipcode.Text + " ")
page.response.write ( Here syntax of the result of the query )

 

[MDXer]

you can add a gridview and then bind the result set or you can run a loop and create a table out putting the results to each cell.


If you google asp.net gridview our display result set or move your question to the asp.net or vb.net forum.