Adding Rows to Data-Bound DataGridView

[jp2msft]

A data-bound DataGridView is wonderfully simple to include with a Windows Form.

Now that all the data is there, how do I go about adding a new record? When
I tried, Visual Studio 2005 Pro gave me an InvalidOperationException, saying,
"Rows cannot be programmatically added to the DataGridView's rows collection
when the control is data-bound."

Am I just not able to add a new record ever if I use a data-bound
DataGridView, or do I need to follow some special steps to un-data-bind it,
add my data, then re-data-bind it?

 

[William Vaughn]

Yup, if it's data bound you just add a row to the DataTable (or structure)
to which it's bound.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[jp2msft]

What page is that described in your book?

Is there a cut-and-dry way of showing me how that's done? Your book is
great, but 1000 pages just takes me too long to find what I need. (Are you
planning a Cookbook in the future?)

From what I understand, once the underlying structure has rows added or
modified, then I need to create a DataSet of changes made to the main
DataSet, updated my TableAdapter with these changes, accept the changes
through the main DataSet, then update TableAdapter again.

Does all of that sound confusing to you, too?

How is all of this better than just creating a new SqlConnection with an
INSERT Command? Microsoft did a lot of work with this, but I think it is
making things more difficult than they need to be. Is that just me?

 

[William Vaughn]

Chapter 7 discusses these issues. Start on page 468.
No, when you have created a DataSet (strongly typed or untyped) and bound it
to a complex bound control like a DataGridView, once you change the Rows
collection members (add or delete rows, or change values in existing rows)
the binding mechanism posts these changes to the bound control. You might
have to execute EndEdit to get this operation to fire but it's posted
automatically. You should not have to call "AcceptChanges" in 90% of the
cases when handling data. AcceptChanges is used to tell ADO that the changes
marked in the DataTable/DataSet have been processed through independent
means. If you use the DataAdapter or generated TableAdapter Update method,
"accept changes" is invoked for you on a row-by-row basis as ADO walks
through the update process posting client-side changes to the server
database.

There is an entirely different strategy (that I discuss in the book) that
ignores the Update method entirely; it assumes that you have stored
procedures that handle complex updates (multi-table related or cascading
updates/deletes) without having to use ADO to do the low-level action
commands for you. This approach uses INSERT SQL to add rows to the database
and UPDATE and DELETE SQL to change or delete rows. But INSERT does not
affect the local data store--the local DataTable or TableAdapter
rowset--only the server-side data store. Keeping these two rowsets in sync
is the issue. Again, there are several ways to accomplish this including use
of replication.

You might benefit from my day-long workshop at DevTeach in Toronto on May
12th.

hth
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Miro]

Hey,

You updated your 2008 tour schedule.

Ive been peaking on your webstie for months to see where you will be in
2008.

Might see you there.

Cheers'

Miro

 

[Miro]

I might have to scratch that. ( just checked the website )

For beign a begininner as much as I am...I cannot justify a cost of 500 yet
for 1 day.

But Great book!

Miro

 

[jp2msft]

In my DataTable "Design View" (the .xsd file showing the default
"Fill,GetData()" SQL commands), I created an Insert and an Update SQL command.

Calling them is supposed to return the number of rows affected, but every
time I call them, I get 0 returned.

Can I not just call them outright?

Do I need to set something ahead of time before I call them?

Is there a way to get the reason(s) why the Insert/Update failed? (they are
in a try/catch block, but no error is thrown)

 

[William Vaughn]

This sounds like the Update method was called with no work to do. This is
often caused by calling AcceptChanges or by not calling EndEdit before the
Update (which posts changes from the bound control to the underlying
DataTable).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[jp2msft]

Mr. Vaughn,

Here is what I had to do, that made things work for me. Please correct me if
I did it the "Boneheaded Way":

I created an InsertRecord and a ModifyRecord for my TableAdapter in the .xsd
Designer. When I need to modify or insert a record, I call one of these
methods on the TableAdapter.

Also, I have to select the current row from (or insert a new row into) the
DataSet. With this row, I update my information with the same information
that I used in my TableAdapter.

On the row, I AcceptChanges.

On the TableAdapter, I Update.

This seems to be the only way I've found to ensure that changes that appear
on the screen also persist to my SQL database.

Goodness! I didn't realize I had to save these changes in two (2) places!

Again, if I am doing this completely wrong, or if you can see a smoother way
of accomplishing what I am doing, please let me know.

Regards,
Joe

 

[Cor Ligthert[MVP]]

JP,

This cannot be the solution because accept changes is to PREVENT that rows
will be updated.

This not for you, because I have seen this written by Bill more times in
this message thread, but for those who because of your answer will have
doubt because of your message in the knowledge of Bill or otherwise take
your solution as true.

Cor

 

[jp2msft]

Thanks Cor,

I'm begining to think now that I should *not* have been working with
DataBound Datasets, but I'm getting pressure to release this so I don't have
time to re-write it.

 

[Cor Ligthert[MVP]]

You probably need the EndEdit, in fact is that something as "accept the
changes from the DataGridView", but that has Bill as well written before.

Cor