ADO Update Problem

[TBass]

Hi,

We have a process management system in VB .NET. It usually used text
files, but I'm trying to switch to using access database files and
ADO.

I made a form with a datagrid and a few buttons.

I define the variables for this program as follows:

Imports System.Data.OleDb

Private dbConnRoutine As OleDbConnection = Nothing
Private dbAdapterRoutine As OleDbDataAdapter = Nothing
Private dbDataSetRoutine As DataSet = Nothing
Private dbBuilderRoutine As OleDbCommandBuilder = Nothing

When they press the LOAD button, the following is executed:

Dim connString As String
Dim sql As String
connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + FileName
dbConnRoutine = New OleDbConnection(connString)

sql = "SELECT * FROM rtnmain"
Dim objCommand As New OleDbCommand(sql, dbConnRoutine)

dbAdapterRoutine = New OleDbDataAdapter(objCommand)
dbAdapterRoutine.TableMappings.Add("Table", "rtnmain")
dbBuilderRoutine = New
OleDbCommandBuilder(dbAdapterRoutine)

dbDataSetRoutine = New DataSet("rtnmain")

dbAdapterRoutine.Fill(dbDataSetRoutine, "rtnmain")
gridRoutine.DataSource =
dbDataSetRoutine.Tables("rtnmain")
gridRoutine.SetDataBinding(dbDataSetRoutine,
"rtnmain")
At which point, the data is loaded. No problems there.

Then, I'll add a row to the datagrid. I press the UPDATE button. The
following code is executed:

dbBuilderRoutine.GetUpdateCommand()
dbAdapterRoutine.Update(dbDataSetRoutine)

The result is an error.

An unhandled exception of type 'System.NullReferenceException'
occurred in system.data.dll

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

Every example I've checked follows this process. Can anyone point out
what I'm doing wrong, or perhaps an alternative way of updating the
table?

Another question:: do I have to re-fill the dataset before trying to
update?

Thanks!
Tom Junior

[Cor Ligthert[MVP]]

Hi,

I don't see real errors however not needed in your code is
- the setting to nothing,
- dbBuilderRoutine.GetUpdateCommand()
(The latter can be the reason of your error, I have never used that.)

Cor
"TBass" <tb*@automateddesign.comschreef in bericht
news:f3**********************************@e23g2000 prf.googlegroups.com...

Hi,

We have a process management system in VB .NET. It usually used text
files, but I'm trying to switch to using access database files and
ADO.

I made a form with a datagrid and a few buttons.

I define the variables for this program as follows:

Imports System.Data.OleDb

Private dbConnRoutine As OleDbConnection = Nothing
Private dbAdapterRoutine As OleDbDataAdapter = Nothing
Private dbDataSetRoutine As DataSet = Nothing
Private dbBuilderRoutine As OleDbCommandBuilder = Nothing

When they press the LOAD button, the following is executed:

Dim connString As String
Dim sql As String
connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + FileName
dbConnRoutine = New OleDbConnection(connString)

sql = "SELECT * FROM rtnmain"
Dim objCommand As New OleDbCommand(sql, dbConnRoutine)

dbAdapterRoutine = New OleDbDataAdapter(objCommand)
dbAdapterRoutine.TableMappings.Add("Table", "rtnmain")
dbBuilderRoutine = New
OleDbCommandBuilder(dbAdapterRoutine)

dbDataSetRoutine = New DataSet("rtnmain")

dbAdapterRoutine.Fill(dbDataSetRoutine, "rtnmain")
gridRoutine.DataSource =
dbDataSetRoutine.Tables("rtnmain")
gridRoutine.SetDataBinding(dbDataSetRoutine,
"rtnmain")
At which point, the data is loaded. No problems there.

Then, I'll add a row to the datagrid. I press the UPDATE button. The
following code is executed:

dbBuilderRoutine.GetUpdateCommand()
dbAdapterRoutine.Update(dbDataSetRoutine)

The result is an error.

An unhandled exception of type 'System.NullReferenceException'
occurred in system.data.dll

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

Every example I've checked follows this process. Can anyone point out
what I'm doing wrong, or perhaps an alternative way of updating the
table?

Another question:: do I have to re-fill the dataset before trying to
update?

Thanks!
Tom Junior

[TBass]

On Dec 17, 12:21 am, "Cor Ligthert[MVP]" <[e-mail]>
wrote:

Hi,

I don't see real errors however not needed in your code is
- the setting to nothing,
- dbBuilderRoutine.GetUpdateCommand()
(The latter can be the reason of your error, I have never used that.)

Cor

"TBass" <[e-mail] in berichtnews:f3**********************************@e 23g2000prf.googlegroups.com...

Hi,

We have a process management system in VB .NET. It usually used text
files, but I'm trying to switch to using access database files and
ADO.

I made a form with a datagrid and a few buttons.

I define the variables for this program as follows:

Imports System.Data.OleDb

Private dbConnRoutine As OleDbConnection = Nothing
Private dbAdapterRoutine As OleDbDataAdapter = Nothing
Private dbDataSetRoutine As DataSet = Nothing
Private dbBuilderRoutine As OleDbCommandBuilder = Nothing

When they press the LOAD button, the following is executed:

Dim connString As String
Dim sql As String

connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + FileName
dbConnRoutine = New OleDbConnection(connString)

sql = "SELECT * FROM rtnmain"
Dim objCommand As New OleDbCommand(sql, dbConnRoutine)

dbAdapterRoutine = New OleDbDataAdapter(objCommand)
dbAdapterRoutine.TableMappings.Add("Table", "rtnmain")
dbBuilderRoutine = New
OleDbCommandBuilder(dbAdapterRoutine)

dbDataSetRoutine = New DataSet("rtnmain")

dbAdapterRoutine.Fill(dbDataSetRoutine, "rtnmain")
gridRoutine.DataSource =
dbDataSetRoutine.Tables("rtnmain")
gridRoutine.SetDataBinding(dbDataSetRoutine,
"rtnmain")

At which point, the data is loaded. No problems there.

Then, I'll add a row to the datagrid. I press the UPDATE button. The
following code is executed:

dbBuilderRoutine.GetUpdateCommand()
dbAdapterRoutine.Update(dbDataSetRoutine)

The result is an error.

An unhandled exception of type 'System.NullReferenceException'
occurred in system.data.dll

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

Every example I've checked follows this process. Can anyone point out
what I'm doing wrong, or perhaps an alternative way of updating the
table?

Another question:: do I have to re-fill the dataset before trying to
update?

Thanks!
Tom Junior- Hide quoted text -

- Show quoted text -

Neither of those changes solved the problem. In the end, I had to go
with a rather complicated solution where I wrote a seperate class to
deal with the database interaction, and I set the datagrid to read
only, using buttons and forms for adding/editing rows. That has worked
flawlessly.

Thanks for the feedback.
T