DataGridView, SqlCE and Adding New Rows...

[JaedenRuiner]

Okay,

So i've got a form, with a DataGridView, and all my tableadapters and bindingsource, with a listbox that is populated at load with all my tables from my DataSet. This part works great. You click on the table in the lost box and I set the appropriate datamember for the BindSource to the right table from my DataSet and voila, the DataGridView now shows the table in full glory.

But as with many database "grids" and what not, there is that last row with the asterisk (*) in the row header, and there I can start adding new rows. But, when I'm done adding the data into the row, it doesn't update the DataSet (or maybe it does) and thus subsequently does not update the actual database table. So when i click on a table, load it into the DGV, enter in a new row or two, then click to another table and back, the table i "supposedly" changed hasn't altered in any way.

So, my question is in what events or what not is it pertinent to listen to the DGV, the BindingSource, or even the dataSet partial class you can create to determine that in the bound control i have added a row, and thus can either:

• Commit the new row to the database
• Cancel the added data

I"m still a bit new to the the whole linking a database into a VB application in such a manner. I'm much more familiar with oracle sql and running through a sql client like sql*plus or pl/sql developer, so the linking of controls to the database connection is a bit strange still. I'm not sure i"m even going to allow the DGV to directly edit the tables, i'm just curious right now (as a process of learning) how I would do it, given a DataSource and DataMember for the DGV pointing to a DataSet with DataTable.
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

ps
I've tested these methods:

Partial Class dsRexamShip
   Partial Class tblLocationDataTable

      Private Sub tblLocationDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
         MsgBox("Adding a new row?", , "RexamClean")
      End Sub
   End Class
end class

The above is the vb source for the stongly typed DataSet created by the XSD file designer into the code for additional handling. That even fires the moment I enter (click on) the "new" row on the DGV.

   Private Sub dgvResult1_UserAddedRow(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowEventArgs) Handles dgvResult1.UserAddedRow
      SQLText.AppendText("User Added Row")
   End Sub

This event fires the moment i type in the dgv's "new row". I guess i just need some help in tying together the events with the actions necessary to take the new data and insert it into the table.
Thanks

 

[cjard]

Can you re-read your post, with the knowledge that a DataTable in client memory is in no way guaranteed to be representative or linked to a server side database table and flatten the ambiguities?

You make edits to a local DGV, and then complain that a table is not updated, but it is not clear what you mean by "table"

The most common mistake newbies make is in being able to see soemthing called EMP in their dataset, and assume that that IS the EMP table in the database. It isnt. Its a local data container suitable for temporarily caching rows from the aforementioned table, but just like a bucket is not a reservoir, when you fill a bucket with water from the tap (from the reservoir), then put poison in the bucket it does not poison the reservoir. You have to carry the water back to the reservoirt and tip it in in order to poison the reservoir. How cruel

 

[JaedenRuiner]

You make edits to a local DGV, and then complain that a table is not updated, but it is not clear what you mean by "table"

Then perhaps I was not clear. I am very well aware that the dataset is not the Database "Table", it is a local "in-memory" copy. But that was sort of the whole point to the question.

There are countless events to the DGV and to the dataset that are in many ways interlinked. NOw, take for example the "not null" parameter to a column in a database table. The DataSet and subsequent bound DGV reflect that setting so when I try to create a new row but don't populate all the fields that are marked "not null" the app creates an exception.

My question is 2 fold

1. Where. At which events are these validations made and thus I can trap them more pleasantly for my data entry validation.
2. How To. Once I've determined:
   • A new row has been added and is appropriately populated
   • A row has been deleted
   • A row has been updated
   Which methods are most appropriate to use with the DataSet to "commit" the new data. Is the new data entered into the DGV already inserted into the DataSet, or do I need to do that step too?

Yes I am a noob, but my noobiness is all regarding the syntax and usage of components in VB.Net, not the architectural design of programming languages. See, I don't know that if you edit the DGV it populates the DataSet, and with the automatic code generation, there are multiple Update commands as well as TAManager that has UpdateAll* methods as well. Which ones do what? Is there a: DGV_OnDataFullEnteredAndValided event where I can Execute: MyDataSet.PleaseCommitAllMyChangesNow() method?
*schmile*

In my previous experience I have not had to use DataSets, I used direct SQL command interpreters, so I'd execute

INSERT INTO {Statement} ; Commit;

I'm looking for the information on how,when,where that occurrs between the DGV, DataSet, and Linked Database.dbo.Table.

Thanks, and sorry for the confusion.

 

[cjard]

[*]Where. At which events are these validations made and thus I can trap them more pleasantly for my data entry validation.

I'd nip into VS options and disable the Just My Code option, that way you can debug within code the designer has generated too

