Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Value can not be null ???

Why does my btnDelete keeps getting an error  - Value can not be null ???

----------------------------------------
Imports System.Data.OleDb


Public Class Form1
    Inherits System.Windows.Forms.Form


   

    Dim myConnection As OleDbConnection

    Dim myCommand As OleDbCommand

    Dim datatable As DataTable
    Dim currRec As Integer = 0
    Dim totalRec As Integer = 0







   Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Try

               myConnection = New OleDbConnection(ConnectionString)
          myConnection.Open()
            myCommand = New OleDbCommand("insert into CarTable([Year], Make, Model, [Type]) values ('" & Me.txtYear.Text & "' , ' " & Me.txtMake.Text & " ' , '" & Me.txtModel.Text & " ',' " & Me.txtType.Text & " ') ",myConnection)
                myCommand.ExecuteNonQuery()
                 MsgBox("ok")


        Catch ex As OleDbException

                 MsgBox(ex.ToString)
     Finally
          If Not myConnection Is Nothing Then
                      If myConnection.State = ConnectionState.Open Then
                    myConnection.Close()
                      End If
             End If
           


        End Try

    End Sub


Private Sub btnDelet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelet.Click


        Dim dataAdapter As New OleDbDataAdapter
        Dim row As DataRow = datatable.Rows(currRec)

        row.Delete()
        dataAdapter.Update(dsMydataset, "CarTable")
        dsMydataset.AcceptChanges()
        totalRec -= 1
        currRec = totalRec - 1

        FillControls()



     
    End Sub
   
    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

        Dim commandstring As String = "SELECT ID, Make, Model, Type, Year FROM  CarTable"
        Dim ConnectionString As String = "provider=microsoft.jet.oledb.4.0;" + "data source = E:\car.mdb"
        Dim conn As New OleDbConnection(connectionstring)

        Dim dataAdapter As New OleDbDataAdapter

        dataAdapter.SelectCommand = New OleDbCommand(commandstring, conn)

        Dim myDataset As New DataSet

        dataAdapter.Fill(myDataset, "CarTable")
        datatable = myDataset.Tables("CarTable")
        currRec = 0
        totalRec = datatable.Rows.Count
        FillControls()

        Me.btnForward.Enabled = True
        Me.btnBackWard.Enabled = True




    End Sub


    Private Sub FillControls()

        Me.txtYear.Text = datatable.Rows(currRec)("Year")

        Me.txtMake.Text = datatable.Rows(currRec)("Make")

        Me.txtModel.Text = datatable.Rows(currRec)("Model")

        Me.txtType.Text = datatable.Rows(currRec)("Type")


    End Sub
End Class
0
VBdotnet2005
Asked:
VBdotnet2005
  • 2
1 Solution
 
LordWabbitCommented:
Mmmm, it would appear that either not all your code is shown here so it's a bit hard to determine why you would be getting the error.  on which line is the error occurring, from a first glance I would think it's on the
dataAdapter.Update(dsMydataset, "CarTable")
line.  I'm assuming that dsMydataset, should actually be myDataset.
0
 
SanclerCommented:
>>
Why does my btnDelete keeps getting an error
<<

It's difficult to know where to begin ;-)

        Dim dataAdapter As New OleDbDataAdapter

This line does nothing except tell this sub that the name dataadapter refers, for the purpose of (or within the scope of) this sub, to a New OleDbDataAdapter.  That dataadapter needs a connection, and a Select statement and some update commands before it can actually do anything.  It is never given any of them.

On the assumption that you will only be wanting to delete any record once you have loaded some, you really ought to be using the same dataadapter that you used in your Sub btnLoad_Click.  But that is delcared within, and so just for the purpose of, that Sub.  If you take that line out of that sub, and the same line out of this sub, and put it - just once - at the start of your code, where you have

    Dim myConnection As OleDbConnection

etc. it will be visible, as the same object, to all your subs.

Just creating it and giving it a connection and a Select command (which your Sub btnLoad_Click will then do) will not be enough.  You will also need to give it an Update, an Insert and a Delete command.  You might be able to do this "automatically" by using a command builder.  To do this, you would use code like this

    Dim cb As New OleDbCommandBuilder(dataadapter)

in your Sub btnLoad_Click, after you have given the dataadapter its Select command.  But it might not work.  This is because, in order to work, a Delete and an Update command have to have some means of identifying which record in the database table uniquely matches which record in the application's datatable: that is, a Primary Key.  And it is far from clear from your post that your records have a Primary Key.

