Unable to delete row

[travlintom]

Hi. I'm new to VB.net and am used to working with recordsets (much
easier). I have a datagrid that, when the user double clicks on a row,
I am giving them a chance to delete it. I thought this would be simple
but it's taking me forever. I am getting an error when I try to delete
the record. I can't figure out what I'm doing wrong.
The error is "Object reference not set to an instance of an object".
None of the incredibly overpriced books I have seem to be of any help.
Someone's knowledge would be greatly appreciated.


Private Sub dgProjItems_DoubleClick(ByVal sender As Object, ByVal e As
System.EventArgs) Handles dgProjItems.DoubleClick
Dim CurItemID As String
CurItemID = dgProjItems.Item(dgProjItems.CurrentRowIndex(), 0)

Dim strConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=C:\Databases\NewEstimate.mdb;"
Dim cn As New OleDbConnection(strConnectionString)
Dim strSQLl As String = "Select * from ProjectsItems Where
ItemID = '" & CurItemID & "'"
Dim objCommand1 As New OleDbCommand(strSQLl, cn)
Dim da As New OleDbDataAdapter(objCommand1)
Dim ds As New DataSet

Try
cn.Open()

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("ProjectItems").Rows(0).Delete() 'error here
da.Update(ds, "ProjectItems")

Me.dgProjItems.DataSource = ds.Tables("ProjectItems")

Catch OleDbExceptionErr As OleDb.OleDbException
MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
End Try
End Sub

 

[Chris]

Hi. I'm new to VB.net and am used to working with recordsets (much
easier). I have a datagrid that, when the user double clicks on a row,
I am giving them a chance to delete it. I thought this would be simple
but it's taking me forever. I am getting an error when I try to delete
the record. I can't figure out what I'm doing wrong.
The error is "Object reference not set to an instance of an object".
None of the incredibly overpriced books I have seem to be of any help.
Someone's knowledge would be greatly appreciated.


Private Sub dgProjItems_DoubleClick(ByVal sender As Object, ByVal e As
System.EventArgs) Handles dgProjItems.DoubleClick
Dim CurItemID As String
CurItemID = dgProjItems.Item(dgProjItems.CurrentRowIndex(), 0)

Dim strConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=C:\Databases\NewEstimate.mdb;"
Dim cn As New OleDbConnection(strConnectionString)
Dim strSQLl As String = "Select * from ProjectsItems Where
ItemID = '" & CurItemID & "'"
Dim objCommand1 As New OleDbCommand(strSQLl, cn)
Dim da As New OleDbDataAdapter(objCommand1)
Dim ds As New DataSet

Try
cn.Open()

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("ProjectItems").Rows(0).Delete() 'error here
da.Update(ds, "ProjectItems")

Me.dgProjItems.DataSource = ds.Tables("ProjectItems")

Catch OleDbExceptionErr As OleDb.OleDbException
MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
End Try
End Sub

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

This error means that an object you are trying to do an operation
against does not exist (is nothing). so figure out which object is empty.

dim DT as datatable = ds.Tables("ProjectItems")
dim DR as datarow = DT.Rows(0)
DR.Delete()

My guess is that your datatable isn't named ProjectItems so that is
returning nothing.

Chris

 

[Guest]

Your code isn't going to work because on one line you create a new empty
dataset:

Dim ds As New DataSet

And then on another line, you are attempting to delete from this empty
dataset:

ds.Tables("ProjectItems").Rows(0).Delete() 'error here

You're never calling Fill on your OleDbDataAdapter to fill this dataset so
that you can delete the row.

 

[travlintom]

Thank you both for your quick reply.
Scott........I added the fill method and now get the error "An
unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll"

Here's the revised code:

Dim strSQLl As String = "Select * from ProjectItems Where ItemID = '" &
CurItemID & "'"
Dim objCommand1 As New OleDbCommand(strSQLl, cn)
Dim da As New OleDbDataAdapter(objCommand1)
Dim ds As New DataSet
da.Fill(ds, "ProjectItems")
Try
cn.Open()

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("ProjectItems").Rows(0).Delete()

da.Update(ds, "ProjectItems")

Me.dgProjItems.DataSource = ds.Tables("ProjectItems")

Catch OleDbExceptionErr As OleDb.OleDbException
MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
End Try

Chris........The table name is correct. Thanks.

 

[Chris]

Thank you both for your quick reply.
Scott........I added the fill method and now get the error "An
unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll"

Here's the revised code:

Dim strSQLl As String = "Select * from ProjectItems Where ItemID = '" &
CurItemID & "'"
Dim objCommand1 As New OleDbCommand(strSQLl, cn)
Dim da As New OleDbDataAdapter(objCommand1)
Dim ds As New DataSet
da.Fill(ds, "ProjectItems")
Try
cn.Open()

Dim cb As New OleDb.OleDbCommandBuilder(da)

ds.Tables("ProjectItems").Rows(0).Delete()

da.Update(ds, "ProjectItems")

Me.dgProjItems.DataSource = ds.Tables("ProjectItems")

Catch OleDbExceptionErr As OleDb.OleDbException
MessageBox.Show(OleDbExceptionErr.Message, "Access SQL")
End Try

Chris........The table name is correct. Thanks.

what does oledbexceptionerr.message say? It will give more detail about
what the error is.

Chris

 

[travlintom]

I ran through all the code. That's the only error I get.

 

[Cor Ligthert [MVP]]

Travlintom,

At least do you consequently have to close something when you open
something. Closing and opening is in fact not needed for a dataadapter
(however better when you use more tables), because the dataadapter does that
generic for you.

Your block could be this both for the Fill and the Update
Try
open connection
Try
fill or update
Catch error
'do action for the Try or Update
End Try
Catch error
'do action for the Open
Finally
Close connection
End Try

As I wrote, you can not use the open. In Net 2.0 you can than use as well
"using".

I hope this helps,

Cor

 

[travlintom]

Thanks all. I found the problem. It was actually the SQL statement was
looking for a string when It should have been an integer. I made the
change and it works fine. Thanks for all your help.