VB Newbie - Database Application

VB Newbie - Database Application

• dan6546546

  I have tried looking at many of the data access examples provided on MSDN.  The examples are definetly not for the faint of heart.  It seems that making available the .NET components has further complicated matters for those that are novice programmers wanting to get in the swing with VB.  It also looks like most sample programs / snippets and help were written by C programmers.  It would be helpful if other VB programmers wrote these.  Maybe I'm wrong, but this is just my feeling after playing with VB 2005 Express over the past couple weeks.  Hopefully in future releases, Microsoft can put VB back in the right direction.  Being an easy to manster language with easier to understand support pages.  Definetly 2005 has many cool features that make this a step in the right direction.

  So, back to my main question (hopefully I didn't offend any readers on this forum.  That wasn't my intent). 

  I went and bought a couple of Database books for VB before the Holiday break, and I'm still scratching my head on how to code my logic correctly.

  Here is a snippet that I wrote to perform my distance calculation.

  '----------------------------------------------------------------------------

  'Haversine Formula for calculating distances between lat/long coordinates

  'Start and Ending positions converted to radians

  'Lat and Lon are stored in the DB as decimal degrees

  'deg/57.29577951 converts numbers to radians

  Dim lat1 As Double = TextBox1.Text / 57.29577951

  Dim lon1 As Double = TextBox2.Text / 57.29577951

  Dim lat2 As Double = TextBox3.Text / 57.29577951

  Dim lon2 As Double = TextBox4.Text / 57.29577951

  Dim dlon, dlat As Double

  Dim A As Double

  Dim C As Double

  Dim r As Double

  Dim pi = Math.Atan2(1, 1) * 4

  dlon = lon2 - lon1

  dlat = lat2 - lat1

   

  a = (Math.Sin(dlat / 2)) ^ 2 + Math.Cos(lat1) * Math.Cos(lat2) * (Math.Sin(dlon / 2)) ^ 2

   

  c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a))

   

  'R (Earth Radius) = 3956.0 mi = 3437.7 nm = 6367.0 km

  'STATUTE MILES

  r = 3956.0

  'NAUTICAL

  'r = 3437.7

  'KILOMETERS

  'r = 6367.0

   

  'Show the distance between the two points.

  Label11.Text = (r * C)

  --------------------------------------------------------------

  That seemed to be simple enough in VB.  But now I need to know how to read through my database, extract my Lat and Longitude coordinates, and use my calculation against each record to determine wether the distance between my two points is within a 100 miles of each other (my formula can easily be changed to reflect this).

  I would like my results to show up in a data grid.

  If anyone can provide some insight, I would apprecaite it.

  Thanks,

  Matt

  
  

