[2005] Can't update, change or add new row in a Access database

[sparrow1]

Hi All,

I'm trying to create a first Database and use a AccessDatabase to do so.

The problem I have is that:

- Can't create a new Row
- Can't update my database
- Can't commit my database

Every time I've got an erros saying that:
Object reference not set to an instance of an object.

Here's my code to update my database:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.Fill(ds, "AddressBook")

        ds.Tables("AddressBook").Rows(inc).Item(1) = IDTextBox.Text
        ds.Tables("AddressBook").Rows(inc).Item(1) = FirstNameTextBox.Text
        ds.Tables("AddressBook").Rows(inc).Item(2) = SurnameTextBox.Text

        da.Update(ds, "AddressBook")

        MsgBox("Data updated")
    End Sub

Thanks in advance,

sparrow1

[jmcilhinney]

Which line throws the exception? Which reference on that line is Nothing?

[talkro]

I think that da in line

Dim cb As New OleDb.OleDbCommandBuilder(da)

hasn't been declared before being used

[sparrow1]

Which line throws the exception? Which reference on that line is Nothing?

Hi,

I was in a little hurry and forgot to tell you wich line gave me the error;

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        da.Fill(ds, "AddressBook")  ' <- here's the error

        ds.Tables("AddressBook").Rows(inc).Item(1) = IDTextBox.Text
        ds.Tables("AddressBook").Rows(inc).Item(1) = FirstNameTextBox.Text
        ds.Tables("AddressBook").Rows(inc).Item(2) = SurnameTextBox.Text

        da.Update(ds, "AddressBook")

        MsgBox("Data updated")
    End Sub

You can also see in attachment what error it gives me.

Wkr,

sparrow1

[talkro]

Where did you declare the variable da?

[sparrow1]

Where did you declare the variable da?

Hi,

I declared the variable da before the form_load.
This is my variable:

Dim da As OleDb.OleDbDataAdapter

Wkr,

sparrow1

[talkro]

try

private da as oledb.oledbDataAdapter

private has a scope for the all class while dim as a limited scope (generally for a specific sub or function)

[stimbo]

I think you forgot the New keyword... either with the dataset or the data adapter

[onlyGirl]

You need the New keyword.

[jmcilhinney]

If you take a look at the error message, it's telling you exactly the same thing as stimbo and onlyGirl.

try

private da as oledb.oledbDataAdapter

private has a scope for the all class while dim as a limited scope (generally for a specific sub or function)

"Dim" and "Private" are completely different things. "Dim" has nothing whatsoever to do with scope and everything to do with declaring a variable, no matter where it's declared. Try entering the code you suggested in the IDE and then mouse over the variable name. You'll see that the tool tip says "Private Dim da As OleDb.OleDbDataAdapter". "Dim" is ALWAYS included when you declare a variable, whether explicitly or implicitly. If you omit the "Dim" key word on a member variable declaration then it is implied. If you omit the access modifier on a member variable declaration and then the default is implied. The default access level for classes is Private while for structures it's Public. Access modifiers like Private are used on all members and types too, while the "Dim" key word is used for one thing only: declaring variables.

[sparrow1]

Hi,

Thanks for your time and explanation about the declaration of variables.

Where shout I use the New Ketword like Stimbo and OnlyGirl suggested.


It is my first database to create and I'm still reading about it.
So, Where should I put the New keyword.

Thanks,

sparrow1

[jmcilhinney]

It doesn't matter whether you've used databases before or whatever. Classes are classes and objects are objects. If you want to create an object, i.e. and instance of a class, then you have to use the New key word to invoke its constructor. All classes are the same. Look at this from your own code:

Dim cb As New OleDb.OleDbCommandBuilder(da)

You wanted a new instance of the OleDbCommandBuilder class so you used the New key word to invoke the class constructor and create an instance. The OleDbDataAdapter class is exactly the same, as is any class.

[sparrow1]

Hi All,

I've rebuild my database and now she's working.

But in the mean time I've got some difficulties like, if the database isn't loaded and the user clicks on the Next Record button or another one then I've got the Null reference error again.

I thought in the first place to Enable all the buttons except the Load button.
The easy way, but I want it the other way.

Is it possible to add some code like:

If the database isn't loaded then
a messagebox with Yes/No and
a text like "would you like to load it" then
yes and load the database.

This to avoid the Error message and crashes the databaseapplication.

If there's another solution, please tell me about it.

Wkr,

sparrow1

[calvin-c]

Assuming you don't want to load the database at startup, I'd disable all the buttons except Load-then enable them at the (successful) end of the Load procedure.

It's possible to use the approach you described, but you'd need to add the 'load database' question to each of the buttons that manipulates the database (either explicitly or by each of the buttons calling a common routine).

[sparrow1]

Hi,

Thanks for your reply!
That was my first thinking about it also.

I've found already something and It does it, like this;

If inc <> -1 Then
            MsgBox("Load Database First")
            Exit Sub  ' instead of exit sub how can I load my database here
        End If

Wkr,

sparrow1

[calvin-c]

When using a shared database you'll probably want to open a connection, use it (e.g. fill the dataset) & then close it. Since this is Access it might not be shared so you could open it when the program starts, in which case all you'd need to do here is fill the dataset.

[circuits2]

When using a shared database you'll probably want to open a connection, use it (e.g. fill the dataset) & then close it. Since this is Access it might not be shared so you could open it when the program starts, in which case all you'd need to do here is fill the dataset.

A DataAdapter opens and closes the connection on its own. There is no need to manaully code the opening and closing actions.

Sparrow, are you using any datasource controls like the BindingNavigator?

[sparrow1]

A DataAdapter opens and closes the connection on its own. There is no need to manaully code the opening and closing actions.

Sparrow, are you using any datasource controls like the BindingNavigator?

Hi,

No I don't use a BindingNavigator.
It's just a Access database.
With a :OleDb.OleDbConnection
OleDb.OleDbDataAdapter
OleDb.OleDbCommandBuilder

Wkr,

sparrow1

[stimbo]

Hi sparrow1,

so, if a user selects a button to look at something but you haven't loaded it yet you get an error because the dataset hasn't been filled yet, correct? There's probably a better way of doing this but you could count if there is a table in the dataset.

If Not ds.Tables.Count = 0 Then
        'Execute code as normal here
 
    Else
 
         Select Case MessageBox.Show("No data exists. Load now?", "Load Data?", MessageBoxButtons.OKCancel)
 
            Case Windows.Forms.DialogResult.OK
 
                 'Fill data here
        End Select
 
End If

[calvin-c]

Oops. Forgot that-we use a common routine that both creates & opens the connection, plus another that closes & destroys it. (It was created for other objects, but it's so convenient that we use it for data adapters, too.)

Sparrow, assuming you've already created your connection what I'd do is replace Exit sub with your da.fill command. You'll need to be sure that you've created da as New & assigned it a select command, at least.

Good luck.