Solved

Transition between datatable and dataview and back again

Posted on 2004-10-19
4
226 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

914 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

21 Experts available now in Live!

Get 1:1 Help Now