Visual Basic/Asp.net Upload-Import

Partial Class DataUpload
    Inherits System.Web.UI.Page


    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        pnlUpload.Visible = True
        pnlView.Visible = False
        pnlImport.Visible = False
    End Sub

    Protected Sub btnUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUploadFile.Click
        If FileUpload1.HasFile Then
            Try


                FileUpload1.SaveAs(Server.MapPath("~/import.xls"))
                lblUpload.Text = "File name: " & FileUpload1.PostedFile.FileName & "<br>" 

            Catch ex As Exception
                lblUpload.Text = "Error: " & ex.Message.ToString
            End Try
        Else
            lblUpload.Text = "Please select a file to upload."
        End If
    End Sub

    Protected Sub btnView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnView.Click
        pnlUpload.Visible = False
        pnlView.Visible = True
        pnlImport.Visible = False
        ' Create a new Adapter
        Dim objDataAdapter As New Data.OleDb.OleDbDataAdapter()

        ' retrieve the Select command for the Spreadsheet
        objDataAdapter.SelectCommand = ExcelConnection()
        ' Create a DataSet
        Dim objDataSet As New Data.DataSet()

        ' Populate the DataSet with the spreadsheet worksheet data

        objDataAdapter.Fill(objDataSet)

        ' Bind the data to the GridView
        GridView1.DataSource = objDataSet.Tables(0).DefaultView
        GridView1.DataBind()

    End Sub

    Protected Function ExcelConnection() As Data.OleDb.OleDbCommand

        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & Server.MapPath("Import.xls") & ";" & _
                 "Extended Properties=Excel 8.0;"

        ' create your excel connection object using the connection string
        Dim objXConn As New Data.OleDb.OleDbConnection(xConnStr)
        objXConn.Open()
        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
        ' in this case, the worksheet name is "Members" and is expressed as: [Members$]
        Dim objCommand As New Data.OleDb.OleDbCommand("SELECT * FROM [import$]", objXConn)
        Return objCommand
    End Function

    Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
        pnlUpload.Visible = False
        pnlView.Visible = False
        pnlImport.Visible = True

        lblImport.Text = "" 'reset to blank

        ' retrieve the Select Command for the worksheet data
        Dim objCommand As New Data.OleDb.OleDbCommand()
        objCommand = ExcelConnection()

        ' create a DataReader
        Dim reader As Data.OleDb.OleDbDataReader
        reader = objCommand.ExecuteReader()

        Dim Counter As Integer = 0

        While reader.Read()       ' set default values for loop
            Counter = Counter + 1

            'Dim FileNumber As String = Convert.ToString(reader("FileNumber"))

            'Dim County As String = Convert.ToString(reader("County"))
            'Dim State As String = Convert.ToString(reader("State"))
            Dim AppraiserID As String = Convert.ToString(reader("AppraiserID"))
            Dim ApprLastName As String = Convert.ToString(reader("ApprLastName"))
            Dim ApprFirstName As String = Convert.ToString(reader("ApprFirstName"))

            'Dim ApprRanking As String = Convert.ToString(reader("ApprRanking"))
            'Dim DateAssigned_Date As Date = Convert.ToString(reader("DateAssigned_Date"))
            'Dim DateAssigned_Time As Date = Convert.ToString(reader("DateAssigned_Time"))
            'Dim DateInspectionScheduled As Date = Convert.ToString(reader("DateInspectionScheduled"))
            'Dim DateCompleted As Date = Convert.ToString(reader("DateCompleted"))
            'Dim InspectionTime As Date = Convert.ToString(reader("InspectionTime"))
            'Dim AppraiserFee As Decimal = Convert.ToString(reader("AppraiserFee"))
            'Dim ProductFee As Decimal = Convert.ToString(reader("ProductFee"))
            'Dim NetProfit As Decimal = Convert.ToString(reader("NetProfit"))
            'Dim CostPercentage As Decimal = Convert.ToString(reader("CostPercentage"))
            'Dim ReportType As String = Convert.ToString(reader("ReportType"))
            'Dim Status As String = Convert.ToString(reader("Status"))
            'Dim DateInReview_Date As String = Convert.ToString(reader("DateInReview_Date"))
            'Dim DateInReview_Time As String = Convert.ToString(reader("DateInReview_Time"))



            'TODO: Insert any required validations here...

            If AppraiserID.ToString.Length = 0 Then
                lblImport.Text &= "Every record must have an appraiserID!"

            End If
            'TODO: Get (or create) the corresponding category_ID

            'TODO: Insert a record into the Members table (if it's not a duplicate)

            'Display information in LabelImport

            lblImport.Text &= AppraiserID & ApprLastName & _
               " " & ApprFirstName & "  " & "<br>"
        End While
        reader.Close()


    End Sub
    Protected Function ImportIntoVendorProfile(ByVal AppraiserID As String, ByVal ApprLastName As String, ByVal ApprFirstName As String) As Integer
        ' make sure values don't exceed column limits
        AppraiserID = Left(AppraiserID, 50)
        ApprLastName = Left(ApprLastName, 50)
        ApprFirstName = Left(ApprFirstName, 50)

        Dim VendorID As Integer = 0

        Try
            Dim SSAdapter As New SSSAppraiserDataSetTableAdapters.VendorProfileTableAdapter
            Dim SSDataTable As SSSAppraiserDataSet.VendorProfileDataTable = Nothing
            SSDataTable = SSAdapter.GetVendorByID(VendorID)

            If Not SSDataTable Is Nothing Then
                If SSDataTable.Rows.Count > 0 Then
                    If Not SSDataTable(0).VendorID = Nothing Then
                        VendorID = SSDataTable(0).VendorID
                        lblImport.Text = lblImport.Text & _
                  "<font color=blue>Member Found, Not Imported: " & _
                  " ID: " & VendorID & " " & ".</font><br>"
                    End If
                End If
            End If
            If VendorID = 0 Then
                ' retrieve the identity key member_id from the insert
                VendorID = Convert.ToInt32(SSAdapter.InsertAppraiserQuery(AppraiserID, ApprLastName, ApprFirstName))
                lblImport.Text = lblImport.Text & _
                      "<font color=green>Member Imported: " & _
                      " ID: " & VendorID & " " & ApprLastName & " " & _
                      ApprFirstName & ".</font><br />"
            End If

            Return VendorID

        Catch ex As Exception
            lblImport.Text &= "<font color=red>" & ex.ToString & "</font><br />"
            Return 0

        End Try

    End Function

End Class

Select allOpen in new window