What am I doing wrong - Trying to update

[Gary Paris]

I have enclosed the sample code that I created. I want to read in employee
data, and modify a few fields. I have tried to globally declare the objects
that I need but I still am having problems. I want to update in a seperate
subroutine and seem to have problems. HELP please.

-------------------------------------------
Public Class Form1

Inherits System.Windows.Forms.Form
Public cn As OleDb.OleDbConnection
Public ds As DataSet
Public da As OleDb.OleDbDataAdapter
Public rowEmployee As DataRow

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Try
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\adonetsbs\SampleDBs\nwind.mdb;"

Dim cn As New OleDb.OleDbConnection(strConn)

Dim strSQL As String
strSQL = "SELECT EmployeeID, FirstName, LastName, Address, City,
Region, " & _
"PostalCode from Employees ORDER BY LastName, FirstName"

Dim da = New OleDb.OleDbDataAdapter(strSQL, strConn)
Dim ds As New DataSet

da.Fill(ds, "Employees")

Dim tbl As DataTable = ds.Tables(0)

'rowEmployee = New DataRow
rowEmployee = tbl.Rows(0)
txtFirstName.Text = rowEmployee("FirstName")
txtLastName.Text = rowEmployee("LastName")
txtAddress.Text = rowEmployee("Address")

Catch ex As Exception
MessageBox.Show(ex.Message & " :: " & ex.Source)
Finally
End Try
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
Try
rowEmployee("LastName") = txtLastName.Text
da.Update(ds)

Catch ex As Exception

MessageBox.Show(ex.Message & " :: " & ex.Source)

End Try

End Sub
End Class
---------------------------------------

When it runs I get the following error:

"Object reference not set to an instance of an object."

on the da.Update(ds) line in the btnUpdate_Click routine.

HELP.

Thanks,

Gary

 

[Guest]

Gary,

In Form1_Load you are creating local variables such as da, ds, etc.

Instead, you need to use the "global" or form level variables that you have
creted in the form's declarations section.

In Form1_Load, you just need to do something like:

ds = New DataSet

instead of dimensioning the variable again.

Kerry Moorman

 

[Cor Ligthert]

Gary,

There is a lot still not right, however let us first take this problem.
Your error message is strange for me, that I don't directly see.
However you needs an update command in the dataadapter.
That is easy to do for a simple select statement as you have.
See the code I have pasted inline (one row)
And try than again. I now don't understand the error you get, however that
is at least needed.

When you have it running have than a look at
Databinding
\\\
cma = DirectCast(BindingContext(dataset1.Tables(0)), CurrencyManager)
textbox1.DataBindings.Add(New Binding("Text", dataset1.Tables(0),
"LastName"))
///
The cma.position gives you than the row that is used and you can affect that
by using buttons on your form.

I hope this helps,

Cor

Public Class Form1

Inherits System.Windows.Forms.Form
Public cn As OleDb.OleDbConnection
Public ds As DataSet
Public da As OleDb.OleDbDataAdapter
Public rowEmployee As DataRow

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Try
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\adonetsbs\SampleDBs\nwind.mdb;"

Dim cn As New OleDb.OleDbConnection(strConn)

Dim strSQL As String
strSQL = "SELECT EmployeeID, FirstName, LastName, Address,
City, Region, " & _
"PostalCode from Employees ORDER BY LastName, FirstName"

Dim da = New OleDb.OleDbDataAdapter(strSQL, strConn)
Dim ds As New DataSet

da.Fill(ds, "Employees")

dim cmb as new OleDb.OleDbCommandbuilder(da)

 

[Cor Ligthert]

Kerry,

That is it, however Gary needs my answer as well for his next problem.



Cor

 

[Guest]

Okay, I'm going to try for a bit of a rewrite.

--------------------------------------------------------
Dim da As System.Data.OleDb.OleDbDataAdapter
Dim cn As System.Data.OleDb.OleDbConnection

cn = New System.Data.OleDb.OleDbConnection ( _
"provider=Microsoft.Jet.OLEDB.4.0; & _
data source=C:\adonetsbs\SampleDBs\nwind.mdb;"

da = New System.Data.OleDb.OleDbDataAdapter( _
"SELECT EmployeeID, FirstName, LastName, Address, City, Region, PostalCode
FROM Employees ORDER BY LastName, FirstName"

Dim ds As System.Data.DataSet
ds = New System.Data.DataSet

cn.Fill(ds)

 

[Gary Paris]

Cor,

I put the
dim cmb as new OleDb.OleDbCommandbuilder(da)
line in as you suggested. Still got the same error. Can you explain the
other command you described and where it goes? I don't understand.

Thanks,

Gary

 

[Gary Paris]

This is OK, but how can I do an update from another subroutine when the
connection and dataadapter are not global? I want to click on a button to
do the update.

Thanks

Gary

 

[Cor Ligthert]

Gary,

See the answer from Kerry what means

ds = New DataSet
da.Fill(ds, "Employees")
dim cmb as new OleDb.OleDbCommandbuilder(da)

Cor

 

[Gary Paris]

Cor,

I made the change and I still get the object not referenced error.

Gary

 

[Cor Ligthert]

Gary

This ones too

cn = New OleDb.OleDbConnection(strConn)

da = New OleDb.OleDbDataAdapter(strSQL, strConn)

Cor

 

[Gary Paris]

Cor,

I made the changes you suggested but I get an error message when the update
is called: Update unable to fine TableMapping ['Table'] or DataTable
'Table'

Here is the code as it looks now:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Try
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\adonetsbs\SampleDBs\nwind.mdb;"

cn = New OleDb.OleDbConnection(strConn)

Dim strSQL As String
strSQL = "SELECT EmployeeID, FirstName, LastName, Address, City,
Region, " & _
"PostalCode from Employees ORDER BY LastName, FirstName"

da = New OleDb.OleDbDataAdapter(strSQL, strConn)
ds = New DataSet

da.Fill(ds, "Employees")

Dim cmb As New OleDb.OleDbCommandBuilder(da)

Dim tbl As DataTable = ds.Tables(0)

'rowEmployee = New DataRow
rowEmployee = tbl.Rows(3)
txtFirstName.Text = rowEmployee("FirstName")
txtLastName.Text = rowEmployee("LastName")
txtAddress.Text = rowEmployee("Address")

Catch ex As Exception

MessageBox.Show(ex.Message & " :: " & ex.Source)

Finally
End Try
End Sub

 

[Cor Ligthert]

Gary,

Sorry

da.Update(ds, "Employees")

Cor

 

[Gary Paris]

Thanks Cor for your help in this. I will keep studying and hopefully I'll
be a real programmer one day!

Gary