Solved

Visual Basic/Asp.net Upload-Import

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

691 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