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?
 
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
0
 
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
0
 
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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?
0
 
appariCommented:
split them using ","
and remove starting " from first element and ending " from the last element.
0
 
stevegingellAuthor Commented:
can you put that down in code please.
0
 
appariCommented:

split(line, """,""")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.