connecting to a Access Database in a Class

[molemoore]

Im trying to connect to an Access database using a class. I think I have been
able to establish the connection but when trying to test it by pulling data
from the database im getting the error:

"An unhandled exception of type 'System.NullReferenceException' occurred in
DB.exe

Additional information: Object reference not set to an instance of an object."

The code that I currently have is:
----------------------
Public Class DB
Private myConnection As New OleDb.OleDbConnection
Private myDataAdapter As New OleDb.OleDbDataAdapter
Private dsPeople As Data.DataSet
Private tblPeople As Data.DataTable
Private intPosition As Integer = 0
Sub New(ByVal Path As String)
Dim strSelect As String

Try
myConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Persist Security
Info=False"
myConnection.Open()
strSelect = ("SELECT FirstName, LastName, HomePhone, WorkPhone,
CellPhone, Email, MailingAddress, City, State, Zip, Comment, ID FROM People")
myDataAdapter = New OleDb.OleDbDataAdapter(strSelect,
myConnection)
Dim intSize As Integer
dsPeople = New DataSet
intSize = myDataAdapter.Fill(dsPeople)
tblPeople = dsPeople.Tables(0)
Catch ex As Exception
End Try
End Sub
Public Sub Assign()
FirstName = dsPeople.Tables(0).Rows(intPosition).Item(0).tostr ing
'(Problem Line)
End Sub
Property FirstName() As String
Get

Return FirstName
End Get
Set(ByVal Value As String)

FirstName = "bob"
End Set
End Property
End Class
---------------------

When I instanciate the object I get the message on the line indicated.

This has me stumped as I'm sure it's a simple problem.

Thanks for any help

Mole

[Lucas Tam]

=?Utf-8?B?bW9sZW1vb3Jl?= <mo*******@discussions.microsoft.com> wrote in
news:72**********************************@microsof t.com:

Private myConnection As New OleDb.OleDbConnection
Private myDataAdapter As New OleDb.OleDbDataAdapter
Private dsPeople As Data.DataSet
Private tblPeople As Data.DataTable
Private intPosition As Integer = 0
Sub New(ByVal Path As String)
Dim strSelect As String

Try
myConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Persist
Security Info=False"
myConnection.Open()

You didn't create anew isntance of a connection -

Private myConnection As New OleDb.OleDbConnection = new
OleDB.oleDBConnection

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[molemoore]

I tried what you suggested and got the same resulting error message. The code
that I provided works in the Form_Load of another program that I've wrote.
What I'm trying to do is take the code and put it into a class (which I'll
later modify to be used with different databases). What I'm trying to do now
is simply get the same fuctionality as I have with the code in the windows
application.

If the solution you provided is the only thing I have wrong then perhaps I
am implementing it wrong.

Thanks for the timely reply and help

Mole

"Lucas Tam" wrote:

=?Utf-8?B?bW9sZW1vb3Jl?= <mo*******@discussions.microsoft.com> wrote in
news:72**********************************@microsof t.com:

Private myConnection As New OleDb.OleDbConnection
Private myDataAdapter As New OleDb.OleDbDataAdapter
Private dsPeople As Data.DataSet
Private tblPeople As Data.DataTable
Private intPosition As Integer = 0
Sub New(ByVal Path As String)
Dim strSelect As String

Try
myConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Persist
Security Info=False"
myConnection.Open()

You didn't create anew isntance of a connection -

Private myConnection As New OleDb.OleDbConnection = new
OleDB.oleDBConnection

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Charlie]

Your FirstName Property is not set up correctly. Set the property after the
Datatable is filled: me.FirstName = DT.rows(0).item("FirstName")

Private _FirstName as string
Property FirstName() As String
Get
Return _FirstName
End Get
Set(ByVal Value As String)
_FirstName = Value
End Set
End Property
"molemoore" wrote:

Im trying to connect to an Access database using a class. I think I have been
able to establish the connection but when trying to test it by pulling data
from the database im getting the error:

"An unhandled exception of type 'System.NullReferenceException' occurred in
DB.exe

Additional information: Object reference not set to an instance of an object."

