Solved

Transition between datatable and dataview and back again

Posted on 2004-10-19
4
225 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
Comment Utility
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
Comment Utility
Anyone?  Adding points.
0
 

Author Comment

by:jacobymatt
Comment Utility
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
Comment Utility
Question answered by asker or dialog valuable.
Closed, 75 points refunded.
ee_ai_construct (replacement part #xm34)
Community Support Admin
0

Featured Post

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Text in tooltip bound field bridview 3 29
Merging PDFs with VB.NET 10 30
Hide Tab Page 3 18
Closing all open child forms. 4 22
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

8 Experts available now in Live!

Get 1:1 Help Now