• microuser

  hi,

  my friend i'm beginner like you but i just want try to help i notice things in your code and i don't understand it till now like for example you said

  foxmcf wrote:

  I have a MDB with two tables.  The City Table contains the following columns: ZipCode, CityName, State, Latitude, Longitude The FCC table contains a list of FM Antennas across the United States along with the Latitude and Longitude of each. I want my VB form to show a grid with a user input box that allows them to type in their ZipCode.  The app would then show the users current City and State, and then display a list of FM Stations within the DB Grid controll from a 100 mile radius from their City.

  i don't see any relation between the 2 tables that you wrote till now to retrieve particular data from the other table

  also i see both of  latitude and longitude are duplicated in both of your table and that could cause confusion in your database when you design a database don't repeat the same field in 2 tables just one table and to connect both with a relation

  also in the part of your code that you posted you didn't convert the textbox.text to double for not causing trouble to you , its not obligation but recomended

  foxmcf wrote:

  Dim lat1 As Double = TextBox1.Text / 57.29577951

   

  i mean like that

  Dim lat1 As Double = Double.parse(TextBox1.Text) / 57.29577951

   

  regarding about connection to the database i prefer if you added a dataset to your form and to use the dataset instead of the database but anyway this was my old way to connect to  microsoft access database i assume that you will just connect the city table to retrieve the longitude and latitude based on the user intery in the zipcode textbox first you import the

   

  imports system.data

  imports system.data.oledb

   

  and in the sub that capture if the user entered the zipcode in the text box you add this code

   

  Dim DBConn as oledbconnection
  Dim DBcomm as oledbcommand
  Dim DBReader as oledbdatareader
  Dim sqlStr as String

   

  DbConn = new oledbconnection("provider=microsoft.jet.oledb.4.0;Data Source="mydatabase.mdb")
  DBConn.open()

  sqlstr = "Select * from citytable where zipcode ='" & zipcodetextbox.text &"'"
  DBComm= new oledbcommand(sqlStr, dbconn)
  DBReader = DBComm.executereader()

   

   if DBreader.Read()
  latitudetextbox.text =DBreader("latitude")

  longitudetextbox.text = dbreader("longitude")
   End if

   DBReader.Close()
   DBconn.Close()

   

  this connection was for microsoft access i don't think the connection to sqlserver will be much different from that you will just change the "oledb" in  connection and  command to "sql" and change the provider of hte connection for more information about that you can review this

  http://msdn.microsoft.com/vbasic/reference/data/default.aspx pull=/library/en-us/dnsql90/html/mandataaccess.asp#mandataac_topic5

   

  i hope that was helpfull

  
  
  

• sbailey

  Thanks for your reply shak,

  You are correct.  There is no real relationship  between the two tables.  The ZipCode table is used to find the users current location, and I've loaded two variables with the users lat and lon coordinates.  I will then use those two variables as the users starting location.

  I created a public function CalculateDistance(startlat, startlon, FCClat, FCClon) based on the code above that I use to calculate the distance between the two points.  The function returns a value in miles.

  I like your first idea of using a dataset rather than connecting directly to the DB and I'm trying to figure out how to parse through each row of my FCCListDataGridView object.

  My FCCListDataGridView object is loaded with all my records.  My code to extract the Lat and Lon coordinates from the FCC list needs to look something like this (I'm guessing),... 

  For Eeach datarow in FccLisstDataGridView

       If CalculateDistance(startlat, startlon, FCClat, FCClon) >=95 or CalculateDistance

       (startlat, startlon, FCClat, FCClon) <=105 THEN

            Add this row to a new dataset

       End IF

  Next

   

  More specifically, I need to know how to read the values from my datagrid view (Latitude and Longitude) so I can pass these values to my function for calculation.  If the value retured from my function is between 95 and 105 miles, I need to add that row from the FCCListDataFridView object to another dataset, so I can show the appropriate records to the user.

  I also need to know how to format my For Each statement to read through each row correctly. 

  I now have two books on VB 2005 Express edition, and both have sections on dealing with datagrids and databases, but none have examples for what I'm trying to do.

  Thanks in advance for any more help I can get!

  Matt

   

   

   

  
  

• chris can surf

  Thanks again for the reply shak,

  After many hours of playing with examples from MSDN, and two different VB 2005 books (Visual Basic 2005 Express Edition for Dummies by Richard Mansfield is a very good book by the way... but the Author definetly has a corn cob up his you know what when it comes to Microsoft and what they have done to VB over the years) I know have a fully functional application.

  I figured out how to create a dataset on the fly, and use this to temporarily store my records and display them in a DataGridView.

  The FCC table has Lat and Lon coordinates, but you can't use a simple SQL statement to select the records.  I probably could have created a function within my Access MDB, but I'm sure that's a whole new topic.  I had to read each record, take the Lat and Lon coordinates and pass them through my function that calculates distance between the Start and End coordinates.  If they were within a user selectable range, I stored those records into my temporary dataset.  Once I read every record, I passed the dataset to my DataGrid, and viola! 

  I apprecaite your time and willingness to help.  I took a couple of your suggestions.  Like I said, I'm a newbie, and this is going to take some getting used to.  I'm sure that there are serveral instances in my program where I could refine my code to make it more streamlined; and I plan to keep playing around with it.  I learned several valuable lessons and I'm starting to like this a lot more.

  
  

• ROK15955

  do a google for TIGER data from the dept of census.

  Hey fox. . . have you considered a GIS system

  
  
  

• kim aldis

  that database you have w/ the zipcode, cityname, state, lattitue, longitude....does it have every zip code in there
  i could use that table if that is so :)
  
  thanks
  
  

• Emin

  neat!

  Well again, you will probably want to employ the 'boxing' strategy I outlined. It will certainly save some clock cycles!

  
  
  

• Ted69

  And if anyone is interested, I can make my source code available.

  Matt

  
  

• 5783sguitar

  The tiger line data is quite extensive and probably the most complete.  They also let you download the source code, but it's definetly not for the faint of heart.  There are several websites out there that let you geocode for free.  I don't remember where I picked up my set of data, but it was a free service I found on Google, and they use tigerline data in conjunction.

  I needed a small portable solution that users can download to find the FM transmitters within a radius of their area.  It's purpose is for individuals that 'listen' to falling meteors.

  In it's simplest application, a user tunes a FM receiver to a local unused FM band.  But for it to work correctly, they need to make sure that there is a station broadcasting on this frequency within a ~100 mile radius from their location.  Since this station is far away, the user won't hear the broadcast, but rather static.

  When a falling meteor enters the atmosphere, it ionizes the air, and temporarily amplifies the signal coming from the remote FM Transmitter.  Most FM Radio astronomers use a computer to monitor the PINGS that a falling meteor creates.

  This application is just a little utility to help users find FM Transmitters.  It will be easy to update since I can provide an automatic download of the latest Zip Code and FCC database to their computer.

  I'm also working on a web solution for this as well.  Seems that VB 2005 EE can help me with this endeavour.

   

   

   

  
  

• wgkwvl

  hey fox. . .

  I did something similar when i worked for Nextel.

  Yes you are right, probably need to move the ffunction to the database. . . but this won't work with access. Access functions are only callable inside of access apps as far as I know.

  You could go with SQL Express but that is for a different day.

  What I did was this. Given a point on the surface of the earth, I calculated Top-left and bottom-right points to define the 'box', then used these points to select from the table. minimizing the number of times I had to calculate the distance.

  psuedo sql:

  select * from fcctable where lat between TopLeft.Lat and BottomRight.Lat and long between TopLeft.Long and BottomRight.Long

  then run those through the Distance function

  make sense

  
  
  

• RobAus

  hi,

  look my friend usualy the controls in the windows form are used to display data from dataset regardless its combobox ,datagridview, textbox or what ever, all that just for displaying data and capture the event or entery not to deal with data

  i'll tell you now what i understood from your example(i'm very bad in geography and math by the way)

  1)you want to make a form contain a combobox that display all the usa zipcodes and the user to chose his location from this combobox(or you want to make a textbox the user type the code himself)

  2) according to the user entery or selection you want to retrieve 2 associated values (lat, long) from your city table for this zipcode

  3) you will make some calculation for both of those values and appoint (maxlongitude , minlongitude) and (maxlatitude, minlatitude) for the selected zipcode

  4) you want to go to your fcc table and select every row where (longitude < maxlongitude, lognitude > minlongitude) and (latitude <maxlatitude, latitude > minlatutitude)

  5) you want to display the fcctable filtered rows in a datagridview

  if what i understood is right i don't think you will need a database in first place Xml file will do that for u, and also you can make it a vb Asp.net website it will be better than windows form

  just use databases for updated, relational, and big amount of data this type of data is a static data

  you can also make your dataset readxml file

  anyway plz see if what i understood is right or wrong

  best regards

  
  
  

• smhaig

  Have you tried the information at:

  http://msdn2.microsoft.com/en-US/library/ms184649.aspx

    E10T

  
  
  

• Louis Phillipee

  Okay, let me ask a different question... :)

  Can someone give me the Author/Book for 2005 Express that deals with Database programming; one that you feel would benifit me in my endeavour above

  Thanks again, and Happy Holidays!

  Matt

  
  

• Maarten van Stam

  I can't send you my database, but I posted a download of the CSV on my website that you can download it from.  Yeah - it has all that stuff in there.

  http://www.phoxcite.com/foxfiles/

  Enjoy.

  
  
• VB Newbie - Database Application