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

Francisco H Tapia my.lists at verizon.net
Thu Apr 17 12:32:29 CDT 2003


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




More information about the dba-SQLServer mailing list