By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,899 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,899 IT Pros & Developers. It's quick & easy.

Time zone based on Zip code (not state)

maylortaylor
P: 72
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.

Expand|Select|Wrap|Line Numbers
  1. Function GetTimeZone(ByVal state As String) As Integer
  2.         Dim TaxConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ConfigurationManager.AppSettings("Database")
  3.         Dim dbConnection As OleDbConnection = New OleDbConnection(TaxConnStr)
  4.         Try
  5.  
  6.  
  7.  
  8.             Dim queryString As String
  9.             queryString = "SELECT Top 1 Zips.State, Zips.TimeZone "
  10.             queryString += "FROM Zips "
  11.             queryString += "WHERE (((Zips.State)='" & state & "'));"
  12.  
  13.             Dim dbCommand As OleDbCommand = New OleDbCommand
  14.             dbCommand.CommandText = queryString
  15.             dbCommand.Connection = dbConnection
  16.  
  17.             Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter
  18.             dataAdapter.SelectCommand = dbCommand
  19.             Dim dataSet As DataSet = New DataSet
  20.             dataAdapter.Fill(dataSet)
  21.             If dataSet.Tables(0).Rows.Count >= 1 Then
  22.                 Return dataSet.Tables(0).Rows(0).Item("TimeZone")
  23.             Else
  24.                 Return 0
  25.             End If
  26.  
  27.         Catch ex As Exception
  28.             Console.WriteLine(ex.Message)
  29.             myLogger.Log(ex.Message)
  30.         Finally
  31.             dbConnection.Close()
  32.         End Try
  33.     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.
Mar 14 '13 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,359
I'm not quite sure what your question is. Did you not try changing the field it uses from state to zip?
Mar 14 '13 #2

maylortaylor
P: 72
Found the answer else where.

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

Expand|Select|Wrap|Line Numbers
  1. Function GetTimeZone(ByVal zipcode As Integer As Integer
Then, you'd have to change your query to search by that zip code:

Expand|Select|Wrap|Line Numbers
  1. queryString = "SELECT Top 1 Zips.TimeZone "
  2. queryString += "FROM Zips "
  3. queryString += "WHERE (((Zips.Zip)=" & zipcode & "));"Everything else should work as is.
  4.  
Mar 22 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.