[dba-SQLServer]how to update via an ADP...

EUREKA! (or however that's spelled)

Thanks Billy, this worked just as you expected!!!!!!!!!!!!!!!!!!!! man this
was driving me nuts and you've made my Thursday :)

-Francisco
http://rcm.netfirms.com

On Thursday, April 17, 2003 11:15 AM [GMT-8],
Billy Pang <tuxedo_man at hotmail.com> wrote:

: Not sure if this works (because I have nothing to test it with) but
: for Sproc#2, include another left join to the tblWebUser table and
: use the contactname from tblWebUser only if the contactname is not
: resolved by the tbl_Contacts table.
:
: CREATE Procedure stp_ListNotes (@ccID as int) AS
: SELECT A.ccID, A.Notes, ISNULL(X.ContactName, Y.UserName) AS
: ContactName, A.ccDateTime
: FROM tbl_ccNotes A LEFT OUTER JOIN tbl_Contacts AS X ON (A.UserID =
: X.ContactID) LEFT OUTER JOIN tblWebUser AS Y ON  (A.UserID = Y.UserID)
: WHERE A.ccID = @ccID
: ORDER BY ccDateTime DESC
:
: HTH
: Billy
:
:
:
:
:
:
:: From: "Francisco H Tapia" <my.lists at verizon.net>
:: Reply-To: dba-sqlserver at databaseadvisors.com
:: To: <dba-SQLServer at databaseadvisors.com>
:: Subject: [dba-SQLServer]how to update via an ADP...
:: Date: Thu, 17 Apr 2003 10:32:29 -0700
::
:: Please observer the following two sprocs, in the sproc #1, I have
:: all the data I need but via the ADP (in a subform) this recordset is
:: not updateable... but sproc #2 IS updatable but does not resolve
:: some of the contact names... now, I've set the unique table property
:: to tbl_ccNotes, and
:: that works fine for sproc 2 which is updatable, but sproc #1 is
:: not.... the question is, how can I get sproc #1 to be an updatable
:: recordset?
::
:: CREATE Procedure stp_ListNotes (@ccID as int) AS
::
:: SELECT A.[ccID],  A.[Notes],  AllUsers.ContactName, A.[ccDateTime],
:: AllUsers.UserType
:: FROM [dbo].[tbl_ccNotes] A
:: INNER JOIN (SELECT UserID, UserName as ContactName, 1 AS UserType
:: From dbo.tblWebUser
::    UNION
::    SELECT ContactID as UserID, ContactName, 0 AS UserType From
:: dbo.tbl_Contacts
::    ) As AllUsers
:: ON (A.UserID = AllUsers.UserID)
:: Where A.ccID = @ccID
:: ORDER BY ccDateTime DESC
::
:: CREATE Procedure stp_ListNotes (@ccID as int) AS
:: SELECT A.[ccID],  A.[Notes],  AllUsers.ContactName, A.[ccDateTime]
:: FROM [dbo].[tbl_ccNotes] A
:: LEFT OUTER JOIN tbl_Contacts As AllUsers
:: ON (A.UserID = AllUsers.ContactID)
:: Where A.ccID = @ccID
:: ORDER BY ccDateTime DESC
::
:: -Francisco
:: http://rcm.netfirms.com