Please Heeelp I don't know what's wrong

[fiaolle]

Hi
I'm new to ADO in Visual Basic.Net and have problems with updating the
DataSet. I'm using the code below, but after I have used the DataAdapter's
Update metod nothing happens to the Dataset OR the database (the record is
not deleted). Before I used the ExecuteNonQuery, then the record was gone in
the database but the record was still in the DataSet. I were told not to use
the ExecuteNonQuery, so I commented it.

I have seen example where I should use Me.OleDbDAFilmKat.Fill(DSFilm,
"Film") instead, but it didn't help. I have also seen articles there I
should use this code
Dim oRow as DataRow
oRow = Me.DSFilm.Tables("Film").Rows(0)
oRow.Delete()
Then after that, use the DeleteCommand for the DataAdapter.
Do I have to do this above to delete a row in the Dataset, then use the
DataAdapter's DeleteCommand to delete a row in the Database. Isn't there
another way.



Dim OleDbDAFilmKat As OleDb.OleDbDataAdapter

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles MyBase.Load
Dim comText As New OleDb.OleDbCommand
Dim SQL As String = "SELECT Film.Nr, Film.Titel, Film.KategoriID FROM Film
ORDER BY Film.Nr"
OleDbDAFilmKat = New OleDb.OleDbDataAdapter(SQL, OleDbConn)
comText.Connection = OleDbConn
comText.CommandText = "DELETE FROM Film WHERE (Nr = ?)"
Me.OleDbDAFilmKat.DeleteCommand = comText
Me.OleDbDAFilmKat.DeleteCommand.Parameters.Add(New
Me.OleDbDAFilmKat.DeleteCommand.Parameters.Add(New
System.Data.OleDb.OleDbParameter("@Original_Nr",
System.Data.OleDb.OleDbType.Integer, 4))
OleDbDAFilmKat.Fill(DSFilm, "Film")
end sub

Private Sub cmdDel_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdDel.Click
Me.OleDbConn.Open()
Me.OleDbDAFilmKat.DeleteCommand.Parameters(0).Value = CInt(Me.txtNr.Text)
'Me.OleDbDAFilmKat.DeleteCommand.ExecuteNonQuery()
Me.OleDbDAFilmKat.Update(DSFilm, "Film")
DSFilm.AcceptChanges()
MaxRows = DSFilm.Tables("Film").Rows.Count
Me.OleDbConn.Close()
End Sub

Please help!!!!!!!!!!!!!!!!!!!
I don't know what's wrong

Fia

 

[William \(Bill\) Vaughn]

Consider that a DataTable is simply a cache of rows from a database table
(or a SELECT product). If you plan to use the DataAdapter (or TableAdapter)
Update method to manage the rows in the DataTable and the database you need
to configure the action commands (UpdateCommand, DeleteCommand,
InsertCommand) so that the Update method can make appropriate changes as it
finds changed, deleted or new rows in the DataTable. Yes, it's perfectly
okay to delete one or more rows by using ExecuteNonQuery DML queries but
you'll have to use the Delete (or Remove) method on the DataTable Rows
collection to keep the DataTable in sync.

I discuss this in detail in my new book.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[fiaolle]

What title will your new book have and when will it come or if it's already
here?

 

[Bart Mermuys]

Hi,

Hi
I'm new to ADO in Visual Basic.Net and have problems with updating the
DataSet. I'm using the code below, but after I have used the DataAdapter's
Update metod nothing happens to the Dataset OR the database (the record is
not deleted). Before I used the ExecuteNonQuery, then the record was gone
in
the database but the record was still in the DataSet. I were told not to
use
the ExecuteNonQuery, so I commented it.

I have seen example where I should use Me.OleDbDAFilmKat.Fill(DSFilm,
"Film") instead, but it didn't help. I have also seen articles there I
should use this code
Dim oRow as DataRow
oRow = Me.DSFilm.Tables("Film").Rows(0)
oRow.Delete()
Then after that, use the DeleteCommand for the DataAdapter.
Do I have to do this above to delete a row in the Dataset, then use the
DataAdapter's DeleteCommand to delete a row in the Database. Isn't there
another way.



Dim OleDbDAFilmKat As OleDb.OleDbDataAdapter

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs)
Handles MyBase.Load
Dim comText As New OleDb.OleDbCommand
Dim SQL As String = "SELECT Film.Nr, Film.Titel, Film.KategoriID FROM Film
ORDER BY Film.Nr"
OleDbDAFilmKat = New OleDb.OleDbDataAdapter(SQL, OleDbConn)
comText.Connection = OleDbConn
comText.CommandText = "DELETE FROM Film WHERE (Nr = ?)"
Me.OleDbDAFilmKat.DeleteCommand = comText
Me.OleDbDAFilmKat.DeleteCommand.Parameters.Add(New
Me.OleDbDAFilmKat.DeleteCommand.Parameters.Add(New
System.Data.OleDb.OleDbParameter("@Original_Nr",
System.Data.OleDb.OleDbType.Integer, 4))
OleDbDAFilmKat.Fill(DSFilm, "Film")
end sub

Private Sub cmdDel_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdDel.Click
Me.OleDbConn.Open()
Me.OleDbDAFilmKat.DeleteCommand.Parameters(0).Value = CInt(Me.txtNr.Text)
'Me.OleDbDAFilmKat.DeleteCommand.ExecuteNonQuery()
Me.OleDbDAFilmKat.Update(DSFilm, "Film")
DSFilm.AcceptChanges()
MaxRows = DSFilm.Tables("Film").Rows.Count
Me.OleDbConn.Close()
End Sub

- Don't use the DataAdapter's DeleteCommand directly, the DataAdapter will
use it. First make sure you have added the right parameter, it needs to be
mapped to a DataColumn in the DataTable so the DataAdapter can get the
values. ( Note that if you don't want to create the Delete, Update and
Insert command yourself you could use the OleDbCommandBuilder instead, but
only if the select query is simple.)

.....
' last parameter is a DataColumn name, parameters must added
' in the order they appear in the sql
Dim p1 As New OleDbParameter("?", System.Data.OleDb.OleDbType.Integer, 4,
"Nr")
p1.SourceVersion = DataRowVersion.Original
Me.OleDbDAFilmKat.DeleteCommand.Parameters.Add(p1)
.....

- Next find the row, flag the row for deletion and call Adapter.Update, no
need to call AcceptChanges here, eg:

Dim dr As DataRow
dr = DSFilm.Tables("Film").Rows.Find( CInt( Me.txtNr.Text ) )
dr.Delete()
Me.OleDbDAFilmKat.Update(DSFilm, "Film")

HTH,
Greetings

 

[William \(Bill\) Vaughn]

Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). It's been
on the shelves for a few months so it should be easy to find anywhere in the
world.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Cor Ligthert [MVP]]

fiaolle,

Your procedure is quiet simple, you can let AdoNet in that case let
dynamically (I write express dynamically)
create the commands with the oledb commandbuilder. Put a row like this
somewhere(after that the dataadapter is constructed). And you are ready.

dim cmb = oledb.oledbcommandbuilder(OleDbDAFilmKat)

You can as well use the generators and copy and paste from that the code
that is generated into your code.

Cor