Remove previous data of database then insert new data to database from textfile.

[anamada]

Hi!

I just need a little help from you guys

What I want to happen is that the button from my system will remove the previous data from database and then insert new data to database from text file. Im done with inserting new data. The thing is that I dont how to control or where to put the Delete Command from my code.

Hoping for fast guide!

Thanks in advance!

Here is my code

Imports System.Data.SqlClient
Imports System.IO
Imports System.Configuration
Imports Microsoft.VisualBasic.FileIO
Imports System.Collections.ObjectModel


Public Class ComProcMain

    Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim files As ReadOnlyCollection(Of String)
        files = My.Computer.FileSystem.GetFiles("C:\Documents and Settings\My Documents\TextFilesToTransfer", FileIO.SearchOption.SearchAllSubDirectories, "*.txt")
        For Each NewData As String In files
            Dim Filename As String = NewData
            Dim Fields As String()
            Dim Delimiter As String = "|"
            Dim connectionString As String

            Using parser As New TextFieldParser(filename)
                parser.SetDelimiters(delimiter)
                While Not parser.EndOfData
                    ' Read in the fields for the current line
                    fields = parser.ReadFields()
                    ' Add code here to use data in fields variable.





                    'INSERT STATEMENT HERE
                    connectionString = "******"
                    Using connection As New SqlConnection(connectionString)

                        'Dim removeData As New SqlCommand("DELETE FROM TBL_SDAMATSCHED", connection)
                        'removeData.Connection.Open()
                        'removeData.ExecuteNonQuery()
                        'removeData.Connection.Close()

                        Dim insertData As New SqlCommand("INSERT INTO TBL_SDAMATSCHED (BRCODE,TA_NO, ACCT_NAME, INVESTMENT, TAX_STATUS, TFEE_RATE, VALUE_DATE, MAT_DATE, ORIG_TERM,ORIG_INTRATE,PRINCIPAL,INTEREST,NET_INTEREST,NMV,REFTSNO,SEQ_NO) " _
                                                                    & "VALUES (@BRCODE,@TA_NO, @ACCT_NAME, @INVESTMENT, @TAX_STATUS, @TFEE_RATE, @VALUE_DATE, @MAT_DATE, @ORIG_TERM,@ORIG_INTRATE,@PRINCIPAL,@INTEREST,@NET_INTEREST,@NMV,@REFTSNO,@SEQ_NO)", _
                                connection)
                        insertData.Parameters.AddWithValue("@BRCODE", Fields(0).Trim.ToString)
                        insertData.Parameters.AddWithValue("@TA_NO", Fields(1).Trim.ToString)
                        insertData.Parameters.AddWithValue("@ACCT_NAME", Fields(2).Trim.ToString)
                        insertData.Parameters.AddWithValue("@INVESTMENT", Fields(3).Trim.ToString)
                        insertData.Parameters.AddWithValue("@TAX_STATUS", Fields(4).Trim.ToString)
                        insertData.Parameters.AddWithValue("@TFEE_RATE", Fields(5).Trim.ToString)
                        insertData.Parameters.AddWithValue("@VALUE_DATE", Fields(6).Trim.ToString)
                        insertData.Parameters.AddWithValue("@MAT_DATE", Fields(7).Trim.ToString)
                        insertData.Parameters.AddWithValue("@ORIG_TERM", Fields(8).Trim.ToString)
                        insertData.Parameters.AddWithValue("@ORIG_INTRATE", Fields(9).Trim.ToString)
                        insertData.Parameters.AddWithValue("@PRINCIPAL", Fields(10).Trim.ToString)
                        insertData.Parameters.AddWithValue("@INTEREST", Fields(11).Trim.ToString)
                        insertData.Parameters.AddWithValue("@NET_INTEREST", Fields(12).Trim.ToString)
                        insertData.Parameters.AddWithValue("@NMV", Fields(13).Trim.ToString)
                        insertData.Parameters.AddWithValue("@REFTSNO", Fields(14).Trim.ToString)
                        insertData.Parameters.AddWithValue("@SEQ_NO", Fields(15).Trim.ToString)
                        insertData.Connection.Open()
                        insertData.ExecuteNonQuery()
                        insertData.Connection.Close()


                    End Using
                End While
            End Using
        Next

        MsgBox("Data iserted!!")
    End Sub

End Class

Im using MS VB 2010 Express and MS SQL SERVER 2008 R2.

[jmcilhinney]

You will put the delete command wherever you want to delete the data. Presumably that will be just before you insert the new data. You obviously know how to create a command and execute it, so just do so with a DELETE statement. With no WHERE clause, it will delete every record.

[anamada]

Ah. Yeah! It's working now. I figured it out now but i have another question. Do you know how to delete a text file inside a specific folder using vb.net?

[jmcilhinney]

Ah. Yeah! It's working now. I figured it out now but i have another question. Do you know how to delete a text file inside a specific folder using vb.net?

I do, but that has nothing to do with the topic of this thread so it doesn't belong in this thread. Please keep each thread to a single topic and each topic to a single thread. Also, is there any particular reason you didn't just search the web for delete file vb.net?