Importing contents of text file into MySQL using VB.Net

Question

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

Sorry, I forgot. I already edited it and I added the code already. — user3678904
– user3678904, Commented May 27, 2014 at 8:27
I think ReadAllLines would be better for this than ReadAllText. Then you simply loop through the array returned by ReadAllLines and process each row one a a time. — Tim
– Tim, Commented May 27, 2014 at 8:30
When I try to use ReadAllLines, it says 'Value of type '1-dimensional array of String' cannot be converted to 'String' — user3678904
– user3678904, Commented May 27, 2014 at 8:35

Tim Schmelter · Accepted Answer · 2014-05-27 08:39:20Z

1

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

answered May 27, 2014 at 8:39

Tim Schmelter

462k79 gold badges720 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3678904 Over a year ago

Thank you a lot sir!!! Without you, I wouldn't have solved this problem. You are such an awesome person.

user3678904 Over a year ago

By the way sir, if it isn't too much, can I ask one more question?

Tim Schmelter Over a year ago

You're welcome. But you should consider to change the types of your columns from varchar to f.e datetime for the Date column and int for the Id column. Edit Yes, of course.

user3678904 Over a year ago

Ok sir will do. My question is, how could I avoid data duplication? Because I'm planning to use insert this code on a button and whenever the button is pressed, the database automatically updates, because the text file that will be used will frequently be updated as well. How to avoid duplication?

Tim Schmelter Over a year ago

@user3678904: you should either check before the insert (in the same query) if that record already exists or create a unique index in the database and catch the specific exception which is raised. The former can be less efficient and creates a race condition on heavy load(many paralel inserts). I know how to catch the constraint-exception with SQL-Server (just catch SqlException and check its Number` property), but i don't know how to do it on MySql. Here's the pseudo-sql for the former approach: if not exists (select * from tbl_entrance_swipe s where s.Loc = @Loc and ..) INSERT INTO ..

|

Collectives™ on Stack Overflow

Importing contents of text file into MySQL using VB.Net

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related