[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting Index out of bound error on csv import

Posted on 2006-04-25
7
Medium Priority
?
357 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:stevegingell
  • 4
  • 3
7 Comments
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 16541123
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
 

Author Comment

by:stevegingell
ID: 16541275
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
 
LVL 39

Expert Comment

by:appari
ID: 16541295

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:stevegingell
ID: 16541481
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
 
LVL 39

Expert Comment

by:appari
ID: 16541520
split them using ","
and remove starting " from first element and ending " from the last element.
0
 

Author Comment

by:stevegingell
ID: 16541560
can you put that down in code please.
0
 
LVL 39

Expert Comment

by:appari
ID: 16541633

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

830 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