Prev: thank you
Next: using schema in security mode
From: Francis Baiden on 16 Jul 2010 16:17
Hello guys,

Can some assist me on this small issue. I know for guys who have been in
this field for a long time won't have any difficulty doing this. I have two
tables and have used view to create a left join. so now I have the values
both table one and tow displaying in the manner I wanted it to be.

On the application developing side in imported the view table into the
dictionary and created a browse procedure using this view. I was first
excited to see the results, but when I use the update buttons to change a
data in one it tells me record not available. I will want this to work to
solve a major challenge am facing with my new application fully on MS SQL.

I need someone to share is ideas on this issue.

Thank you.

Francis
c6.2

From: Erland Sommarskog on 16 Jul 2010 17:52
Francis Baiden (joebaiden1(a)yahoo.com) writes:
> Can some assist me on this small issue. I know for guys who have been in
> this field for a long time won't have any difficulty doing this. I have
> two tables and have used view to create a left join. so now I have the
> values both table one and tow displaying in the manner I wanted it to
> be.
>
> On the application developing side in imported the view table into the
> dictionary and created a browse procedure using this view. I was first
> excited to see the results, but when I use the update buttons to change a
> data in one it tells me record not available. I will want this to work to
> solve a major challenge am facing with my new application fully on MS SQL.

In which table do you try to change data? The left or right table?

It could certainly help if you posted the view definition, the table
definition and the code you use for the update.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Tony Rogerson on 17 Jul 2010 04:20
Hi Francis,

You can't update a view composed of multiple tables, you could create an
instead of trigger on the view which will give you access to updating both
tables using 2 update statements.

I cover this in my blog post:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2010/01/23/how-to-create-a-one-to-one-relationship-in-sql-server-using-dri-triggers-and-views.aspx

Many thanks,
Tony

"Francis Baiden" <joebaiden1(a)yahoo.com> wrote in message
news:eawiwPSJLHA.1868(a)TK2MSFTNGP05.phx.gbl...
> Hello guys,
>
> Can some assist me on this small issue. I know for guys who have been in
> this field for a long time won't have any difficulty doing this. I have
> two tables and have used view to create a left join. so now I have the
> values both table one and tow displaying in the manner I wanted it to be.
>
> On the application developing side in imported the view table into the
> dictionary and created a browse procedure using this view. I was first
> excited to see the results, but when I use the update buttons to change a
> data in one it tells me record not available. I will want this to work to
> solve a major challenge am facing with my new application fully on MS SQL.
>
> I need someone to share is ideas on this issue.
>
> Thank you.
>
> Francis
> c6.2

 | 
Pages: 1
Prev: thank you
Next: using schema in security mode