But assuming you get this far OK, these two lines

        dataAdapter.Update(dsMydataset, "CarTable")
        dsMydataset.AcceptChanges()

refer to an object - dsMydataset - which this sub can't see.  It doesn't exist.  But even if we adopt LordWabbit's assumption that the reference should really be to myDataset rather than to dsMydataset, this sub still won't be able to see it.  That is because the line declaring/creating it

        Dim myDataset As New DataSet

is within your Sub btnLoad_Click, so it is only visible to, and can only be used for the purposes of, that sub.  It is a "scope" problem again.  And the answer is similar to above.  Move that line to the start of your form with

    Dim myConnection As OleDbConnection

etc.

Apart from that, so far as I can see (although no guarantees), it's all fine ;-)

Roger
0
 
VBdotnet2005Author Commented:
Sancher,

When I move Dim myDataset as New Dataset to the top, I got error

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

Additional information: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

Imports System.Data.OleDb


Public Class Form1
    Inherits System.Windows.Forms.Form


   

    Dim myConnection As OleDbConnection
    Dim myCommand As OleDbCommand
    Dim dsMydataset As New DataSet
    Dim datatable As datatable
    Dim currRec As Integer = 0
    Dim totalRec As Integer = 0

...

 Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click


        Dim ConnectionString As String = "provider=microsoft.jet.oledb.4.0;" + "data source = E:\car.mdb"

        Try

            Dim myConnection As OleDbConnection = New OleDbConnection(ConnectionString)
            myConnection.Open()

            myCommand = New OleDbCommand("insert into CarTable([Year], Make, Model, [Type]) values ('" & Me.txtYear.Text & "' , ' " & Me.txtMake.Text & " ' , '" & Me.txtModel.Text & " ',' " & Me.txtType.Text & " ') ", myConnection)

            myCommand.ExecuteNonQuery()


            myConnection.Close()


        Catch ex As OleDbException

            MsgBox(ex.ToString)


        End Try


    End Sub


 Private Sub btnDelet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelet.Click


        Dim dataAdapter As New OleDbDataAdapter
        Dim row As DataRow = datatable.Rows(currRec)

        row.Delete()
        dataAdapter.Update(dsMydataset, "CarTable")            ---------------error -------------
        dsMydataset.AcceptChanges()
        totalRec -= 1
        currRec = totalRec - 1

        FillControls()



     
    End Sub

Private Sub btnBackWard_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackWard.Click

        currRec -= 1
        If currRec < totalRec Then
            currRec = totalRec - 1

        End If

        FillControls()


    End Sub

    Private Sub btnForward_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnForward.Click

        currRec += 1

        If currRec >= totalRec Then
            currRec = 0

        End If

        FillControls()




    End Sub

 Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

        Dim commandstring As String = "SELECT ID, Make, Model, Type, Year FROM  CarTable"
        Dim ConnectionString As String = "provider=microsoft.jet.oledb.4.0;" + "data source = E:\CS152\HOME WORK\6th\car2.mdb"
        Dim conn As New OleDbConnection(connectionstring)

        Dim dataAdapter As New OleDbDataAdapter

        dataAdapter.SelectCommand = New OleDbCommand(commandstring, conn)

        'Dim myDataset As New DataSet

        dataAdapter.Fill(dsMydataset, "CarTable")
        datatable = dsMydataset.Tables("CarTable")
        currRec = 0
        totalRec = datatable.Rows.Count
        FillControls()

        Me.btnForward.Enabled = True
        Me.btnBackWard.Enabled = True




    End Sub

Private Sub FillControls()

        Me.txtYear.Text = datatable.Rows(currRec)("Year")

        Me.txtMake.Text = datatable.Rows(currRec)("Make")

        Me.txtModel.Text = datatable.Rows(currRec)("Model")

        Me.txtType.Text = datatable.Rows(currRec)("Type")


    End Sub

0
 
SanclerCommented:
What did you do about this bit?

>>
Just creating it and giving it a connection and a Select command (which your Sub btnLoad_Click will then do) will not be enough.  You will also need to give it an Update, an Insert and a Delete command.  You might be able to do this "automatically" by using a command builder.  To do this, you would use code like this

    Dim cb As New OleDbCommandBuilder(dataadapter)

in your Sub btnLoad_Click, after you have given the dataadapter its Select command.  But it might not work.  This is because, in order to work, a Delete and an Update command have to have some means of identifying which record in the database table uniquely matches which record in the application's datatable: that is, a Primary Key.  And it is far from clear from your post that your records have a Primary Key.
<<
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now