jacobymatt
asked on
Transition between datatable and dataview and back again
The program I have built opens up an access database, fills a dataset from it, and then iterated over the rows and colums and builds a string from the values in order to convert it to a comma delimited text file. I built the program without the use of a dataview because there was no need for one but now I need to sort the table I fill from access before I go through the rows and build the string. So to sum up my code I have:
Dim dbConn As New OleDbConnection
Dim ds As New DataSet
Public dbAdapt As New OleDbDataAdapter
dbConn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta source=" & txtFileName.Text ' I give it the file name so dont worry
Dim myCommand As New OleDbCommand
myCommand.Connection = dbConn
myCommand.CommandText = "SELECT * From UAB"
dbAdapt.SelectCommand = myCommand
dbAdapt.Fill(ds, "UAB")
Private Sub Process_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Process.Click
'OleDbDataAdapter2.Fill(Ds UAB1)
Dim strData As String
Dim strTRdata As String
Dim Row As DataRow
Dim dvRow As DataRowView
Dim icol As Integer
Dim Item As String
Dim H As String
Dim TR As String
Dim POnum As String
Dim lineNum As Integer
Dim DtUAB As DataTable = ds.Tables("UAB")
''''''' This is the part I just added ''''''
Dim dvUAB As DataView = DtUAB.DefaultView
dvUAB.Sort = "F33, F15, F16, F49"
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '''''''''' '''''''''' '''''''''' ''''''''
strTRdata = ""
strData = ""
POnum = ""
For Each Row In DtUAB.Rows
icol = 0
If Not Row.Item(icol) Is DBNull.Value Then
Item = Row.Item(icol)
Else
Item = ""
End If
'If POnum <> Row.Item(1) Then
For icol = 0 To 13
If Not Row.Item(icol) Is DBNull.Value Then
Item = Row.Item(icol)
Else
Item = ""
End If
If Item = "H" Then
If POnum <> Row.Item(1) Then
If H = "yes" Then
strData = strData & vbCrLf & Item
Else
strData = strData & Item
H = "yes"
End If
POnum = Row.Item(1)
End If
ElseIf Item <> "H" Then
strData = strData & "|" & Item
POnum = Row.Item(1)
End If
POnum = Row.Item(1)
Next
The main For loop continues on to have several other nested for loops that grab the rest of the column values but the logic is the same. I have 2 questions: is the sort I am attempting to use correct, meaning can I sort against the column names in access like "F33" or do I have to do it a different way. And then once I get them sorted in my dataview, the rest of my code doesnt work against that because it is programmed around a datatable. Is there a way to fill the datatable with the dataview. The point is to get the table sorted before I iterate over the rows and build my string. Surely I don't have to redo all my logic to work with the dataview instead of the datatable. Suggestions?
Dim dbConn As New OleDbConnection
Dim ds As New DataSet
Public dbAdapt As New OleDbDataAdapter
dbConn.ConnectionString = "Provider=Microsoft.Jet.OL
Dim myCommand As New OleDbCommand
myCommand.Connection = dbConn
myCommand.CommandText = "SELECT * From UAB"
dbAdapt.SelectCommand = myCommand
dbAdapt.Fill(ds, "UAB")
Private Sub Process_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Process.Click
'OleDbDataAdapter2.Fill(Ds
Dim strData As String
Dim strTRdata As String
Dim Row As DataRow
Dim dvRow As DataRowView
Dim icol As Integer
Dim Item As String
Dim H As String
Dim TR As String
Dim POnum As String
Dim lineNum As Integer
Dim DtUAB As DataTable = ds.Tables("UAB")
''''''' This is the part I just added ''''''
Dim dvUAB As DataView = DtUAB.DefaultView
dvUAB.Sort = "F33, F15, F16, F49"
''''''''''''''''''''''''''
strTRdata = ""
strData = ""
POnum = ""
For Each Row In DtUAB.Rows
icol = 0
If Not Row.Item(icol) Is DBNull.Value Then
Item = Row.Item(icol)
Else
Item = ""
End If
'If POnum <> Row.Item(1) Then
For icol = 0 To 13
If Not Row.Item(icol) Is DBNull.Value Then
Item = Row.Item(icol)
Else
Item = ""
End If
If Item = "H" Then
If POnum <> Row.Item(1) Then
If H = "yes" Then
strData = strData & vbCrLf & Item
Else
strData = strData & Item
H = "yes"
End If
POnum = Row.Item(1)
End If
ElseIf Item <> "H" Then
strData = strData & "|" & Item
POnum = Row.Item(1)
End If
POnum = Row.Item(1)
Next
The main For loop continues on to have several other nested for loops that grab the rest of the column values but the logic is the same. I have 2 questions: is the sort I am attempting to use correct, meaning can I sort against the column names in access like "F33" or do I have to do it a different way. And then once I get them sorted in my dataview, the rest of my code doesnt work against that because it is programmed around a datatable. Is there a way to fill the datatable with the dataview. The point is to get the table sorted before I iterate over the rows and build my string. Surely I don't have to redo all my logic to work with the dataview instead of the datatable. Suggestions?
ASKER
Anyone? Adding points.
ASKER
well anyway I ended up altering my code to fit the dataview because I could not find a way to transfer a dataview's contents back to the original datatable, it's slightly different:
For Each dvRow In dvUAB
icol = 0
If Not dvRow.Item(icol) Is DBNull.Value Then
Item = dvRow.Item(icol)
Else
Item = ""
End If
If dvRow.Item(1) <> POnum Then
POnum = dvRow.Item(1)
For icol = 0 To 13
If Not dvRow.Item(icol) Is DBNull.Value Then
Item = dvRow.Item(icol)
Else
Item = ""
End If
If Item = "H" Then
strData = strData & vbCrLf & Item
Else
strData = strData & "|" & Item
End If
Next
End If
For Each dvRow In dvUAB
icol = 0
If Not dvRow.Item(icol) Is DBNull.Value Then
Item = dvRow.Item(icol)
Else
Item = ""
End If
If dvRow.Item(1) <> POnum Then
POnum = dvRow.Item(1)
For icol = 0 To 13
If Not dvRow.Item(icol) Is DBNull.Value Then
Item = dvRow.Item(icol)
Else
Item = ""
End If
If Item = "H" Then
strData = strData & vbCrLf & Item
Else
strData = strData & "|" & Item
End If
Next
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER