I am trying to create a program which reads data from a text file and insert the data in to a SQL 2005 table, but unfortunately it only reads the first line and insert correctly but not the rest of the lines on the text file
The text file looks like this
John, Doe, 40, 12/04/1968
Micke, Gulm, 39, 07/08/1970
Tomy, Salo, 29, 13/03/1980
The SQL 2005 DB name “USERSDAT”
Table name: anvinfo
Sub GetIt1() Dim i As Long = 0 Dim sr As StreamReader = New StreamReader("C:\DEV\ Test.txt") Dim line As String = sr.ReadLine() Dim dbConn As SqlConnection = New SqlConnection("Data Source=devcomp7834;Initial Catalog= USERSDAT;Integrated Security=True") Dim dbCmd As SqlCommand = New SqlCommand() dbCmd.Connection = dbConn Dim wholeFile As String = sr.ReadToEnd() Do Dim fields() As String = line.Split(",") dbCmd.CommandText = "INSERT INTO dbo. anvinfo(fnamn, enamn, alder, fodd)VALUES('" & fields(0) & "','" & fields(1) & "', '" & fields(2) & "', " & fields(3) & " )" dbConn.Open() dbCmd.ExecuteNonQuery() i = i + 1 line = sr.ReadLine() Loop While Not line = String.Empty dbConn.Close() End Sub
Please if posiable help to sort out this issue as I need read the entire text file which in reality will be about 5000-10000 lines and insert them in to the SQL DB line by line.
Thanks in advance.