Home All Groups Group Topic Archive Search About

What am I doing wrong - Trying to update

microsoft.public.dotnet.languages.vb
Author
6 Apr 2005 3:19 PM
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

Author
6 Apr 2005 4:23 PM
Kerry Moorman
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


Show quoteHide quote
"Gary Paris" wrote:

> 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
>
>
>
Author
6 Apr 2005 4:26 PM
Cor Ligthert
Kerry,

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

:-)

Cor
Author
6 Apr 2005 4:23 PM
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

Show quoteHide quote
> 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)

Show quoteHide quote
>            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
>
>
Author
6 Apr 2005 4:41 PM
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

Show quoteHide quote
"Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message
news:OW%23fSUsOFHA.2736@TK2MSFTNGP09.phx.gbl...
> 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)
>
>>            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
>>
>>
>
>
Author
6 Apr 2005 4:49 PM
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
Author
6 Apr 2005 4:58 PM
Gary Paris
Cor,

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

Gary

Show quoteHide quote
"Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message
news:%23OEn0isOFHA.2748@TK2MSFTNGP09.phx.gbl...
> Gary,
>
> See the answer from Kerry what means
>
>              ds = New DataSet
>            da.Fill(ds, "Employees")
>             dim cmb as new OleDb.OleDbCommandbuilder(da)
>
> Cor
>
Author
6 Apr 2005 5:11 PM
Cor Ligthert
Gary

This ones too

cn = New OleDb.OleDbConnection(strConn)

da = New OleDb.OleDbDataAdapter(strSQL, strConn)

Cor
Author
6 Apr 2005 5:55 PM
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


Show quoteHide quote
"Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message
news:eKNF2usOFHA.3668@TK2MSFTNGP14.phx.gbl...
> Gary
>
> This ones too
>
> cn = New OleDb.OleDbConnection(strConn)
>
> da = New OleDb.OleDbDataAdapter(strSQL, strConn)
>
> Cor
>
>
Author
6 Apr 2005 6:24 PM
Cor Ligthert
Gary,

Sorry

da.Update(ds, "Employees")

Cor
Author
6 Apr 2005 6:47 PM
Gary Paris
Thanks Cor for your help in this.  I will keep studying and hopefully I'll
be a real programmer one day!

Gary

Show quoteHide quote
"Cor Ligthert" <notmyfirstn***@planet.nl> wrote in message
news:OJ8F1XtOFHA.3788@tk2msftngp13.phx.gbl...
> Gary,
>
> Sorry
>
> da.Update(ds, "Employees")
>
> Cor
>
>
Author
6 Apr 2005 4:35 PM
brix_zx2
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)

---------------------------------------
That will get your data adapter and dataset going.... the rest I'm having a
bit of trouble reading.......
Show quoteHide quote
"Gary Paris" wrote:

> 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
>
>
>
Author
6 Apr 2005 4:46 PM
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

Show quoteHide quote
"brix_zx2" <brix***@discussions.microsoft.com> wrote in message
news:67111A61-16DE-44DC-B2E5-16D5C38B8E98@microsoft.com...
> 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)
>
> ---------------------------------------
> That will get your data adapter and dataset going.... the rest I'm having
> a
> bit of trouble reading.......
> "Gary Paris" wrote:
>
>> 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
>>
>>
>>



Post Other interesting topics
Microsoft not content with "dissing" just the Classic VB Developer Army....
Where to find good icons?
Setup project registery values with app install folder in them
Event for PgUp and PgDn on Form
How to get current free physical memory ?
Set textbox1.enabled = false inside a module?
modify HTML in code behind
How to expose a structure to VB6 from VB.NET?
How to create a zoom feature in Vb.Net
'' Could not be set on property 'Items' - Error