Link to home
Start Free TrialLog in
Avatar of stevegingell
stevegingell

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stevegingell
stevegingell

ASKER

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

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

split(line, """,""")