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

Francisco,

This may not be the problem, but it looks funny to me so I ask:  If
there is the same UserID in both tbl.WebUser and tbl_Contacts, which
ContactName or UserType is sproc #1 to try to update if you change the
value of AllUsers.ContactName or AllUsers.UserType?

Charles Wortz
Software Development Division
Texas Education Agency
1701 N. Congress Ave
Austin, TX 78701-1494
512-463-9493
CWortz at tea.state.tx.us



-----Original Message-----
From: Francisco H Tapia [mailto:my.lists at verizon.net] 
Sent: Thursday 2003 Apr 17 12:32
To: dba-SQLServer at databaseadvisors.com
Subject: [dba-SQLServer]how to update via an ADP...


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