Updatable View 2

[Pampers]

Hi Everyone,
Newby trying stuff in vb2005.net. I have a form with two subforms. The second subform is populated by a view. But I can't get it updatable (single table view)...


Pampers
Keeping it simple can be complicated

 

[TipGiver]

A view is a table that is populated by a select query. It is a simple way to filter and show the user some data, .. instead of sending a select query to the database. So i could say that a view is a (table ofcource) saved select query string.

A view is only for view.. so you cannot update that table. Just update the real table and the changes will be shown in the view

 

[Pampers]

Hi Tipgiver,
Copy that. But if you 'open' a view in sql server manager or use it in a access.adp, you can update it...

Anyway, I think the way to go is (and what I find on the internet) to create stored procedures for inserting, deleting and updating records. Is that so?

Pampers
Keeping it simple can be complicated

 

[TipGiver]

Create an SP or send through VB the insert, delete, etc queries.

 

[TipGiver]

I am not pretty sure, but i view is a table, or rather a virtual table. When you bind it somewhere, what actually heppens is that a 'select' is sent to the database. From a management tool, you are able to do many "admin" things. Because of that, you might be able to modify a view. In general, as a user that sends queries.. it is not possible to modify a view.

 

[Pampers]

Done. Created Insert, Update, Delete, Select Sp,
InsertEmployee
DeleteEmployee
UpdateEmployee
SelectEmployee

Added the insert, delete and update SP's to SelectEmployeeTableAdapter on dataset SelectEmployee (with the wizard). Fine. And then...what? Not sure how to proceed.

How can I create from the above a datagridview with all the employees on it which I can delete, update and insert records. I know how to do it when I use the table Employees (just drag and drop), but not with the sp. If I drag and drop SelectEmployee on a form, I can only fill one record by choosing and EmployeeID.

Pampers
Keeping it simple can be complicated

 

[phinoppix]

pampers, not all Views on SQLServer are uptable, eg. if the select statement fetches column data from more than one table via joins, or simple query has GROUP BY.

If I'm not mistaken, dragging a view to the IDE will only produce the select command object, making it a readonly datasource. To make it updatable on .NET, you have to manually configure the other CRUD command objects. Basically, you set the command text, doesn't matter if it's plain CRUD statement or a stored proc. Then, you create the parameters, mapping each to its corresponding datasource column.

To see how all these fits, try dragging a table to create a fully configured DataAdapter (c/o wizard) and see how all 4 command objects are initialized, esp the command parameters.

my 2 cents

 

[Pampers]

Hi phinoppix,
Tnx for tip. Yep, followed your advise, and are getting the hang of it (more or less). I see it takes a while to get used to VB and VB.Net if you come from MsAccess-environment. But this forum is a big support.

Pampers
Keeping it simple can be complicated