dataset and procedure

[cmsbuffet]

I have a stored procedure

GetZipcodesForDistance( _
                Application("outputTextBox"), _
                 Application("radiusTextBox"))

I would like to assign the query result of the procedure GetZipcodesForDistance into a data set zipcodedistance. I have a for loop to display the rows of the data set.

For Each rowDistance In zipcodedistance.Rows
                If rowDistance(1) = Application("outputTextBox") Then
            Page.Response.Write(" " & rowDistance(1) & " ")
                    End if
Next

However, I cannot find a method to assign each row of the query to each row of data set. Any hints?


I am trying to call the procedure above and to populate DataSet with the results of the procedure query. Thanks.

 

[ArtieChoke]

First of all, how is GetZipcodesForDistance defined as a stored procedure? What database? Your definition looks like a subroutine.

To assign the results to a dataset, you need to execute the stored procedure in a connection/command object and return the value to the dataset. You don't individually assign each row. You bind the query to the dataset.

Please show all your database code.


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[jmeckley]

..connection and commmand defined

var table = new DataTable
table.Load(command.ExecuteReader());
return table;


Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[cmsbuffet]

Here is my code.

Stored Procedure

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

Functions

ALTER Function dbo.CalculateDistance( @Latitude1 Float, @Longitude1 Float, @Latitude2 Float, @Longitude2 Float )
Returns Float
AS
Begin
    Return Sqrt(Power(@Longitude2 - @Longitude1, 2) * Power(Cos((@Latitude1 + @Latitude2) / 114.591559026164646), 2) * 12391523883.7963953387686623629457364 + Power(@Latitude2 - @Latitude1,2) * 12346214104.563361) / 1609.344
End

ALTER Function dbo.LatitudePlusDistance(@StartLatitude Float, @Distance Float) Returns Float
As
Begin
    Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))
End

ALTER Function dbo.LongitudePlusDistance
    (@StartLatitude Float,
    @StartLongitude Float,
    @Distance Float)
Returns Float
AS
Begin
    Return (Select @StartLongitude + Sqrt(@Distance * @Distance / (4784.39411916406 * Cos(2 * @StartLatitude / 114.591559026165) * Cos(2 * @StartLatitude / 114.591559026165))))
End

 

[jbenson001]

Ok so you are getting back a result set from the SP. What is your questions? What do you want to do with the results in ASP.NET?

 

[cmsbuffet]

I want to create a table of zip codes which will be chosen from an existing table of zip codes. The client will need to enter a zip code and a distance in two text boxes, and the table, which I want to create, will show on aspx page all the zipcodes within this radius with a click of a button. Do you need to see my VB and .aspx code?

 

[ca8msm]

Just bind the results of your stored procedure to whatever data control you want to use (e.g. a GridView).

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[cmsbuffet]

I know how to bind grid view to sql data source in design view in .net. I am not sure how to bind stored procedure to grid view using VB or any other way.

 

[jbenson001]

That's what Google is for. You can find a lot of info on line.

 

[taree]

Hope this give you an idea how to bind with stored procedure

Dim BiddersData As OracleDataReader
Dim cmdBiddersData As OracleCommand = New OracleCommand()
With cmdBiddersData
.Connection = oOracleConn
.CommandText = "LettingInformation.BiddersInformation"
.CommandType = CommandType.StoredProcedure
.Parameters.Clear()
.Parameters.Add(New OracleParameter("p_letting", OracleType.VarChar)).Value = lettId
.Parameters.Add(New OracleParameter("p_contid", OracleType.VarChar)).Value = contId
.Parameters.Add(New OracleParameter("p_call", OracleType.VarChar)).Value = calId
.Parameters.Add(New OracleParameter("p_result", OracleType.Cursor)).Direction = ParameterDirection.Output
End With
BiddersData = cmdBiddersData.ExecuteReader()
gvvendors.DataSource = BiddersData
gvvendors.DataBind()
BiddersData.Close()

 

[cmsbuffet]

If I am using Transact-SQL, what can I use instead of the word "Oracle" in the example? Thanks.

 

[jbenson001]

SQLDataReader .. etc

 

[cmsbuffet]

http://www.aspnettutorials.com/tutorials/controls/data-table-vb.aspx

The code that I tried in the link above doesn't compile.

I am trying it out to learn how to create tables in VB connected with ASPX page.

The warnings are caused by functions that have As inside brackets instead of after brackets in functions' declarations.

And the Error - I don't understand. Can you help? I don't know much about Withevents. Thanks.
---------------------

Warning 2 Function 'AddDataToTable' doesn't return a value on all code paths. A null reference exception could occur at run time when the result is used.

Warning 1 Function without an 'As' clause; return type of Object assumed.

Error 3 Handles clause requires a WithEvents variable defined in the containing type or one of its base types.

 

[jmeckley]

the messages are pretty self explanatory.

Warning 2 means that you have either a loop, foreach, switch or logic block an one of the paths doesn't do anything.
examples

foreach(var x in ...)
{
   if(x != something) continue;
   return x;
}

it is possible that x will never equal something. to fix this...

foreach(var x in ...)
{
   if(x != something) continue;
   return x;
}
throw new ArgumentOutOfRangeException();
or
return new object(); //as default value

this is just one of the many ways a code path may not return a value.

Warning 2 means something like this is happening

function int get_number()
{
   return new object();
}

I didn't think vb still allowed this! you should enable strict/explicit types to disable the lazy types in vb. this will preform compile time checks which reduces runtime errors.

the error means you are trying to wire up an event handler without the proper syntax. being a c# dev, I'm not sure what the exact vb syntax is. fortunately the messages tell you exactly where the problem is. file and line #.

Jason Meckley
Programmer
Specialty Bakers, Inc.