CSV Bulk insert and Delete

[teddymeu]

Hi Guys, since I've done nothing but ask questions these last few weeks to get my first application up and running I thought it was about time to share the wealth and help out a newbie like me since you all did the same, thanks for all that replied to me.

This code will save a csv file to a dir, you can then use this to bulk insert the information to a specific table in your DB, this is done with asp.net vb and sql, I'm using an mdf for this. I also gave admins the ability to delete the info in there before they actually import the csv.

<script runat="server">    
    Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        'Save the uploaded file to an "Uploads" directory
        ' that already exists in the file system of the 
        ' currently executing ASP.NET application.
        Dim saveDir As String = "\Data\"
 
        ' Get the physical file system path for the currently
        ' executing application.
        Dim appPath As String = Request.PhysicalApplicationPath
 
        ' Before attempting to save the file, verify
        ' that the FileUpload control contains a file.
        If (FileUpload1.HasFile) Then
            Dim savePath As String = appPath + saveDir + FileUpload1.FileName
 
            ' Call the SaveAs method to save the 
            ' uploaded file to the specified path.
            ' Will overwrite existing file of same name
            FileUpload1.SaveAs(savePath)
 
            ' Notify the user that the file was uploaded successfully.
            UploadStatusLabel.Text = "Your file was uploaded successfully."
 
        Else
            ' Notify the user that a file was not uploaded.
            UploadStatusLabel.Text = "You did not specify a file to upload."
        End If
 
    End Sub
 
    Sub DeleteAllButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim SQLConn As New System.Data.SqlClient.SqlConnection
        SQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PostOffice.mdf;Integrated Security=True;User Instance=True"
        Dim strInsert As String
        Dim cmdInsert As SqlCommand
        strInsert = "DELETE FROM LocalPostOffice WHERE(ID > 0)"
        cmdInsert = New SqlCommand(strInsert, SQLConn)
        SQLConn.Open()
        cmdInsert.ExecuteNonQuery()
        SQLConn.Close()
    End Sub
 
    Sub ImportButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim SQLConn As New System.Data.SqlClient.SqlConnection
        SQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PostOffice.mdf;Integrated Security=True;User Instance=True"
        Dim strInsert As String
        Dim cmdInsert As SqlCommand
        strInsert = "BULK INSERT [LocalPostOffice] FROM [c:\Inetpub\wwwroot\MerlinLocalPostOfficeApp\Data\csv.txt] WITH (FIELDTERMINATOR = ',')"
        cmdInsert = New SqlCommand(strInsert, SQLConn)
        SQLConn.Open()
        cmdInsert.ExecuteNonQuery()
        SQLConn.close()
    End Sub
 
  </script>

Hope this helps someone in the future.