Solved

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

Posted on 2010-08-17
8
294 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:AidenA
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33455732
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
 

Author Comment

by:AidenA
ID: 33455787
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
 
LVL 4

Accepted Solution

by:
justin-clarke earned 400 total points
ID: 33455916
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
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33455926
Think that should be OK off the top of my head.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AidenA
ID: 33456174
yeah seemed to work ok thanks.

so just one last thing then... what is cnnRead = .ExecuteReader() used for then in the above code?
0
 
LVL 18

Assisted Solution

by:John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou earned 100 total points
ID: 33456938
hi
for this question read this helpfull article http://blogs.x2line.com/al/archive/2007/05/01/3049.aspx
0
 

Author Comment

by:AidenA
ID: 33457090
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
 
LVL 4

Expert Comment

by:justin-clarke
ID: 33464205
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now