How to perform ADO.NET connection to MS Access database, place into dataset and manipulate

Hi I've been unwittingly using ADO without realising there was a difference between that and ADO.NET.

Anyway, I just want to try and switch to ADO.NET but i'm not really sure how to do it. I've gotten as far as below... made the connection to the database and everything went fine there. What do I do from here? I guess i need to put the data into a dataset and start working with it... how do i go about this? If I have a int column called 'Int1' and a string column called 'string1' for example, how do i go about looping through, extracting data etc...

Now, I've seen a second piece of code about this which i put at the end below, which seems to show how to create a dataset... although what confuses me here is that it seems to be creating it's own connection to the database? Some help please!
'Connection to database
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & "'" & Server.MapPath("~/App_Data/CTC Database.mdb") & "'"
Dim cnnRead As OleDbDataReader
 
      strSQL = "SELECT * FROM([CTC Table])"

With cnn
         .Connection = New OleDbConnection(strConnection)
         .Connection.Open()
         .CommandText = strSQL
         cnnRead = .ExecuteReader()
End With

'Second piece of code i've seen to create a dataset
Dim da As New OleDbDataAdapter(strSQL, strConnection)
Dim ds As New DataSet()
da.Fill(ds, "Customers")

Open in new window

AidenAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

justin-clarkeCommented:
If you use the above code you can loop through the dataset like so....


If (ds.Tables(0).Rows.Count > 0) Then

   For Each row As DataRow In ds.Tables(0).Rows

      'inside here you can access all the "rows"
      Dim strInt1 As String = row("Int1").ToString()

      Dim strString1 As String = row("String1").ToString()

   Next

End If

Open in new window

0
AidenAAuthor Commented:
Hi, thanks,

but as in my question... i'm just wondering also, how to get from first piece of code to second piece of code. What should i be using here... this datareader or OleDbDataAdapter? can you just link it together for me as i'm having trouble figuring it out...
0
justin-clarkeCommented:
Sorry I see what you mean.
Dim cn As New System.Data.OleDb.OleDbConnection
Dim cm As New System.Data.OleDb.OleDbCommand
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & "'" & Server.MapPath("~/App_Data/CTC Database.mdb") & "'"

cn.ConnectionString = strConnection
cm.Connection = cn
cn.Open()
				
Dim strSQL As String = "SELECT * FROM tblTable"		

Dim da As New OleDbDataAdapter(strSQL, strConnection)
Dim ds As New DataSet()
da.Fill(ds)


'loop through the rows here


cn.Close()

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

justin-clarkeCommented:
Think that should be OK off the top of my head.
0
AidenAAuthor Commented:
yeah seemed to work ok thanks.

so just one last thing then... what is cnnRead = .ExecuteReader() used for then in the above code?
0
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
hi
for this question read this helpfull article http://blogs.x2line.com/al/archive/2007/05/01/3049.aspx
0
AidenAAuthor Commented:
ok thanks, so i should use in certain situations. Can't get the dataview to work though. Below is what i have... i put into a class (is that the right way to go about it?)

but doesn't work cmd.ExecuteReader throws an exception because it says 'Object reference not set to an instance of an object.' How do i create this details view properly?

'In one of my webpages where i want to create a connection

Dim objCC As New CreateConnection(strConnection, strSQL)
objCC.CreateDataView("Ref No")

'class to create connection
Public Class CreateConnection

   Public ds As New DataSet()
   Public cnnADO As OleDbConnection
   Public cmd As OleDbCommand
   Public dt As DataTable

   Public Sub New(ByVal strConnection As String, ByVal strSQL As String)

      cnnADO = New OleDbConnection(strConnection)
      cmd = New OleDbCommand(strConnection, cnnADO)
      cmd.CommandText = strSQL

      cnnADO.Open()
      Dim da As New OleDbDataAdapter(strSQL, strConnection)
      da.Fill(ds)
      
   End Sub
   Public Sub CreateDataView(ByVal strColumn)
      dt = New DataTable()
      dt = ds.Tables("CTC Table")
      dt.Load(cmd.ExecuteReader())
      Dim dv As DataView = New DataView(dt)
      dv.Sort = strColumn
   End Sub
End Class

Open in new window

0
justin-clarkeCommented:
Hi

I think you are mixing two ways of "filling" your data table.

If you take out the line:

dt.Load(cmd.ExecuteReader())

It should be OK.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.