Can you update through a view?

[Greg P]

I am using VS2005 and have been learning a ton about databinding. I know
that when you drag a view from the datasource window (creating a
dataGridView) that an update method is not added to the table adaptor. I
would like to update the b ase tables thorugh the view. How do i do this?

FYI: I have some reserarch on how to handle updating multiple tables and
making sure I don't break referential integrity, yet in this case I will not
be adding or deleteing, only updating. From what I've read I understand that
I should create temperary datatables to store the add, mod, and del changes
and then update them in the correct order (Child delete, Parent Insert,
Parent Mod, Child Insert, Child Mod, grandChild insert, GrandChild Mod).

I currenlty have the sql stored on teh server as a view, should I be storing
the sql in the application? In that case wouldn't have a bound table
adaptor? I guess Im a little fuzzy on creating bound table adaptors whose
result set is not in the format of an exisitng table? I thought I had to
creat a view? Sorta babbling now.... hope this makes sence.

Thanks,
Greg P.

[Kevin Yu [MSFT]]

Hi Grep,

The UpdateCommand is not created for the DataSource, because it is selected
from a view. To create the DeleteCommand and UpdateCommand, there is a
prerequisite. The source must have a primary key for the update command to
detect which row in the table to update. Since it is a View, we cannot
generate the update command for it.

If you really want to update the datasource with the data in a
DataGridView, it think dragging and dropping from the DataSource windows is
not suitable here. Because it only satisfies the most basic usage. If you
want to use drag and drop, you have to make the DataSource a table instead
of a View in SQL database. Or you have to create the typed DataSet and
create the DataAdapter with your own code to do the updates.

HTH.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Kevin,

1) I understand that Microsoft can't generate the updateCommand but can I
add it afterwards? This would allow me to use some functionality from the
drag and drop and add what I need. I do have the pirmary key of the table
that I want to update included in the view. I just hide the column in the
dataGridView.

2) If the answer to this is no, then I want to confirm what you are saying.
I can use the existing dataSet but I need to create my own TableAdaptor and
not use the generated TableAdaptor? If so how do I add the UpdateCommand?
Same question as #1, if microsoft creates it or if I do how do I add a new
Command. (I will look into some reference material as soon as I'm done
writting this)

3) Finally is it easier to use ADO.NET and recordSet (or the .net
equivelant) to do this? I could execute stored procedures to do this based
on dataGridView.cellValueChanged?

Thanks again, you have been great.

Greg P.

"Kevin Yu [MSFT]" wrote:

Hi Grep,

The UpdateCommand is not created for the DataSource, because it is selected
from a view. To create the DeleteCommand and UpdateCommand, there is a
prerequisite. The source must have a primary key for the update command to
detect which row in the table to update. Since it is a View, we cannot
generate the update command for it.

If you really want to update the datasource with the data in a
DataGridView, it think dragging and dropping from the DataSource windows is
not suitable here. Because it only satisfies the most basic usage. If you
want to use drag and drop, you have to make the DataSource a table instead
of a View in SQL database. Or you have to create the typed DataSet and
create the DataAdapter with your own code to do the updates.

HTH.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

After 2 Bass Ales and the doulble episode of House I got an update working.
(by the way House was awsome!) This works with a Drag and Drop from the
DataSource window. I added a tableAdaptor to my exisiting typed DataSet
(DataSource).xsd file. I used the "use existing stored procedures" to create
a select, insert, update and delete methods for a drag and drop dataGridView.
Is there any other way to do this besides using stored procedures? I will
use them if I need to but that is a lot of stored procedures for all of the
data acccess in my application. Yet this is much cleaner than hand coding
the ADO myself. I'm pretty happy right now... time for Bass Ale #3 while I
begin writing 4 stored procedures for every view I will be using in my app...
:-)

Greg P.

"Greg P" wrote:

Kevin,

1) I understand that Microsoft can't generate the updateCommand but can I
add it afterwards? This would allow me to use some functionality from the
drag and drop and add what I need. I do have the pirmary key of the table
that I want to update included in the view. I just hide the column in the
dataGridView.

2) If the answer to this is no, then I want to confirm what you are saying.
I can use the existing dataSet but I need to create my own TableAdaptor and
not use the generated TableAdaptor? If so how do I add the UpdateCommand?
Same question as #1, if microsoft creates it or if I do how do I add a new
Command. (I will look into some reference material as soon as I'm done
writting this)

3) Finally is it easier to use ADO.NET and recordSet (or the .net
equivelant) to do this? I could execute stored procedures to do this based
on dataGridView.cellValueChanged?

Thanks again, you have been great.

Greg P.

"Kevin Yu [MSFT]" wrote:

Hi Grep,

The UpdateCommand is not created for the DataSource, because it is selected
from a view. To create the DeleteCommand and UpdateCommand, there is a
prerequisite. The source must have a primary key for the update command to
detect which row in the table to update. Since it is a View, we cannot
generate the update command for it.

If you really want to update the datasource with the data in a
DataGridView, it think dragging and dropping from the DataSource windows is
not suitable here. Because it only satisfies the most basic usage. If you
want to use drag and drop, you have to make the DataSource a table instead
of a View in SQL database. Or you have to create the typed DataSet and
create the DataAdapter with your own code to do the updates.

HTH.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

Hi Greg,

Yes, using existing stored procedure can customize the updating procedure.
Also, if you double click on the DataSet designer, it will generate another
file for you to add additional code for the typed DataSet. But in my
opinion, using the existing SP will be more elegant.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Greg P]

Thanks Kevin!

"Kevin Yu [MSFT]" wrote:

Hi Greg,

Yes, using existing stored procedure can customize the updating procedure.
Also, if you double click on the DataSet designer, it will generate another
file for you to add additional code for the typed DataSet. But in my
opinion, using the existing SP will be more elegant.

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Kevin Yu [MSFT]]

You're welcome!

Kevin Yu
Microsoft Online Community Support

================================================== ==========================
==========================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ==========================
==========================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)