The code that I currently have is:
----------------------
Public Class DB
Private myConnection As New OleDb.OleDbConnection
Private myDataAdapter As New OleDb.OleDbDataAdapter
Private dsPeople As Data.DataSet
Private tblPeople As Data.DataTable
Private intPosition As Integer = 0
Sub New(ByVal Path As String)
Dim strSelect As String

Try
myConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & ";Persist Security
Info=False"
myConnection.Open()
strSelect = ("SELECT FirstName, LastName, HomePhone, WorkPhone,
CellPhone, Email, MailingAddress, City, State, Zip, Comment, ID FROM People")
myDataAdapter = New OleDb.OleDbDataAdapter(strSelect,
myConnection)
Dim intSize As Integer
dsPeople = New DataSet
intSize = myDataAdapter.Fill(dsPeople)
tblPeople = dsPeople.Tables(0)
Catch ex As Exception
End Try
End Sub
Public Sub Assign()
FirstName = dsPeople.Tables(0).Rows(intPosition).Item(0).tostr ing
'(Problem Line)
End Sub
Property FirstName() As String
Get

Return FirstName
End Get
Set(ByVal Value As String)

FirstName = "bob"
End Set
End Property
End Class
---------------------

When I instanciate the object I get the message on the line indicated.

This has me stumped as I'm sure it's a simple problem.

Thanks for any help

Mole

[molemoore]

Ok, I'll give that a shot and see if I can figure it out.

While I've got some attention :) I've got another problem: When I'm trying
to send the data from my dataset back to the original database it's not
updating the database correctly. Again, this is probably a simple problem
that's showing my lack of experience. The code I have is:

dsPeople.Tables(0).Rows(intPosition).EndEdit()
dsPeople.AcceptChanges()
myDataAdapter.Update(dsPeople)

Once again your help is greatly appreciated

Mole

"Charlie" wrote:

Your FirstName Property is not set up correctly. Set the property after the
Datatable is filled: me.FirstName = DT.rows(0).item("FirstName")

Private _FirstName as string
Property FirstName() As String
Get
Return _FirstName
End Get
Set(ByVal Value As String)
_FirstName = Value
End Set
End Property

[Charlie]

To update a database, you need an Update statement, an Insert statement, or a
Delete statement. The most simple and straightforward way to handle that is
just to concatenate the statement, using the correct syntax and the values
entered by the user on the form. Then set up an oledb.oledbcommand, using
the statement and your connection as parameters. Make sure you have a "WHERE
PrimaryKey = ..." clause for any Update or Delete statement. Open the
connection. Use the ExecuteNonQuery method of the command object. Close the
connection. Check the database for the update. Use Try blocks to catch
errors.

www.charlesfarriersoftware.com

"molemoore" wrote:

Ok, I'll give that a shot and see if I can figure it out.

While I've got some attention :) I've got another problem: When I'm trying
to send the data from my dataset back to the original database it's not
updating the database correctly. Again, this is probably a simple problem
that's showing my lack of experience. The code I have is:

dsPeople.Tables(0).Rows(intPosition).EndEdit()
dsPeople.AcceptChanges()
myDataAdapter.Update(dsPeople)

Once again your help is greatly appreciated

Mole

"Charlie" wrote:

Your FirstName Property is not set up correctly. Set the property after the
Datatable is filled: me.FirstName = DT.rows(0).item("FirstName")

Private _FirstName as string
Property FirstName() As String
Get
Return _FirstName
End Get
Set(ByVal Value As String)
_FirstName = Value
End Set
End Property

[Cor Ligthert]

Moolemoore,

Exactly as you wrotie is this showing your lack of experience.

:-)

This was in past the one of the most showed problems in the ADONET
newsgroup.

dsPeople.Tables(0).Rows(intPosition).EndEdit()
dsPeople.AcceptChanges()
What Acceptchanges does is telling that the updates are done and set all the
rowschanges to done and therefore will the next update update nothing.

The Acceptchanges is a part of the datataadaper.update when the original
dataset is used.
myDataAdapter.Update(dsPeople)

I hope this helps?

Cor

[Lucas Tam]

=?Utf-8?B?bW9sZW1vb3Jl?= <mo*******@discussions.microsoft.com> wrote in
news:71**********************************@microsof t.com:

I tried what you suggested and got the same resulting error message.

Not to mention, you didn't create new instances of any of your variables.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/