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

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

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.

DhaestCommented:
>> dt.Load(cmd.ExecuteReader())

What are you trying here ?
I assume that the datatable has already been filled (dt = ds.Tables("CTC Table"))
0
AidenAAuthor Commented:
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
DhaestCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AidenAAuthor Commented:
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
AidenAAuthor Commented:
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
DhaestCommented:
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
DhaestCommented:
0
DhaestCommented:
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
AidenAAuthor Commented:
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

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
DhaestCommented:
You can use the dataview you created within your objcc-class
0
AidenAAuthor Commented:
ok, perfect, you've been a great help thanks...

Aiden
0
DhaestCommented:
Glad I could help you
0
AidenAAuthor Commented:
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
DhaestCommented:
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
AidenAAuthor Commented:
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
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.