Problems - ArrayList

[knockyo]

[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica]But i am using the ArrayList to store each of the SQL. Eg:[/FONT]

[/FONT]

Dim aSQLMaster As New ArrayList
StrSQL = "INSERT INTO NXP_AM1_ACU_DETAIL " _
& "(FromId,P001,P002,P003,P004,P005, " _
& "P006,P007) VALUES ('" & UniqueId & "', " _
& "'" & aColumns(5) & "', '" & aColumns(8) & "', " _
& "'" & aColumns(11) & "', '" & aColumns(14) & "', " _
& "'" & aColumns(18) & "', '" & aColumns(21) & "', " _
& "'" & aColumns(24) & "')"
aSQLMaster.Add(StrSQL)

[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica][/FONT][/FONT][FONT=Verdana, Arial, Helvetica]

[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica]But i get the error is: Index was outside the bounds of the array [/FONT][/FONT]
[/FONT][/FONT]

 

[cjard]

euww@that SQL..

did you ever come across the notion of parameterised SQLs?


>Index was outside the bounds of the array

You get this when you ask an array of 3 elements for its fourth element. Or an array of 27 for the 1075th element or.. you get the idea

 

[knockyo]

[FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica]here is my code:

[/FONT][/FONT][FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica]

[FONT=courier][SIZE=2]Dim clsReader As TextReader = File.OpenText(txtFile.Text)
                Dim sLine As String = clsReader.ReadLine()
                While sLine <> Nothing
                    If sLine.StartsWith("Data") Then
                        sData = sLine.Substring(0)
                    ElseIf sLine.StartsWith("Order") Then
                        sOrder = sLine.Substring(11)
                    ElseIf sLine.StartsWith("Job") Then
                        sJob = sLine.Substring(11)
                        sJob = Path.GetFileNameWithoutExtension(sJob)
                        sJob = sJob.Replace("_", "/")
                        Dim DT As DataTable = Nothing
                        pmCon.QueryMe("SELECT UniqueId FROM NXP_AM1_ACU_MASTER WHERE " _
                                & "DeviceType='" & sJob & "'", DT)
                        If DT.Rows.Count <> 0 Then
                            UniqueId = Trim(DT.Rows(0).Item(0))
                        End If
                    Else
                        aColumns = sLine.Split(Char.Parse(" "))
                        If aColumns.Length <> 0 Then
                            StrSQL = "UPDATE NXP_AM1_ACU_MASTER SET " _
                                     & "BatchNo='" & Trim(sOrder) & "', " _
                                     & "TrackNo='" & aColumns(0) & "', " _
                                     & "SortNo='" & aColumns(1) & "', " _
                                     & "BinNo='" & aColumns(2) & "', " _
                                     & "FinalResult='" & aColumns(3) & "', " _
                                     & "ACUFileName='" & Trim(sOrder) & ".Dat" & "' " _
                                     & "WHERE UniqueId='" & UniqueId & "'"
                            aSQLMaster.Add(StrSQL)

                            StrSQL = "INSERT INTO NXP_AM1_ACU_DETAIL " _
                                        & "(FromId,P001,P002,P003,P004,P005, " _
                                        & "P006,P007) VALUES ('" & UniqueId & "', " _
                                        & "'" & aColumns(5) & "', '" & aColumns(8) & "', " _
                                        & "'" & aColumns(11) & "', '" & aColumns(14) & "', " _
                                        & "'" & aColumns(18) & "', '" & aColumns(21) & "', " _
                                        & "'" & aColumns(24) & "')"
                            aSQLMaster.Add(StrSQL)
                        End If
                    End If
                    sLine = clsReader.ReadLine
[/SIZE][/FONT][FONT=Verdana, Arial, Helvetica][FONT=Verdana, Arial, Helvetica][SIZE=2][COLOR=midnightblue]

[/FONT][/FONT]
[/COLOR][/SIZE][/FONT][/FONT]

 

[cjard]

euww@that SQL..

did you ever come across the notion of parameterised SQLs?


>Index was outside the bounds of the array

You get this when you ask an array of 3 elements for its fourth element. Or an array of 27 for the 1075th element or.. you get the idea

 

[knockyo]

so, for my problems means that? My sql there should not write like that? must use parameter?

 

[knockyo]

okay, i know the problems is at where, here is my loop until 6:

for my text file result i want to grab is like this:

the file above show me problems code is here

aColumns = sLine.Split(Char.Parse(" "))

how i ignore no matter how many SPACE in between the data also inlcude ONLY ONE SPACE?

 

[knockyo]

i solve it with Split it with Multiple Space

anyway, thanks you point my error, so i can direct go to alter my errors

really thanks!

 

[JohnH]

You have the Split option to remove empty entries:

Dim split1() As String = sLine.Split(" ".[SIZE=2]ToCharArray[/SIZE], StringSplitOptions.RemoveEmptyEntries)

Edit: Oops, I just noticed you are on .Net 1.1. knockyo, and that version of Split is new in .Net 2.0. Anyway you could split by single space character and just remove the empty elements yourself, it is much faster and easier processing than to search&substring over the line.

 

[cjard]

and you can use the OLEDB database driver to read your text file.. by writing a schema.ini file you can use the file jsut like a database table.. its really much easier than messing around with splitting it yourself..