Accessing a Database

Accessing a Database

• Pirringer

  
  How did you create the DataSet Did you use code or the Data Source Configuration Wizard
  
  

• twiztar

  In VB.Net you'll get a dataset which is a bit more than the VB6 record set. A dataset can contain one or many datatables which can be thought of somewhat similar to a recordset.

  Getting the data depends on what you want to do with it. If its being used for user interface directly then you may want to user the data binding features and bind the controls to this datasource.

  However if you want to user it a little bit more programatically. Say you are looping through the records picking certain records based upon selction criteria and using in some calculation or doing different actions depending upon the record data then you'll need to do a bit of iterating through the datatable records.

  In this example DS is a dataset which contains a single datatable that can have one or many rows. This will loop around each row and set a string for the contents of fields called Name and City and write them out.

  For Each row As DataRow In Ds.Tables(0).Rows
  strName = row.Item("Name").ToString
  strCity = row.Item("City").ToString
  Console.WriteLine(strName & " " & strCity)
  Next

  The for each is a nice way to iterate round as you dont have to be concerned with setting specific index values or doing movenext command. If you want to know how many rows there are you can use DS.Tables(0).rows.count

  Intellisense is great for checking out whats available in the dataset.

  
  

• ScottyO

  Public MustInherit Class BindingManagerBase

  Inherits System.Object

  Member of: System.Windows.Forms

  Summary:

  Manages all System.Windows.Forms.Binding objects that are bound to the same data source and data member. This class is abstract.

  
  
  

• velt

  A dataset is just an object. You can create it with code or using the wizards within the IDE.

  code:

  dim ds as new dataset

  ds = GetData()

  where GetData() would have the code to connect to your DB which would return you some result set. If you are just returning on table, then I suggest using a DataTable instead of a Dataset. Because in Spotty's example above, he/she is basically using a DataTable anyways.

  code for that is:

  dim dt as new DataTable

  
  

• Martin Flores

  Paul

  Thank you for the reply.

  I created the DataSet using the DAta Source Config Wizard.

  I wrote a Diet Analysis program for use in UK schools. It is a VB6 program which extracted nutrition data from an Access DB. The program allowed pupils to create a list of food and then, when the list was complete, the DB was accessed and the attributes for each food extracted. This analysed the total nutrition content.

  The selection of each individual food on the list was linked to a data reference line in the DB so I could go direct to that line of data. In VB6 I placed the DB on a Form and accessed the data using SQL using such statements as:
  Data.Recordset.MoveLast()
  Data.Recordset.AbsolutePosition = n - 1
  value = Data.Recordset.Fields(column_ref).value

  BUT under VB 2005 I cannot use the "SQL Recordset" approach but I would like to follow a similar approach if possible. Given the data reference line in the DB what I need to do is determine a method that I can access the DB and get the data.

  The contributors have been very helpful in the guidance given but considering the above application which of the methods would be the most suitable.

  I should be able to work out how to cycle through the DB eg. for searches) and how to sort the data but my real requirement is to go direct to known lines in the DB and get the data from known columns.

  Ideally I would like a method which is similar to the VB6 approach which worked well. Ideally I would like to keep the data open rather than opening and closing the database during each time it is accessed. There are numerous times that the DB is accessed so I am trying to avoid opening and closing the database which was the alternative VB6 method.

  Should it be a DataSet or a DataTable approach

  I would appreciate some pointers in the most appropriate direction.

  Thanks

  
  
  

• chuck620

  Many thanks to all for the helpful replies. I am trying to get my head around the new approach in VB 2005.

  I wrote a Diet Analysis program for use in UK schools. It is a VB6 program which extracted nutrition data from an Access DB. The program allowed pupils to create a list of food and then, when the list was complete, the DB was accessed and the attributes for each food extracted. This analysed the total nutrition content.

  The selection of each individual food on the list was linked to a data reference line in the DB so I could go direct to that line of data. In VB6 I placed the DB on a Form and accessed the data using SQL using such statements as:
  Data.Recordset.MoveLast()
  Data.Recordset.AbsolutePosition = n - 1
  value = Data.Recordset.Fields(column_ref).value

  BUT under VB 2005 I cannot use the "SQL Recordset" approach but I would like to follow a similar approach if possible. Given the data reference line in the DB what I need to do is determine a method that I can access the DB and get the data.

  The contributors have been very helpful in the guidance given but considering the above application which of the methods would be the most suitable.

  I should be able to work out how to cycle through the DB eg. for searches) and how to sort the data but my real requirement is to go direct to known lines in the DB and get the data from known columns.

  Ideally I would like a method which is similar to the VB6 approach which worked well. Ideally I would like to keep the data open rather than opening and closing the database during each time it is accessed. There are numerous times that the DB is accessed so I am trying to avoid opening and closing the database which was the alternative VB6 method.

  Should it be a DataSet or a DataTable approach

  I would appreciate some pointers in the most appropriate direction.

  Thanks to the contributors and thanks for reading this far.

  Many thanks
  KeiV, Winchester

  
  
  
• Accessing a Database