Solved

Visual Basic/Asp.net Upload-Import

Posted on 2011-03-08
4
249 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

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