Then, on the Debug menu, choose Exceptions... (you might have to customize your menu bars to see this, some user in VB layout dont seme to have it - i'm a c# guy) and elect to see exceptions as soon as they are thrown rather than when they bubble up unhandled. You'll find the code that throws the exception, inside the designer generated code. As to whether you want to catch it yourself first.. there is a Validate event, but I've never hooked it

[*]How To. Once I've determined:

• A new row has been added and is appropriately populated
• A row has been deleted
• A row has been updated

• 
  Rows have two repevant properties, DataRowState and DataRowVersion . The relevant property for most things is teh .RowState of the DataRow. RowVersion comes in when..well.. read MSDN for the explanation. Its far more comprehensive than I am.
  You can read about these in MSDN
  
  
  

  Which methods are most appropriate to use with the DataSet to "commit" the new data.

  As a bucket of data, the bucket itself has nothing to do with deliveringthe data or sending it back. Thats whatthe tableadapter is for. The most apt (and only) method is the Update() method. GetChanges() can produce a selection of rows that meet only a certain RowState criteria but generally i avoid using these rows in update operations because you can go out of sync if the database calculates new values for some columns
  
  e.g. I add a row and the PK is -1. The db will see this and calculate the next id in the sequnce. In doing so, .net will requery the new id and amend the datatable passed in. If I used GetChanges, I got a cloned row with -1. the DB updated my -1 to 273473, but i now have no way of tying that row back to the original it was cloned from, so I cannot merge the changes into my bound datatable. Ergo i tend to just Update() the bound datatable and any values the db calcs will show up immediately
  
  
  

  Is the new data entered into the DGV already inserted into the DataSet, or do I need to do that step too?

  In MVC there is a Model, View and Controller. THe model holds data and is the DataTable within the DataSet. DS dont hold data directly, and i'm a stickler for using the correct terms to avoid confusion, so please remember this
  The DGV forms the view and the controller.. Mostly it is a View but it allows for editing and is therefore a Controller
  
  Edits made in the DGV are sent to the model as soon as the user finishes editing the value and moves out of the cell. They are NOT updated every time the user makes an edit
  
  i.e. the Cell says "hello" and the user edits it to say "hello world". Only one update is made to the model, the model is not updated with "hello ", "hello w", "hello wo", "hello wor" ... every time they press a key. Edits are committed once subject to validation succeeding. If validation fails, focus does not leave the cell/editor component
  
  

  Yes I am a noob, but my noobiness is all regarding the syntax and usage of components in VB.Net, not the architectural design of programming languages. See, I don't know that if you edit the DGV it populates the DataSet, and with the automatic code generation, there are multiple Update commands

  I dont use 2008 yet, though we have more than one Update().. the concept of overloading is the key ; where multiple methods have the same name, but differ by their parameter signature. typically only one provides functionality and the others are sugar to help you in common situations. You can see the various overloads in the MyDataSet.Designer.vb file. Check it out
  
  

  as well as TAManager that has UpdateAll* methods as well. Which ones do what?

  The idea of a method name being a verb is that it performs the obvious action. Overloads should NOT typically perform different actions... i.e we should have a method called Run() that when passed a Nose object drips snot everywher, when passed a Bath object fills it with water and when passed a Television object it makes its way at high speed to the local pawn shop
  
  Update() always persists changes present in the argument, back to the DB. IF you pass a DataRow, only that ro is synced. If you pass a Table, all rows in the table that have changed, are synced..
  
  
  
  

  Is there a: DGV_OnDataFullEnteredAndValided event where I can Execute: MyDataSet.PleaseCommitAllMyChangesNow() method?

  Noo.. you wouldnt send updates to the database every time a validation succeeded, because your network would die in update traffic. Probably. Instead when you click the Save button, you would validate the form, endedit the bindingsources in use (making sure any active edits are sent into the model) and then Update() the db from the model. the designer does this code for you and you can read it in your form (not in the .designer.vb file). Follow DW2
  
  
  

  In my previous experience I have not had to use DataSets, I used direct SQL command interpreters, so I'd execute
  

  INSERT INTO {Statement} ; Commit;

  I'm looking for the information on how,when,where that occurrs between the DGV, DataSet, and Linked Database.dbo.Table.

  When you call Update() and pass in a datatable, conceptually the TA does:
  
  for each row in the table
  -if the rowstate is added
  --load the data values into an INSERT query and execute
  -if the rowstate is modified
  --load the values into a UPDATE query and execute
  -if the rowstaet is deleted
  --load the vals into a DELET..
  
  So your INSERT INTO is stil lbeing done, but not by you. You can do it if you really want to, because the ta has (if GenerateDBDirect was ticked) an INSERT() method that takes all the column values to insert.
  
  -
  
  Another source of confusion for newbies is MS choice to call the tableadpetr method Update().
  
  This has NOTHING to do with SQL UPDATE query in the sense that you know. Update() selectively calls I, U or D based on the rowstate. It should have been called SyncToDB() or something to remove the confusion that it is only for updating existing data

 

[filespace]

Me.DBTableAdapter.Update(Me.JukeboxDataSet.Customers)

i don't know id this is what you are asking for but this is how i save my changes that i make in the gridview...