Getting Index out of bound error on csv import

I am having problems with importing data from csv file to my sql database. I keep getting the following error:

"Index was outside the bounds of the array."

It says its at this line: objCmd.Parameters.Add("@SONumber", Record(1))


Here is my on click event:

 Sub Import(ByVal Sender As Object, ByVal E As EventArgs)
        If Not (txtUpload.PostedFile Is Nothing) Then
            Dim postedFile As HttpPostedFile = txtUpload.PostedFile
            Dim filename As String = Path.GetFileName(postedFile.FileName)
            Dim filepath As String = "C:\Inetpub\wwwroot\shipping\uploads\" & filename
            postedFile.SaveAs(filepath)
            lblMessage.Text = "File has been uploaded"

            Dim objStreamReader As StreamReader
            Dim line As String

            objStreamReader = File.OpenText(filepath)
            line = objStreamReader.ReadLine()

            While (line <> Nothing)
                lblResult.Text &= line & "<br>"
                line = objStreamReader.ReadLine()

                Dim Record() = Split(line, ",")

                Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("DSN"))
                Dim objCmd = New SqlCommand("INSERT INTO ShippingData (InvoiceDate, SONumber," & _
                "PONumber, CarrierID, PackageNumber, TrackingNumber) " & _
                "VALUES (@InvoiceDate, @SONumber, @PONumber, @CarrierID, " & _
                "@PackageNumber, @TrackingNumber)", objConn)

                objCmd.Parameters.Add("@InvoiceDate", Record(0))
                objCmd.Parameters.Add("@SONumber", Record(1))
                objCmd.Parameters.Add("@PONumber", Record(2))
                objCmd.Parameters.Add("@CarrierID", Record(3))
                objCmd.Parameters.Add("@PackageNumber", Record(4))
                objCmd.Parameters.Add("@TrackingNumber", Record(5))
                objConn.Open()
                objCmd.ExecuteNonQuery()
                objConn.Close()
                lblMessage.Text = "Shipment has been Imported!"
        End While
            objStreamReader.Close()

        End If
    End Sub
stevegingellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:
it means the line you read from the csv file has no commas.
change your code like this,

                Dim Record() = Split(line, ",")

            if Record.GetUpperBound(0)>= 5 then
                      Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("DSN"))
                      Dim objCmd = New SqlCommand("INSERT INTO ShippingData (InvoiceDate, SONumber," & _
                      "PONumber, CarrierID, PackageNumber, TrackingNumber) " & _
                      "VALUES (@InvoiceDate, @SONumber, @PONumber, @CarrierID, " & _
                      "@PackageNumber, @TrackingNumber)", objConn)

                      objCmd.Parameters.Add("@InvoiceDate", Record(0))
                      objCmd.Parameters.Add("@SONumber", Record(1))
                      objCmd.Parameters.Add("@PONumber", Record(2))
                      objCmd.Parameters.Add("@CarrierID", Record(3))
                      objCmd.Parameters.Add("@PackageNumber", Record(4))
                      objCmd.Parameters.Add("@TrackingNumber", Record(5))
                      objConn.Open()
                      objCmd.ExecuteNonQuery()
                      objConn.Close()
                      lblMessage.Text = "Shipment has been Imported!"
            else
                  ' add code here to handle the case when data has less than 5 columns of data
            end if

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevegingellAuthor Commented:
Worked like a gem! Thanks much.

BTW, What do you mean by "it means the line you read from the csv file has no commas." Isnt splitting functions breaking it up on the basis of commas. Also what are you doing by Record.GetUpperBound(0)>= 5
appariCommented:

suppose the line you read from csv has the value "This is test line without comma"
when you split this line using
Split(line, ",")
record array will be an array with only one element, so when you refer Record(1) you are getting the error.
by using record.getupperbound(0) i am checking the number elements in the records array after the split, if it has 5 or more elements proceed else display error
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

stevegingellAuthor Commented:
Thanks for the explain...one more question. what do i do if I want to upload/import also from an excel document along with the csv file. Then i get the values seperated by double-quotes( and commas). How do i split them then?
appariCommented:
split them using ","
and remove starting " from first element and ending " from the last element.
stevegingellAuthor Commented:
can you put that down in code please.
appariCommented:

split(line, """,""")
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.