April 17, 2003 at 11:33 am
Please observer the following two sprocs, in the sproc #1, I have all the data I need but via the ADP (MS Access Data projects [front end gui]) (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?, please forgive the naive question...
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
-Francisco
-Francisco
April 17, 2003 at 12:49 pm
This was the solution reply to my post on another list, I want to thank anyone who was trying to figure out an answer for me... but also wanted to post the solution here in case it helps anyone else out....
quote:
Not sure if this works (because I have nothing to test it with) but forSproc#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
-Francisco
-Francisco
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply