Help with Loop and Insert into Database (Resolved)

[FastEddie]

I am trying to insert a record into a database from each line in my text file. So far all I have accomplished is to insert 1 record (the same record) for each line in the file. So if I had 10 lines of different people I am getting some thing like this (see below) instead of the 10 different people.

Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie
Fast Eddie

The code I have is

'***Subs
    Private Sub ReadFile()

        Dim oRead As StreamReader = _
            File.OpenText("C:\member.txt")

        Do While oRead.Peek >= 0
            Dim myLine As String = (oRead.ReadLine)
            Dim sAgent As String = myLine.Substring(0, 12)
            Dim sOffice As String = myLine.Substring(12, 12)
            Dim sName As String = myLine.Substring(24, 34)
            Dim sLName As String = myLine.Substring(58, 17)
            Dim sPhone As String = myLine.Substring(195, 12)
            Dim sExt As String = myLine.Substring(207, 4)
            Dim sFax As String = myLine.Substring(211, 12)
            Dim sEmail As String = myLine.Substring(207, 80)
            Dim sWeb As String = myLine.Substring(303, 80)
            Dim sDesig As String = myLine.Substring(386, 30)
            Dim sType As String = myLine.Substring(386, 30)
            Dim sLid As String = myLine.Substring(0, 12)

            InsertMember(sAgent, sOffice, sName, sLName, _
                sPhone, sExt, sFax, sEmail, sWeb, sDesig, _
                sType, sLid)
        Loop
        oRead.Close()
    End Sub

[jmcilhinney]

That code looks OK at a glance. It would be interesting to see your InsertMember method because if all you're inserting is "Fast Eddie" then you must not be using all the parameter values you're passing. Also, have you debugged to make sure that you're passing the correct data to InsertMember each time. If you haven't then you should do that immediately, because debugging is the first thing you should do. If the right data is being passed to InsertMember but the wrong data ends up in the database then the issue is obviously with InsertMember.

Also, it's very inefficient to insert one record at a time like that. Imaging if you had thousands of records and you were connecting, inserting and disconnecting every time. What you should be doing is populating a DataTable with all the data, then inserting it all in one go. Follow the Data Access link in my signature for an example.

[FastEddie]

jmcilhinney the datatable makes perfect sence and I have modified my code to do that. However I am still having a problem with the loop because this is only giving me the last record in the text file

' Loop Problem
    Private Sub ReadFile()
 
        Dim oRead As StreamReader = _
            File.OpenText("C:\member.txt")
 
        Do While oRead.Peek >= 0
 
            Dim myLine As String = (oRead.ReadLine)
            Dim sAgent As String = myLine.Substring(0, 12)
            Dim sOffice As String = myLine.Substring(12, 12)
            Dim sName As String = myLine.Substring(24, 34)
            Dim sLName As String = myLine.Substring(58, 17)
            Dim sPhone As String = myLine.Substring(195, 12)
            Dim sExt As String = myLine.Substring(207, 4)
            Dim sFax As String = myLine.Substring(211, 12)
            Dim sEmail As String = myLine.Substring(207, 80)
            Dim sWeb As String = myLine.Substring(303, 80)
            Dim sDesig As String = myLine.Substring(386, 30)
            Dim sType As String = myLine.Substring(386, 30)
            Dim sLid As String = myLine.Substring(0, 12)
 
 
            Dim myDT As New DataTable
            myDT.Columns.Add("name", GetType(String))
            myDT.Columns.Add("lname", GetType(String))
            myDT.Columns.Add("officeid", GetType(String))
            myDT.Columns.Add("phone", GetType(String))
            myDT.Columns.Add("fax", GetType(String))
            myDT.Columns.Add("email", GetType(String))
            myDT.Columns.Add("web", GetType(String))
            myDT.Columns.Add("designation", GetType(String))
            myDT.Columns.Add("type", GetType(String))
            myDT.Columns.Add("agentID", GetType(String))
            myDT.Columns.Add("lid", GetType(String))
 
            Dim myRow As DataRow = myDT.NewRow
            myRow("name") = sName
            myRow("lname") = sLName
            myRow("officeid") = sOffice
            myRow("phone") = sPhone
            myRow("fax") = sFax
            myRow("email") = sEmail
            myRow("web") = sWeb
            myRow("designation") = sDesig
            myRow("type") = sType
            myRow("agentid") = sAgent
            myRow("lid") = sLid
            myDT.Rows.Add(myRow)
 
            DataGridView1.DataSource = myDT
        Loop
 
        oRead.Close()
 
    End Sub

[techgnome]

1) Don't set the datasource until the end.... rather than each time through the loop
2) Put a breakpoint on line #7 above.... and step through it... see what it's doing.

-tg

[FastEddie]

Techgnome moving the datasource and rearanging where the columns and rows are set took care of the problem. Thank you.