Solved

Visual Basic/Asp.net Upload-Import

Posted on 2011-03-08
4
226 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
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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
Comment Utility
Dim AppraiserID As String = Convert.ToString(reader("AppraiserID"))
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now