Time zone based on Zip code (not state)

Question

Ok people, I am here with what i assume to be a simple logic problem.

I'll show you my code first (at least all i think you need) and then explain the problem.

Function GetTimeZone(ByVal state As String) As Integer

    Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
    Dim dbConnection As OleDbConnection = New OleDbConnection(TaxConnStr)
    Try



        Dim queryString As String
        queryString = "SELECT Top 1 Zips.State, Zips.TimeZone "
        queryString += "FROM Zips "
        queryString += "WHERE (((Zips.State)='" & state & "'));"

        Dim dbCommand As OleDbCommand = New OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As DataSet = New DataSet
        dataAdapter.Fill(dataSet)
        If dataSet.Tables(0).Rows.Count >= 1 Then
            Return dataSet.Tables(0).Rows(0).Item("TimeZone")
        Else
            Return 0
        End If

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        myLogger.Log(ex.Message)
    Finally
        dbConnection.Close()
    End Try
end Function

Ok, what i am trying to do is find out the time zone based on not the State (how this code gets it) but by the zip code. This is obviously b/c some states (KY, TN, OK, etc) are divided by their time zone.

So i would ultimately like this function to look at the zip code...find out which time zone that zip code is in...and then return the time zone.

If it's not apparent. The table this information is pulling from is named "Zips". It consists of several columns ranging from zip, city, state, county, areacode, and timezone. The timezone is column in the table only has values such as "5,6,7,8,etc" to correspond to each timezone. 5 = Eastern, 6 = Central, so on..

Thanks for any guidance.

Answer 1

Looks pretty straight forward, unless I'm not understanding something. You'd first need to change your method signature to take a zip code:

Function GetTimeZone(ByVal zipcode As Integer) As Integer

Then, you'd have to change your query to search by that zip code:

queryString = "SELECT Top 1 Zips.TimeZone "
queryString += "FROM Zips "
queryString += "WHERE (((Zips.Zip)=" & zipcode & "));"

Everything else should work as is.