AidenA
asked on
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.'
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(
the error occurs on the line dt.Load(cmd.ExecuteReader(
'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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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(strConnec tion, strSQL)
Dim dataView as New Dataview = objCC.CreateDataView("Ref No")
objCC.cnnADO.Close()
'in my webpage codebehind
strSQL = "SELECT * FROM([CTC Table])"
Dim objCC As New CreateConnection(strConnec
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use the dataview you created within your objcc-class
ASKER
ok, perfect, you've been a great help thanks...
Aiden
Aiden
Glad I could help you
ASKER
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?
i think it didn't work there again yeah? should we get a moderator to check what's going on there?
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 ....
Moderators, like the asker mentioned:
- close this questions by accepting my own solution but giving the points for other assisted solutions ....
ASKER
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)
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)
What are you trying here ?
I assume that the datatable has already been filled (dt = ds.Tables("CTC Table"))