Solved

creating a dataview in ado.net (connecting to ms access database)

Posted on 2010-08-18
16
285 Views
Last Modified: 2012-05-10
Hi, what is wrong with the code below?

I have a few lines of code in my codebehind where I call my CreateConnection class and create the object. You can see the class below. So, the connection works fine but dt.Load(cmd.ExecuteReader()) does not when i try and create the dataview by calling the objCC.CreateDataView("Ref No") function in my class

the error occurs on the line dt.Load(cmd.ExecuteReader()) which says that 'Object reference not set to an instance of an object.'

'in my webpage codebehind

strSQL = "SELECT * FROM([CTC Table])"

Dim objCC As New CreateConnection(strConnection, strSQL)

objCC.CreateDataView("Ref No") 'Does not work properly

objCC.cnnADO.Close()



'connection works fine but dt.Load(cmd.ExecuteReader()) does not

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)



      Connect(strConnection, strSQL)



   End Sub

   Private Sub Connect(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.RowFilter = ""

      dv.Sort = strColumn



   End Sub



End Class

Open in new window

0
Comment
Question by:AidenA
  • 8
  • 7
16 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33463090
>> dt.Load(cmd.ExecuteReader())

What are you trying here ?
I assume that the datatable has already been filled (dt = ds.Tables("CTC Table"))
0
 

Author Comment

by:AidenA
ID: 33463260
don't know what i'm doing to be honest! just looking at this for the first time so trying to get it working from code i've seen on internet.

If i remove that line, then the code fails on 'dv.Sort = strColumn' where the error is 'DataTable must be set prior to using DataView.'

So, i'm just guessing but it sounds like from that error the datatable has not been filled? I know the dataset is filled as i can access the values... just need to get it working beyond that point to get the dataview working
0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 500 total points
ID: 33463361
Are you sure that this dt = ds.Tables("CTC Table") gives you back a datatable ?

How is your datatable called ? "CTC Table" or just "CTC" ?
Public Sub CreateDataView(ByVal strColumn)



      dt = New DataTable()

      dt = ds.Tables("CTC Table")

 

      if not dt is nothing then 

          Dim dv As DataView = New DataView(dt)

         'dv.RowFilter = ""

         dv.Sort = strColumn

      else

           messagebox.show("Datatable 'ctc table' does not exist")

      end if

   End Sub

Open in new window

0
 

Author Comment

by:AidenA
ID: 33463381
yeah i just figured it out myself , used this line of code below

dt = ds.Tables(0)

i think "CTC Table" has nothing to do with the name of the table but actually the name of the dataset table.

well, you might be able to help me further anyway as i look at this, otherwise i'll just give a few points as your first post made me think a bit more
0
 

Author Comment

by:AidenA
ID: 33463400
actually, maybe you could save me some time and show me how to then iterate through a dataview to find the columns and rows i want and put into a variance, that sort of thing
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33463410
By the way: I see that you create a dataview, but you don't return it after you call the function. What are you going to do with it, perhaps you should change it from sub to function. Example below


'in my webpage codebehind
strSQL = "SELECT * FROM([CTC Table])"
Dim objCC As New CreateConnection(strConnection, strSQL)
Dim dataView as New Dataview = objCC.CreateDataView("Ref No")
objCC.cnnADO.Close()

Public Function CreateDataView(ByVal strColumn) as DataView



      dt = New DataTable()

      dt = ds.Tables(0)

      Dim dv As DataView = New DataView(dt)

      'dv.RowFilter = ""

      dv.Sort = strColumn



      return dv

End Function

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33463420
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 500 total points
ID: 33463439
Walking through a DataView
http://www.knowdotnet.com/articles/iteratingdataview.html

VB.NET DataRow Tips
http://dotnetperls.com/datarow-vbnet



For Each rowView As DataRowView in dataView 

    Dim row As DataRow = rowView.Row 



       ' Loop over ItemArray.

        For Each item As Object In row1.ItemArray

            ' Test the type of each element.

            If (TypeOf item Is Integer) Then

                Console.WriteLine("Integer")

            ElseIf (TypeOf item Is String) Then

                Console.WriteLine("String")

            ElseIf (TypeOf item Is DateTime) Then

                Console.WriteLine("DateTime")

            End If

        Next





Next 

Open in new window

0
 

Accepted Solution

by:
AidenA earned 0 total points
ID: 33463458
actually this is what i've done as an example, figured out how to iterate through it so it's working fine

does that seem an ok way to go about it, or should i create my own local version of the dataview and set it to the one in my class?
strSQL = "SELECT * FROM([CTC Table])"

Dim objCC As New CreateConnection(strConnection, strSQL)

objCC.CreateDataView("Ref No")



For Each oRow As Object In objCC.dv.Table.Rows

    Dim strInt1 As String = oRow.Item("Ref No").ToString()

    Dim strString1 As String = oRow.Item("Created By").ToString

Next



objCC.cnnADO.Close()

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33463464
You can use the dataview you created within your objcc-class
0
 

Author Comment

by:AidenA
ID: 33463484
ok, perfect, you've been a great help thanks...

Aiden
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33463491
Glad I could help you
0
 

Author Comment

by:AidenA
ID: 33463515
ah... when i try to close these questions by accepting my own solution but giving the points for other assisted solutions it doesn't seem to work?

i think it didn't work there again yeah? should we get a moderator to check what's going on there?
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33463554
Why delete this question ? I thought that I gave some usefull information (and I also mentioned before you said you found the solution the problem that probably was occuring - wrong tablename in the creation of your dataview).

Moderators, like the asker mentioned:
- close this questions by accepting my own solution but giving the points for other assisted solutions ....
0
 

Author Comment

by:AidenA
ID: 33463594
no don't want to delete question at all... just want to accept my own solution and then give you 500 points for one of your assisted solutions. (just for the sake of someone who may be looking at this question at a later date... then they will be able to find what the solution actually was more quickly). But each time i try to do this, it doesn't work.

I have sent a message to moderator, will sort out why i can't seem to do it anymore (used to be able to do it fine)
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 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

20 Experts available now in Live!

Get 1:1 Help Now