Question ArrayList Error Problem passing a DataSet

merckrene

Member
Joined
Nov 3, 2009
Messages
9
Programming Experience
Beginner
have a problem on my code it runs and display what I want but has a error says

ArgumentOutOfRangeException was unhandled
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index


it pointing on MsgBox(dta(0)(1).ToString)

this is my code
VB.NET:
Public Class dbConnection
    Private dbProvider As String
    Private dbSource As String
    Private dbPassword As String
    Private dbName As String
    Private dbHost As String
    Private count As Integer
    Private con As New OleDb.OleDbConnection
    Private comm As New OleDb.OleDbCommand
    Private da As New OleDb.OleDbDataAdapter
    Private ds As New DataSet

    Private Sub dbGetConnection()
        dbName = "dbSAIS.mdb"
        dbHost = ""
        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        dbSource = "Data Source = " & dbName & ";"
        dbPassword = "Jet OLEDB:Database Password=password;"
        con.ConnectionString = dbProvider & dbSource & dbPassword
    End Sub
    Function dbConnect() As Integer
        Dim status As Boolean = 2
        dbGetConnection()
        Try
            con.Open()
        Catch ex As Exception
            status = 0
        End Try
        If status <> 0 Or status <> 2 Then
            status = 1
        End If
        Return status
    End Function
    Function dbClose() As Integer
        Dim status As Boolean = 2
        Try
            con.Close()
        Catch ex As Exception
            status = 0
        End Try
        If status <> 0 Or status <> 2 Then
            status = 1
        End If
        Return status
    End Function
    Function executeNonQuery(ByVal query As String) As Integer
        Dim status As Boolean
        comm.CommandText = query
        comm.Connection = con
        dbConnect()
        Try
            comm.ExecuteNonQuery()
        Catch ex As Exception

        End Try
        dbClose()
        Return status
    End Function
    Public Function executeQuery(ByVal query As String) As ArrayList
        Dim dtArray As New ArrayList
        dbConnect()
        Try
            da.SelectCommand = New OleDb.OleDbCommand(query, con)
            da.Fill(ds, "Table")
            count = ds.Tables("Table").Rows.Count
            For Each row As DataRow In ds.Tables("Table").Rows
                dtArray.Add(CType(row, DataRow))
            Next
        Catch ex As Exception

        End Try
        dbClose()
        Dim dta As New ArrayList
        dta = dtArray
        MsgBox(dta(0)(1).ToString)
        Return dtArray
    End Function
    Function dbCount() As Integer
        Return Count
    End Function
End Class
 
Hi,

You have got a few issues going on here:-

1) Have you checked the value of your Count variable to make sure that the query passed to your DataAdapter is actually returning any records from your Database? You will get this error if No Rows had been returned from the Database or the Rows that were returned only had 1 column in the Row since you are trying to access column 2.

2) If you are only going to be returning a single table from your Database then use a DataTable and not a DataSet.

3) Do not use an ArrayList to hold object types, this is old hat, and if you turn Option Strict On then you would have to do a Type Conversion to use a DataRow contained within the ArrayList. Use a List(of DataRow) instead and no Type Conversion would be necessary when using the List.

4) You have got some strange and unnecessary code in that block you posted.

If you are going to use Boolean variables, i.e:-

VB.NET:
Dim status As Boolean = 2

then those variables should only ever be assigned the values True or False.

Why do a Type Conversion here when it is already of type DataRow:-

VB.NET:
For Each row As DataRow In ds.Tables("Table").Rows
  dtArray.Add(CType(row, DataRow))
Next

What is the point of this code when everything is in your dtArray:-

VB.NET:
Dim dta As New ArrayList
dta = dtArray
MsgBox(dta(0)(1).ToString)
Return dtArray

What is this supposed to do:-

VB.NET:
Function dbCount() As Integer
  Return count
End Function

Nothing at the moment.

4) Finally, turn Option Strict On and keep it On to help you avoid errors before they happen in the future. If you do so then you are going to get some additional conversion errors that you need to deal with.

Hope that gets you started.

Cheers,

Ian
 
Back
Top