Help with turning this sub into a function (Resolved)

[FastEddie]

I am trying to step up my learning of OOP and one of the things I am doing to get the hang of it is going through the process of compartmentalizing my long subs into smaller functions. I have been moderately successful at doing this but have had a lot of trouble and have, in many cases, resorting to trial and error (guesing) until I get it right. This morning I ran into a roadblock and I want to walk through this a step at a time so it that it will make more sense.

The first thing the IDE wants me to do is set the Object Clause for the Function. Is that where I should have iRadius as Int and strZip as Text?

Function GetRadius()
 
        iRadius = CInt(txtMiles.Text)
        Dim strZip As String = CStr(txtZip.Text)
 
        Dim conn As OleDbConnection = New OleDbConnection(strCon)
        Dim strSelect1 As String = ( _
            "SELECT top 1 latitude, longitude, zip FROM tblZipCode " & _
            "WHERE zip = @Zip")
        Dim cmd1 As OleDbCommand = New OleDbCommand(strSelect1, conn)
        Dim dtrZip As OleDbDataReader
 
        cmd1.Parameters.AddWithValue("@Zip", strZip)
 
        Try
            conn.Open()
            dtrZip = cmd1.ExecuteReader(CommandBehavior.SingleRow)
            If dtrZip.Read Then
                strLat1 = CStr(dtrZip("Latitude"))
                strLon1 = CStr(dtrZip("Longitude"))
                strZip = CStr(dtrZip("Zip"))
            End If
            dtrZip.Close()
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        Finally
            conn.Close()
        End Try
 
        Dim iStartLat As Double = CSng(strLat1)
        Dim iStartLon As Double = CSng(strLon1)
        Dim ZipId As String = CStr(strZip)
        Dim LatRange As Double = iRadius / ((6076 / 5280) * 60)
        Dim LonRange As Double = iRadius / (((System.Math.Cos(CDbl _
            (iStartLat * 3.141592653589 / 180)) * 6076) / 5280) * 60)
        Dim LowLatitude As Double = iStartLat - LatRange
        Dim HighLatitude As Double = iStartLat + LatRange
        Dim LowLongitude As Double = iStartLon - LonRange
        Dim HighLongitude As Double = iStartLon + LonRange
 
        Dim strSelect2 As String = _
            "Select Zip, City, State, AreaCode, " & _
            "Latitude, Longitude From tblZipCode " & _
            "Where Latitude <=" & HighLatitude & _
            "And Latitude >=" & LowLatitude & _
            "And Longitude <=" & HighLongitude & _
            "And Longitude >=" & LowLongitude
 
        Dim Cmd2 As New OleDbDataAdapter(strSelect2, conn)
        Dim ds As New DataSet
        Cmd2.Fill(ds, "ZipCodes")
        dgZipCodes.DataSource = ds.Tables("ZipCodes")
 
    End Function

[mendhak]

What is the function's return value? I'm not sure why you need an object clause in there, but you can pass arguments to this function by giving it an arguments list. For example,

txtMiles.Text

doesn't need to be in the function, it can be passed as an argument.

[FastEddie]

I am going to be sending the Zip Code and a Radius from the form to the function. The function will return a DataSet with the City, State and Zip (the Select statement near the end of this code block).

I think it is looking for something like this:

Function GetRadius() As String

But since I am going to end up sending a dataset I don't know if a string is the correct object.

[mendhak]

So if it returns a dataset,

Function GetRadius() As DataSet


The last line of your function's code should be

Return ds


Do not bind your datagrid to the dataset in the function itself. Bind it to the dataset RETURNED by your function.

[FastEddie]

Next these two variables have to become Public Variables. This is how I would normally handle doing this. Is that an OK way of doing it?

Public iRadius = CInt(txtMiles.Text)
Public strZip As String = CStr(txtZip.Text)

It would seem there should be a way to do this right from the calling sub but if there is I cannot get the syntax correct. Is it possible do pass these values along to the function from here?

Dim strZip As String = (tbZip.Text)
Dim iRadius As Integer = (tbRadius.Text)
GetRadius()

[FastEddie]

I don't have this last part figured out and it probably doesn’t matter because I have something that works. I'll worry about "Best Practices" as I progress though my VB learning experience.

I did figure out the rest of the Returning of the Dataset part and will post the solution here hoping someone finds it useful.

The function will end with something like this

Dim Cmd2 As New OleDbDataAdapter(strSelect2, conn)
        Dim ds As New DataSet
        Cmd2.Fill(ds, "ZipCodes")
 
        Return ds
 
    End Function

And the Sub to fill the datagrid should look something like this

dgZipCodes.DataSource = GetZipRadius().Tables("ZipCodes")

[techgnome]

*whack!* That's the sound of me slapping you on the wrists.... do NOT use a function like that..... everytime you do it like that, it's a call to the db and the dataset is thrown away at the end of the line....
It would be MUCH better to set a DataSet variable to the function and then bind THAT to the grid:

Dim myDS as DataSet = GetZipRadius()
 
dgZipCodes.DataSource = myDS

-tg

[FastEddie]

Ow that hurt! But thanks for the tip anyway! Is the difference that in your version the dataset is held in memory for as long as the form is open?

How do I ensure the form opens with the table filled and not as an undelined link as it is doing now that I removed the ds.Tables("ZipCodes")

[techgnome]

To answer your first question, if it's a module level variable, then yes.

As for the other part... I usualy fill my datasets in the constructor of the form, right after the call to the InitializeComponents..... seems to work for me.

-tg

[wossname]

You should be using option strict and option explicit too. Stops you writing bad code.

[FastEddie]

How do I ensure the form opens with the table filled and not as an undelined link as it is doing now that I removed the ds.Tables("ZipCodes")

I should update this thread. I found the answer to my question. The key is to add one more line and that is the DataMember like so.

Dim myDS As DataSet = GetUE_ZipRadius(tbZip.Text, CInt(tbRadius.Text))
DataGrid1.DataSource = myDS
DataGrid1.DataMember = "ZipCodes"