How to update view?

[jaYPee]

I am having a problem on how to update view from sql server 2000
database. I have tried dataset and sql dataadapter but no luck. I'm
using this view in datagrid and i want to update the datagrid if there
is changes.

thank in advance

[Anthony Fine]

jaYPee,

Have a look here, it mentions updatable views...

http://msdn.microsoft.com/library/de...eate2_30hj.asp
Anthony
"jaYPee" <hi******@yahoo.com> wrote in message
news:s0********************************@4ax.com...

I am having a problem on how to update view from sql server 2000
database. I have tried dataset and sql dataadapter but no luck. I'm
using this view in datagrid and i want to update the datagrid if there
is changes.

thank in advance

[scorpion53061]

i want to update the datagrid if there

is changes.

Post the code you are using for that sub.

Solutions like these can be found using the VB.NET/ADO.NET Newsgroup Search
Tool at
http://www.kjmsolutions.com/newsgrouptool.htm

This may work. Please do not mass post like this again.

'in the example code below it is assumed that each data

'adapter (MyAD1, MyAD2, MyAd3) have commands for update

'delete and inserts for the respective tables they are

'managing.

MyAD1.Update(MyDS.Tables(0))

MyAD2.Update(MyDS.Tables(1))

MyAD3.Update(MyDS.Tables(2))

'This is a very simplistic code example. If there

'are parent/child relationships involed here then you

'have to break up the process lets say the tables

'are 1 parent to two that is parent to 3. If you have

'updates, inserts, and deletes happening in all of

'the datase's tables then yoyu have to submit things

'in order.

'submit all the inserts first starting from the

'top table on down

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Added))

MyAD1.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Added))

'now submit the updated rows

MyAD1.Update(MyDS.Tables(0).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(1).Select("", "",
DataViewRowState.ModifiedCurrent))

MyAD1.Update(MyDS.Tables(2).Select("", "",
DataViewRowState.ModifiedCurrent))

'finally submit the deletions from the bottom up

MyAD3.Update(MyDS.Tables(2).Select("", "", DataViewRowState.Deleted))

MyAD2.Update(MyDS.Tables(1).Select("", "", DataViewRowState.Deleted))

MyAD1.Update(MyDS.Tables(0).Select("", "", DataViewRowState.Deleted))

'the above will work in most situations. This code is untested.

[scorpion53061]

> Have a look here, it mentions updatable views...

http://msdn.microsoft.com/library/de...eate2_30hj.asp

Anthony,

If I am not mistaken, this is referring to SQL "views" as opposed to
dataviews in which we bind to in our applications.

[jaYPee]

On Tue, 13 Jan 2004 22:15:17 -0600, "scorpion53061" <Its the end of
the world as we know [e-mail]> wrote:

Have a look here, it mentions updatable views...

http://msdn.microsoft.com/library/de...eate2_30hj.asp

Anthony,

If I am not mistaken, this is referring to SQL "views" as opposed to
dataviews in which we bind to in our applications.

this is a views from sql server 2000 i have created. this is the sql:

SELECT SchYrSemCourseID, SchYrSemID, Course.CourseID, CourseTitle,
CourseDesc, Unit FROM Course INNER JOIN SchYrSemCourseJoin ON
Course.CourseID = SchYrSemCourseJoin.CourseID

i used this to create views. i try to use the sqldataadapter to create
a dataset from this views but i can't update it.

[William Ryan]

jayPee, I think that's going to cause you problems. Since your view is
using joins I think you'd need some sophisticated update logic. Why not
pull the tables down individually and use a DataRelation if you need to
update things.

"jaYPee" <hi******@yahoo.com> wrote in message
news:s4********************************@4ax.com...

On Tue, 13 Jan 2004 22:15:17 -0600, "scorpion53061" <Its the end of
the world as we know [e-mail]> wrote:

Have a look here, it mentions updatable views...
http://msdn.microsoft.com/library/de...-us/tsqlref/ts

_create2_30hj.asp
Anthony,

If I am not mistaken, this is referring to SQL "views" as opposed to
dataviews in which we bind to in our applications.

this is a views from sql server 2000 i have created. this is the sql:

SELECT SchYrSemCourseID, SchYrSemID, Course.CourseID, CourseTitle,
CourseDesc, Unit FROM Course INNER JOIN SchYrSemCourseJoin ON
Course.CourseID = SchYrSemCourseJoin.CourseID

i used this to create views. i try to use the sqldataadapter to create
a dataset from this views but i can't update it.

[jaYPee]

On Wed, 14 Jan 2004 00:25:28 -0500, "William Ryan"
<do********@nospam.comcast.net> wrote:

jayPee, I think that's going to cause you problems. Since your view is
using joins I think you'd need some sophisticated update logic. Why not
pull the tables down individually and use a DataRelation if you need to
update things.

don't know what u mean by DataRelation. U mean i can still join table
using DataRelation?

can u give me pls some sample program. i would be very if u have one.

"jaYPee" <hi******@yahoo.com> wrote in message
news:s4********************************@4ax.com.. .

On Tue, 13 Jan 2004 22:15:17 -0600, "scorpion53061" <Its the end of
the world as we know [e-mail]> wrote:

>> Have a look here, it mentions updatable views...
>>
>>

http://msdn.microsoft.com/library/de...-us/tsqlref/ts

_create2_30hj.asp

>
>Anthony,
>
>If I am not mistaken, this is referring to SQL "views" as opposed to
>dataviews in which we bind to in our applications.
>

this is a views from sql server 2000 i have created. this is the sql:

SELECT SchYrSemCourseID, SchYrSemID, Course.CourseID, CourseTitle,
CourseDesc, Unit FROM Course INNER JOIN SchYrSemCourseJoin ON
Course.CourseID = SchYrSemCourseJoin.CourseID

i used this to create views. i try to use the sqldataadapter to create
a dataset from this views but i can't update it.

[William Ryan]

Yes, you can join the local tables, and in many instance, you'll get much
better performance and it'll be much easiser to update do to no unnecessary
redundancies (unless your tables aren't normalized and even then, you'll
still be better off) 2) you can isolate your update logic. This link may
help... http://www.knowdotnet.com/articles/datarelation.html
"jaYPee" <hi******@yahoo.com> wrote in message
news:f1********************************@4ax.com...

On Wed, 14 Jan 2004 00:25:28 -0500, "William Ryan"
<do********@nospam.comcast.net> wrote:

jayPee, I think that's going to cause you problems. Since your view is
using joins I think you'd need some sophisticated update logic. Why not
pull the tables down individually and use a DataRelation if you need to
update things.

don't know what u mean by DataRelation. U mean i can still join table
using DataRelation?

can u give me pls some sample program. i would be very if u have one.

"jaYPee" <hi******@yahoo.com> wrote in message
news:s4********************************@4ax.com.. .

On Tue, 13 Jan 2004 22:15:17 -0600, "scorpion53061" <Its the end of
the world as we know [e-mail]> wrote:

>> Have a look here, it mentions updatable views...
>>
>>

http://msdn.microsoft.com/library/de...n-us/tsqlref/t

s_create2_30hj.asp

>
>Anthony,
>
>If I am not mistaken, this is referring to SQL "views" as opposed to
>dataviews in which we bind to in our applications.
>

this is a views from sql server 2000 i have created. this is the sql:

SELECT SchYrSemCourseID, SchYrSemID, Course.CourseID, CourseTitle,
CourseDesc, Unit FROM Course INNER JOIN SchYrSemCourseJoin ON
Course.CourseID = SchYrSemCourseJoin.CourseID

i used this to create views. i try to use the sqldataadapter to create
a dataset from this views but i can't update it.