Hi guys, I added a extra line of code to test for records in the database and if they exist to update them before inserting them instead. For some odd reason it's not working and it simply just inserts the data causing duplicates. Any suggestions?
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<script runat="server">
Sub btnUpload_OnClick(sender As Object, e As EventArgs)
Dim strPath As String = "csv/"
Dim dt As DataTable
If Me.fiUpload.HasFile Then
Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath,fiUpload.FileName)
'*** Insert to Database ***'
InsertToDatabase(dt)
End IF
End Sub
'*** Convert CSV to DataTable ***'
Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM " & strFilesName
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
Function InsertToDatabase(ByVal dt)
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer
strConnString = "Provider=SQLOLEDB;" & _
"Server=test-sql\testing;" & _
"Database=Test;"& _
"uid=test;" & _
"pwd=test!!@3242gfgf8143f5d;"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Loop Insert ***'
For i = 0 To dt.Rows.Count - 1
Try
'EXTRA NEW LINE OF CODE TO UPDATE RECORDS
Dim updateCmd As String = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = '@empFullName'"
strSQL = "INSERT INTO MARRhrtime (empFullName,sickTime,VacationTime) " & _
"VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
Catch err As Exception
Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"
End Try
Next
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Function
</script>
