CODEWITHSUNDEEP
mysqlvb.net
user3678904
user3678904

Reputation: 33

Importing contents of text file into MySQL using VB.Net

Good day everyone. I am trying to create a program that will transfer all of the contents of a text file into a database. So far, my code works, but my code only inserts the first line of the text file into the database. What should I add to solve the problem? I am noob at programming sorry.

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim filename As String = "C:\Users\user\desktop\swipe.txt"
        Dim Query As String
        Dim data As String = System.IO.File.ReadAllText(filename)
        Dim Loc, _date, time, temp, id As String
        Loc = data.Substring(0, 3)
        _date = data.Substring(3, 8)
        time = data.Substring(11, 4)
        temp = data.Substring(15, 3)
        id = data.Substring(18, 3)
        Query = "INSERT INTO tbl_entrance_swipe VALUES ('" + Loc + "','" + _date + "','" + time + "','" + temp + "','" + id + "')"
        Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=cph;User ID=root;Password=;")
        Try
            con.Open()
            Dim sql As MySqlCommand = New MySqlCommand(Query, con)
            sql.ExecuteNonQuery()
            MsgBox("Record is Successfully Inserted")
            con.Close()
        Catch ex As Exception
            con.Close()
            MsgBox("Record is not Inserted" + ex.Message)
        End Try
    End Sub
End Class

Upvotes: 2

Views: 2777

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

You are using File.ReadAllText which reads the complete text of the file. You want to read line by line, therefore you can use File.ReadLines(deferred executed) or File.ReadAllLines(reads all into a String()).

You should also use sql-parameters to prevent sql-injection and incorrect implicit type conversions or localization issues. Finally, use the Using statement to ensure that all unmanaged resources are disposed (f.e. the connection gets closed even on error):

Dim filename As String = "C:\Users\user\desktop\swipe.txt"
Dim allLines As String() = File.ReadAllLines(filename)
Dim query As String = "INSERT INTO tbl_entrance_swipe VALUES (@Loc, @Date, @Time, @Temp, @Id)"

Using con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=cph;User ID=root;Password=;")
    con.Open()
    Using cmd As New MySql.Data.MySqlClient.MySqlCommand(query, con)
        For Each line In allLines
            Dim loc, dt, time, temp, id As String
            loc = line.Substring(0, 3)
            dt = line.Substring(3, 8)
            time = line.Substring(11, 4)
            temp = line.Substring(15, 3)
            id = line.Substring(18, 3)
            cmd.Parameters.Clear()
            Dim pLoc As New MySql.Data.MySqlClient.MySqlParameter("@Loc", MySqlDbType.VarChar)
            pLoc.Value = loc
            cmd.Parameters.Add(pLoc)
            Dim pDate As New MySql.Data.MySqlClient.MySqlParameter("@Date", MySqlDbType.VarChar)
            pDate.Value = dt
            cmd.Parameters.Add(pDate)
            Dim pTime As New MySql.Data.MySqlClient.MySqlParameter("@Time", MySqlDbType.VarChar)
            pTime.Value = time
            cmd.Parameters.Add(pTime)
            Dim pTemp As New MySql.Data.MySqlClient.MySqlParameter("@Temp", MySqlDbType.VarChar)
            pTemp.Value = temp
            cmd.Parameters.Add(pTemp)
            Dim pId As New MySql.Data.MySqlClient.MySqlParameter("@Id", MySqlDbType.VarChar)
            pId.Value = id
            cmd.Parameters.Add(pId)

            cmd.ExecuteNonQuery()
        Next
        MsgBox("All records were inserted successfully")
        con.Close()
    End Using
End Using

Upvotes: 1

Related Questions