I am trying to read from txt file and write to a database and getting pk error

Question

I have the reader and writer parts working but I keep getting a pk error if whats in the txt file is already in the database. How do I check the pk's in both files against one another?

Imports System.IO
Imports System.Data.SqlClient

Public Class Form1

Public AllEmp As New List(Of NEEmployee)

Private Sub btnRead_Click(sender As Object, e As EventArgs) Handles btnRead.Click
    getEmployeesFromFile()
    dgvEmployees.DataSource = AllEmp
End Sub

Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click


    For Each emp As NEEmployee In AllEmp
        InsertAnEmployee(emp)
    Next

End Sub

Private Sub getEmployeesFromFile()
    Dim reader As StreamReader
    Try
        reader = File.OpenText("employee.txt")
        While reader.EndOfStream = False
            Dim strEmp As String() = reader.ReadLine().Split(CChar("|"))
            Dim emp As New NEEmployee
            emp.EmpID = CInt(strEmp(0))
            emp.FirstName = strEmp(1)
            emp.LastName = strEmp(2)
            emp.Department = strEmp(3)
            emp.Shift = CInt(strEmp(4))
            emp.PayClass = strEmp(5)
            emp.CompanyLocation = strEmp(6)
            AllEmp.Add(emp)
        End While
        reader.Close()
    Catch ex As Exception
        Throw ex
    End Try
End Sub

Public Function InsertAnEmployee(ByVal Emp As NEEmployee) As Integer

    Dim Command As SqlCommand
    Dim Connection As SqlConnection
*emphasized text*
        Try

            ' instantiate a SQL connection and command
            Connection = New SqlConnection("Data Source=SAMJULIE-PC\SAMJULIE;Initial Catalog=NESgillis;Integrated Security=True;Pooling=False")
            Command = New SqlCommand

            ' define the type of command
            Command.CommandText = String.Format("INSERT INTO NEEmployee (EmpID, FName, LName, Department, Active, Shift, WageClass, CompanyLocation) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", _
                                                Emp.EmpID, Emp.FirstName, Emp.LastName, Emp.Department, Emp.Department, Emp.Shift, Emp.PayClass, Emp.CompanyLocation)
            Command.CommandType = CommandType.Text
            Command.Connection = Connection

            ' open connection and read data
            Connection.Open()

            InsertAnEmployee = Command.ExecuteNonQuery()

            ' close and clean up objects
            Connection.Close()
            Connection.Dispose()
            Command.Dispose()

        Catch ex As Exception
            Throw ex
        End Try
    End If
    Return InsertAnEmployee

End Function

End Class

Public Class NEEmployee
    Property EmpID As Integer
    Property FirstName As String
    Property LastName As String
    Property Department As String
    Property Shift As Integer
    Property PayClass As String
    Property CompanyLocation As String
End Class

score 1 · Accepted Answer · edited May 23 '17 at 12:21

If you're not worried about concurrency at all (i.e., there is only a single client running at a time performing this import), you can simply modify your Insert query to be something like:

Command.CommandText = String.Format("IF NOT EXISTS (SELECT * FROM NEEmployee WHERE EmpID={0}) INSERT INTO NEEmployee (EmpID, FName, LName, Department, Active, Shift, WageClass, CompanyLocation) VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')", _
                                            Emp.EmpID, Emp.FirstName, Emp.LastName, Emp.Department, Emp.Department, Emp.Shift, Emp.PayClass, Emp.CompanyLocation)

However, if there are multiple clients possibly importing in parallel, then you will still occasionally see the PK violation. See this thread for your options if that's the case: Only inserting a row if it's not already there

One other observation (though not directly related to your question): your SQL is prone to SQL injection attack, since you are using simple string substitution. See this thread for a simple example of using the SqlCommand Parameters object in VB.NET: How to use parameters "@" in an SQL command in VB

I am trying to read from txt file and write to a database and getting pk error

1 Answers1