ADO.NET Hell

ADO.NET Hell

• Muhammad_Afzaal

  Hi,
  
  Thanks for all of the help.  My original code had problems.  After updating the code it still didn't run becuase I had an error in the select query itself.  One suggestions is to use the data designers and drop the connection, adapter, dataset, etc. on the form.  I have a problem/questions with this.  If/when I decide to deploy this to another computer and my database is in a different location what do I do   Do I just modify the connection or is it more or less complicated   Also,  I used the data designer the other evening for this same problem.  I think made a change to my database structure and then the project would no longer run (don't remeber the exact error).  I am still a little leary of using some of the features that I don't understand or can't completely control.  I guess I'm a control freak or something.
  
  Richard
  

• Stephen Lepisto

  Hi,
  
  Yes, your quite right. oda.GPS.Fill(dsStops) is the right syntax for filling a dataset. But don't forget to open your connection when filling a dataset conn.Open().
  
  After filling a dataset you could access the data by the DataTable Object in the Dataset. eg.
  
  Dim dt As DataTable
  
  dt = dsStops.Tables("GPSLocations")
  MessageBox.Show(dt.Rows(1)("Cluster").ToString())
  
  
  Here's a code snippet to get you started...
  http://msdn.microsoft.com/library/default.asp url=/library/en-us/dv_vbCode/html/vbtskcodeexamplereadingsqldataintodataset.asp
  
  
  
  cheers,
  
  
  Paul June A. Domag
  
  

• Meriya

  I've looked at the code and made changes but I still get an error when I try to fill the dataset.  This is beginning to look a lot more complicated than it should be.  The error I am receiving is
  
  An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
  
  My code now looks like the following.  CreateConn returns an open connection to the Access database.  I greatly appreciate your help.  There has to be something else that I am not doing.
  
  
  
  
  

• BorisM

  Hi Richard,
  
  I'm glad you've got your code going.  Your latest version looks great.
  
  I personally agree that you're best off using the designer configured adapters.  The proof is in fewer lines of code!  VB 2005 really makes these best practices easier to find and do.  Hopefully you can check that out soon. 
  
  To address your concern, yes it's pretty easy to modify a connection post deployment.  This is a matter of saving your connection string to a setting - this gets saved to the app.config or web.config file, and that's a human editable XML file!  You could even design a simple Tools->Options file over the config.
  
  in VB 2002/2003
  - select your connection object and get properties
  - expand the (Dynamic Properties) node
  - select the Connection String property and configure it to use Dynamic properties. 
  * note that your string is now read from the app.config file
  
  in VB 2005
  - configure your adapters using the Data Source Window or Dataset Designer
  - Open the dataset.xsd file
  - Get properties on the table adapter, select the connection
  * note how your connection string is already automatically saved to settings.  This means it's accessible as a part of My.Settings and it's saved under the covers to app.config.
  
  hope that helps.
  
  Paul
  
  

• confusesc

  Hi,
  
  Quick question, are you using VB 2005 or VB 2003
  
  Reading, modifying and updating data is far simpler in VB 2005 using design-time features like TableAdapters (strongly typed data adapters) and the Data Source window.  How Do I Help has great topics on this, and I'd be glad to point you at specifics.
  
  In VB 2003 you can use command builder to build up Select, Insert, Update, and Delete commands objects to be used by your data adapter - see sample above.  However, VB 2003 does this work for you as well so you don't need to write all this code.  Do this by dragging and dropping an OleDbDataAdapter component from the toolbox onto your form or component class.  Then, right click on it and use the Configuration Wizard.  As a result, the designer will build all the command objects for you.  Then you can simply call odaGPS.Fill(dsStops) and odaGPS.Update(dsStops.GetChanges). 
  
  ** One very important note about connections.  DataAdapter objects manage opening, closing and pooling connections for you.  You are better off *not* to open connections explicitly before using the Adapter.  Instead, don't call Open, and the oda.GPS.Fill call will call Open for you internally.  One other note, if you ever do Open connections, please make sure you always call Close in the Finally block of a Try Catch Finally.  This way you never leak connections (top performance / scalability issue).  Opening connections really is only done in ADO.NET for the purpose of opening a DataReader (a forward only firehose of data). 
  
  hth,
  Paul
  

• megamacy

  Didn't post the code.  Here it is.
  
  

  Dim conn As New OleDb.OleDbConnection

  Dim selectCmd As New OleDb.OleDbCommand

  Dim odaGPS As OleDb.OleDbDataAdapter

  Dim dsStops As New DataSet

  Dim dtStops As New DataTable

  conn = CreateConnection()

  selectCmd.Connection = conn

  selectCmd.CommandText = "Select Time, Cluster From GPSLocations " + _

  "Where Speed < 1.0"

  selectCmd.Connection = conn

  odaGPS = New OleDb.OleDbDataAdapter(selectCmd)

   

  odaGPS.Fill(dsStops)

  
  

• saggett

  Hi,
  
  The SelectCommand property is for the automatic generation of Commands using the CommandBuilder object.
  Pass the selectCmd object to the constructor of OleDbDataAdapter and it should work fine.
  ------------------
  CODE SNIPPET
  ------------------
  Dim conn As New OleDb.OleDbConnection
  Dim selectCmd As New OleDb.OleDbCommand
  Dim odaGPS As OleDb.OleDbDataAdapter
  Dim dsStops As New DataSet
  
  conn = CreateConnection()
  selectCmd.CommandText = "Select Time, Cluster From GPSLocations " + _
  "Where Speed < 1.0"
  selectCmd.Connection = conn
  
  odaGPS = New OleDb.OleDbDataAdapter(selectCmd)
  
  odaGPS.Fill(dsStops)
  ------------------
  Regards,
  Vikram

  
  
  

• Philsky

  Hi,
  
  I worked on the code some more and finally convinced myself to use the data designer and command builder to build the commands.  I redid the entire in about 5 minutes where as I have spent countless hours trying to get the old code to work.  It has been a learning experience so not all is lost.  I am not having problems with my update.  The code is as follows:
  
  

  Sub ClusterStops()

  Dim dsStops As New DataSet

  Dim dtStops As New DataTable

  Dim stopInt As Int16

  Dim prevDate, curDate As Date

  Dim i As Int16

   

  'Fill the dataset with data

  odaCluster.Fill(dsStops)

  'Get and instance of the table to manipulate

  dtStops = dsStops.Tables(0)

  stopInt = 1

  'If there are rows in the table set the firs cluster to 1

  If dtStops.Rows.Count > 0 Then

  dtStops.Rows(0).Item("Cluster") = stopInt

  End If

  'Step through each row of the table and cluster the stops

  For i = 1 To dsStops.Tables(0).Rows.Count - 1

  prevDate = dtStops.Rows(i - 1).Item("ObsTime")

  curDate = dtStops.Rows(i).Item("Obstime")

  'if the differenc in time is greater than 10 seconds increment the counter

  If Abs(DateDiff(DateInterval.Second, prevDate, curDate)) > 10 Then

  stopInt += 1

  End If

  'Modify the value in the client side table

  dtStops.Rows(i).Item("Cluster") = stopInt

  Next i

  dtStops.AcceptChanges()

  odaCluster.Update(dsStops)

  End Sub
  
  The adapter and connection are on the form.  The code runs without error but when I look at the table nothing has been changed.  Every row in the local dataset should have been changed.  The Select query was easy.  The Update query is more difficult since I am not sure what to do about the variables.  Could you tell me where the "How Do I Help" is located   Is it on MSDN or hiding in the help files that came with VB.Net   Thank  you for your help.
  
  Richard

  
  
• ADO.NET Hell