[RESOLVED] converting from text file to DB file

[ecotec8286]

So i'm having a hard time converting a working version of my project that received all of its data from a text file, to now receive data from a database. Both have all the same information, but I can't figure it out.

Heres the working version from the text file:

Private Sub LoadTranList()
        Dim tranFile As System.IO.StreamReader 'file of transaction data
        Dim tranFileName As String = "fatrans.txt"
        Dim nextTran As transaction
        Dim holdrecord As String
        Dim tid As String 'client id from file record
        Dim tcode As String 'stock code from file record
        Dim tdate As Date 'tran date from file record
        Dim ttype As String 'buy or sell from file record
        Dim tshares As Integer 'number of shares
        Dim tprice As Double 'share price

        'check to see if transaction file exists
        If Not System.IO.File.Exists(tranFileName) Then
            Throw New Exception("cannot find transaction file")
            Exit Sub
        End If

        'open the transaction file to start at first record
        tranFile = New System.IO.StreamReader(tranFileName)

        'read thru tran file, add client trans to list
        Do While tranFile.Peek <> -1
            holdrecord = tranFile.ReadLine() 'get next record
            'break record into fields
            tid = holdrecord.Substring(0, 3)
            tcode = holdrecord.Substring(4, 5).Trim
            tdate = CDate(holdrecord.Substring(10, 10))
            ttype = holdrecord.Substring(21, 1)
            tshares = CInt(holdrecord.Substring(23, 5))
            tprice = CDbl(holdrecord.Substring(29, 7))

            If CInt(tid) = m_ID Then 'create client tran, add to list
                nextTran = New transaction(m_ID, tcode, tdate, ttype, tshares, tprice)
                m_tranlist.Add(nextTran)
            End If
        Loop
    End Sub

Now I can fill/populate a table with all of the information using:

Dim selectstring As String
        Dim connectionstring As String
        Dim transadapter As OleDb.OleDbDataAdapter
        Dim transtable As DataTable

        connectionstring = "Provider = Microsoft.JET.OLEDB.4.0;" & "Data Source = barestern2003.mdb"

        'create select statement
        selectstring = "select * from transact"

        'create adapter
        transadapter = New OleDb.OleDbDataAdapter(selectstring, connectionstring)

        'create the stock data table
        transtable = New DataTable

        transadapter.Fill(transtable) 'Fill the data table

So I must be having trouble with the Do Loop, because I don't know how to read each individual line, like a text file. I know how to change all of the .Substring's in the text file to the .Item's from the DB, so that's not a problem Any suggestion? Thanks for any help!

[jmcilhinney]

Once you've got a DataTable you can loop through its Rows collection and get the data from the DataRows. Follow the Database FAQ link in my signature for more information.

[ecotec8286]

For counter = 0 To Table.Rows.Count - 1
            tid = holdrecord.Substring(0, 3)
            tcode = holdrecord.Substring(4, 5).Trim
            tdate = CDate(holdrecord.Substring(10, 10))
            ttype = holdrecord.Substring(21, 1)
            tshares = CInt(holdrecord.Substring(23, 5))
            tprice = CDbl(holdrecord.Substring(29, 7))

            If CInt(tid) = m_ID Then 'create client tran, add to list
                nextTran = New transaction(m_ID, tcode, tdate, ttype, tshares, tprice)
                m_tranlist.Add(nextTran)
            End If
Next

Would this be correct then?

[jmcilhinney]

Did it work when you tried it?

Follow the Database FAQ link in my signature for more information.

[ecotec8286]

nope

[jmcilhinney]

Then it's not correct and you should do as I've advised twice already.

Follow the Database FAQ link in my signature for more information.

[ecotec8286]

sorry, dumb mistake by me, i figured it out! thanks for your help.

[jmcilhinney]

I had a look at the thread that I know has the answer you need and I will accept that that answer may not be immediately obvious, although it is there for those who are prepared to make the effort. With that in mind, here is an example of how you loop through a collection that is specifically relevant to this topic:

For Each row As DataRow In myDataTable
    'Use row here
Next

That gives you access to the current row inside the loop. You can then get the field values by name from that row, an example of which you can you can find by doing as I have suggested.