Solved

Transition between datatable and dataview and back again

Posted on 2004-10-19
4
228 Views
Last Modified: 2010-04-23
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.OLEDB.4.0;Data 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(DsUAB1)
        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?
0
Comment
Question by:jacobymatt
  • 3
4 Comments
 

Author Comment

by:jacobymatt
ID: 12349467
okay....I got the sorting to work, and you can use the "F34" as a column name to sort by.  Now the only question is how do send the dataview back over to the datatable so I can use it throughout the rest of my code.  Please help.  
0
 

Author Comment

by:jacobymatt
ID: 12350198
Anyone?  Adding points.
0
 

Author Comment

by:jacobymatt
ID: 12415730
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
0
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 12416906
Question answered by asker or dialog valuable.
Closed, 75 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.Net - Get ColumnNames of SQL Table 4 15
"Emulate" TAB key when press Enter Key 3 46
Visual studio 2015 1 21
vb.net convert long time to mm:ss 23 12
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

860 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