A
Allen
Hey all, I have a question for you all. I'm learning vb.net and need some
help. I have two classes one named Customers and one named CustomersDA. I
have to go though Customer for everything. I have a listbox that needs to
get data from an array. I call a function called GetAll that is a ArrayList
in Customers that then calls CustomersDA.GetAll that is also an array list
but returns customers (see code below).I need to fill a listbox from the
ArrayList from GetAll. I cant seem to get it to work. Here are the classes
and code:
Imports System.Data.OleDb
Imports System.Collections
Public Class CustomerDA
Shared customers As New ArrayList() ' Customer references
Shared aCustomer As Customer
'Declare a connection. Eliminate the hard-coded path to
'the database by putting the database file in the
'project's Bin folder
Shared cnnCustomer As New _
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Customers.mdb")
'Declare variables for Customer attribute values
Shared name, address, phoneno As String
'Initialize Method (chapter 13 example)
'no connection passed as reference
Public Shared Sub Initialize()
Try
' Try to open the connection
cnnCustomer.Open()
Catch e As Exception
Console.WriteLine(e.ToString)
End Try
End Sub
' Terminate Method (chapter 13 example)
Public Shared Sub Terminate()
Try
cnnCustomer.Close()
cnnCustomer = Nothing
Catch e As Exception
Console.WriteLine(e.Message.ToString)
End Try
End Sub
' AddNew Method --Throws DuplicateException if exists
Public Shared Sub AddNew(ByVal aCustomer As Customer)
' Get customer information
name = aCustomer.GetName
address = aCustomer.GetAddress
phoneno = aCustomer.GetPhoneNo
' Declare a string SQL statement
Dim sqlInsert As String = "INSERT INTO customerTable " & _
"VALUES ('" & name & "', '" & address & "', '" & phoneno & " ')"
Dim adpcustomer As New OleDbDataAdapter()
Try
Dim c As Customer = Find(phoneno)
Throw New DuplicateException(" Customer Exists ")
Catch e As NotFoundException
Try ' Assign Insert Commands and Execute
adpcustomer.InsertCommand = New OleDbCommand(sqlInsert)
adpcustomer.InsertCommand.Connection = cnnCustomer
adpcustomer.InsertCommand.ExecuteNonQuery()
Finally
End Try
End Try
End Sub
' Find Method--Throws NotFoundException if Not Found
Public Shared Function Find(ByVal key As String) As Customer
Dim acustomer As New Customer()
acustomer = Nothing
Dim dsCustomer As New DataSet()
Try
' Define the SQL SQL statement using the phoneno key
Dim sqlQuery As String = "SELECT Name, Address, PhoneNO " & _
"FROM CustomerTable WHERE phoneNo = '" & key & "'"
Dim adpCustomer As New _
OleDbDataAdapter(sqlQuery, cnnCustomer)
adpCustomer.Fill(dsCustomer, "CustTable")
If dsCustomer.Tables("CustTable").Rows.Count > 0 Then
Dim custRow As DataRow
custRow = dsCustomer.Tables("custTable").Rows(0)
name = custRow.Item("Name")
address = custRow.Item("address")
phoneno = custRow.Item("phoneno")
acustomer = New Customer(name, address, phoneno)
Else
Throw New NotFoundException("Not Found")
End If
dsCustomer = Nothing
Catch e As OleDb.OleDbException
Console.WriteLine(e.Message.ToString)
End Try
Return acustomer
End Function
' GetAll Method
Public Shared Function GetAll() As ArrayList
Dim dsCustomer As New DataSet()
Dim sqlQuery As String = "SELECT Name, Address, PhoneNo " & _
"FROM CustomerTable"
Try
Dim adpCustomer As New _
OleDbDataAdapter(sqlQuery, cnnCustomer)
adpCustomer.Fill(dsCustomer, "CustTable")
If dsCustomer.Tables("CustTable").Rows.Count > 0 Then
Dim dsRow As DataRow
' Clear the array list
customers.Clear()
For Each dsRow In dsCustomer.Tables("CustTable").Rows
name = dsRow("Name")
address = dsRow("Address")
phoneno = dsRow("PhoneNo")
Dim aCustomer As New _
Customer(name, address, phoneno)
customers.Add(aCustomer)
Next
Else
' No records in database
End If
dsCustomer = Nothing
Catch e As Exception
Console.WriteLine(e.ToString)
Throw New NotFoundException("Not Found")
End Try
Return customers
End Function
help. I have two classes one named Customers and one named CustomersDA. I
have to go though Customer for everything. I have a listbox that needs to
get data from an array. I call a function called GetAll that is a ArrayList
in Customers that then calls CustomersDA.GetAll that is also an array list
but returns customers (see code below).I need to fill a listbox from the
ArrayList from GetAll. I cant seem to get it to work. Here are the classes
and code:
Imports System.Data.OleDb
Imports System.Collections
Public Class CustomerDA
Shared customers As New ArrayList() ' Customer references
Shared aCustomer As Customer
'Declare a connection. Eliminate the hard-coded path to
'the database by putting the database file in the
'project's Bin folder
Shared cnnCustomer As New _
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=Customers.mdb")
'Declare variables for Customer attribute values
Shared name, address, phoneno As String
'Initialize Method (chapter 13 example)
'no connection passed as reference
Public Shared Sub Initialize()
Try
' Try to open the connection
cnnCustomer.Open()
Catch e As Exception
Console.WriteLine(e.ToString)
End Try
End Sub
' Terminate Method (chapter 13 example)
Public Shared Sub Terminate()
Try
cnnCustomer.Close()
cnnCustomer = Nothing
Catch e As Exception
Console.WriteLine(e.Message.ToString)
End Try
End Sub
' AddNew Method --Throws DuplicateException if exists
Public Shared Sub AddNew(ByVal aCustomer As Customer)
' Get customer information
name = aCustomer.GetName
address = aCustomer.GetAddress
phoneno = aCustomer.GetPhoneNo
' Declare a string SQL statement
Dim sqlInsert As String = "INSERT INTO customerTable " & _
"VALUES ('" & name & "', '" & address & "', '" & phoneno & " ')"
Dim adpcustomer As New OleDbDataAdapter()
Try
Dim c As Customer = Find(phoneno)
Throw New DuplicateException(" Customer Exists ")
Catch e As NotFoundException
Try ' Assign Insert Commands and Execute
adpcustomer.InsertCommand = New OleDbCommand(sqlInsert)
adpcustomer.InsertCommand.Connection = cnnCustomer
adpcustomer.InsertCommand.ExecuteNonQuery()
Finally
End Try
End Try
End Sub
' Find Method--Throws NotFoundException if Not Found
Public Shared Function Find(ByVal key As String) As Customer
Dim acustomer As New Customer()
acustomer = Nothing
Dim dsCustomer As New DataSet()
Try
' Define the SQL SQL statement using the phoneno key
Dim sqlQuery As String = "SELECT Name, Address, PhoneNO " & _
"FROM CustomerTable WHERE phoneNo = '" & key & "'"
Dim adpCustomer As New _
OleDbDataAdapter(sqlQuery, cnnCustomer)
adpCustomer.Fill(dsCustomer, "CustTable")
If dsCustomer.Tables("CustTable").Rows.Count > 0 Then
Dim custRow As DataRow
custRow = dsCustomer.Tables("custTable").Rows(0)
name = custRow.Item("Name")
address = custRow.Item("address")
phoneno = custRow.Item("phoneno")
acustomer = New Customer(name, address, phoneno)
Else
Throw New NotFoundException("Not Found")
End If
dsCustomer = Nothing
Catch e As OleDb.OleDbException
Console.WriteLine(e.Message.ToString)
End Try
Return acustomer
End Function
' GetAll Method
Public Shared Function GetAll() As ArrayList
Dim dsCustomer As New DataSet()
Dim sqlQuery As String = "SELECT Name, Address, PhoneNo " & _
"FROM CustomerTable"
Try
Dim adpCustomer As New _
OleDbDataAdapter(sqlQuery, cnnCustomer)
adpCustomer.Fill(dsCustomer, "CustTable")
If dsCustomer.Tables("CustTable").Rows.Count > 0 Then
Dim dsRow As DataRow
' Clear the array list
customers.Clear()
For Each dsRow In dsCustomer.Tables("CustTable").Rows
name = dsRow("Name")
address = dsRow("Address")
phoneno = dsRow("PhoneNo")
Dim aCustomer As New _
Customer(name, address, phoneno)
customers.Add(aCustomer)
Next
Else
' No records in database
End If
dsCustomer = Nothing
Catch e As Exception
Console.WriteLine(e.ToString)
Throw New NotFoundException("Not Found")
End Try
Return customers
End Function