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

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
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail