Francisco H Tapia
my.lists at verizon.net
Thu Apr 17 13:34:29 CDT 2003
Good question Charles, I neglected to mention that the UserID is a GUID (my bad), thus no possibility of a dup ID :) -Francisco http://rcm.netfirms.com On Thursday, April 17, 2003 11:13 AM [GMT-8], Wortz, Charles <CWortz at tea.state.tx.us> wrote: : 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 : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com