|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Please Heeelp I don't know what's wrongI'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 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. -- Show quote____________________________________ 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" <fiao***@telia.com> wrote in message news:c9GCh.34266$E02.13482@newsb.telia.net... > 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 > > What title will your new book have and when will it come or if it's already
here? Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message CInt(Me.txtNr.Text)news:uD$#HmRVHHA.3980@TK2MSFTNGP02.phx.gbl... > 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" <fiao***@telia.com> wrote in message > news:c9GCh.34266$E02.13482@newsb.telia.net... > > 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 = Show quote > > '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 > > > > > > 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. -- Show quote____________________________________ 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" <fiao***@telia.com> wrote in message news:g6HCh.34278$E02.13454@newsb.telia.net... > What title will your new book have and when will it come or if it's > already > here? > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message > news:uD$#HmRVHHA.3980@TK2MSFTNGP02.phx.gbl... >> 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" <fiao***@telia.com> wrote in message >> news:c9GCh.34266$E02.13482@newsb.telia.net... >> > 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 >> > >> > >> >> > > Hi,
Show quote "fiaolle" <fiao***@telia.com> wrote in message - Don't use the DataAdapter's DeleteCommand directly, the DataAdapter will news:c9GCh.34266$E02.13482@newsb.telia.net... > 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 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 Show quote > > Please help!!!!!!!!!!!!!!!!!!! > I don't know what's wrong > > Fia > > 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 Show quote "fiaolle" <fiao***@telia.com> schreef in bericht news:c9GCh.34266$E02.13482@newsb.telia.net... > 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 > >  |
|||||||||||||||||||||||
Other interesting topics