Solved

Visual Basic/Asp.net Upload-Import

Posted on 2011-03-08
4
244 Views
Last Modified: 2012-05-11
I am attempting to follow a tutorial on 4 Guys From Rolla for Importing Excel into SQL and hitting a snag. http://www.4guysfromrolla.com/articles/032608-1.aspx

My code is throwing an error when I attempt to import into the table.  Specifically line 88.   "IndexOutOfRangeexception was unhandled by user code".  

The troubleshooting tips recommend checking maximum length, column names and index is not a negative number.  

Any help is appreciated.  Thank you.





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

Open in new window

0
Comment
Question by:rcowen00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 35076045
Do you know specifically which line it is in your code? Line 88 in the above snippet is a commented line, so I'm guessing thats not everything and the line #s won't match
0
 

Author Comment

by:rcowen00
ID: 35076070
Dim AppraiserID As String = Convert.ToString(reader("AppraiserID"))
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35076108
That means it can't find a field named "AppraiserID" in the datareader

Is that the first column? If so, try changing it to reader(0) and see if that works...
0
 

Author Comment

by:rcowen00
ID: 35077510
If I remove the column headers then it at least go to the next step.  Thank you.

 Dim AppraiserID As String = Convert.ToString(reader(0))
            Dim ApprLastName As String = Convert.ToString(reader(1))
            Dim ApprFirstName As String = Convert.ToString(reader(2))

vs

Dim AppraiserID As String = Convert.ToString(reader(AppraiserID))
            Dim ApprLastName As String = Convert.ToString(reader(ApprLastName))
            Dim ApprFirstName As String = Convert.ToString(reader(ApprLastName))
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question