Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2006

    Unanswered: Do an insert if not present, else do an update?

    First off I apologize for the long post. OK. I hope I explain this correctly. I've got a table with matching ID's from 2 other tables to tie individual Club Members to Activities that the club has performed. So, if a particular club activity has 10 members, but only 2 are "signed up" for a particular activity, when the user edits that activity, if they want, they can "sign up" all of the other members without having to do individual entries. So, for the two that are signed up, it would just Update their listing. But for the other 8, it would have to do an Insert (not an Update).

    I've included a Word document which contains the table layouts and the Sample Data.

    Using that information, here is the current tblClubActivities list and the number of students in each Club:
    '    current tblClubActivities
    caID   ClubMember                          Activity                Units
     1     Empty_Record                        Empty_Record              0
     2     Save The Earth (Mila Kunis)         CFC Sit-In                2
     3     Save The Earth (Evangeline Lilly)   CFC Sit-In                2
     4     German (Jordana Brewster)           Student Sign-Up           4
     5     Spanish (Evangeline Lilly)          Fund Raiser (114)         2
     6     Spanish (Jennifer Morrison)         Fund Raiser (114)         2
     7     PETA (Lake Bell)                    City Hall Demonstration   3
     8     PETA (Rhona Mitra)                  City Hall Demonstration   3
     9     PETA (Anna Kournikova)              City Hall Demonstration   3
    '    current tblclub totals (for Clubs that have members)
    Astronomy (2)
    Chess (1)
    German (1)
    PETA (3)
    PoliSci Leaders (3)
    Save the Earth (7)
    Spanish (3)
    So, for example, let's say that PETA wants to update it's activities listing for City Hall Demonstration. It wants to credit 5 units to each student instead of the current 3. For that it would basically be an "update" for each of the three Members (because all three are already in there). But let's say that Save The Earth wants to change the credits AND tie ALL the Members to the activity (CFC Sit-In) (changing the units from 2 to 3). That would require updating the existing 2 then inserting the other 5. And, of course, if PETA wanted to add a different Activity (for all 3 Members), then it would be an insert for all three. One other wrinkle (at least to me it makes it a lot more confusing) is that a cmemID can be in there for more than one actID. Arghhhh!!!!

    I want to do this through a stored procedure. This is the procedure that I am currently working with but it's not working.

    CREATE PROCEDURE admin_sp_MultipleSysGrpInsertUpdate
         @in_intCACredits TINYINT,
         @in_intActID TINYINT,
         @in_intClubID INTEGER
         SET NOCOUNT ON;
         UPDATE  tblClubActivities
         SET     caCredits = 5
         WHERE   actID = @in_intActID
         AND     caCredits <> @in_intCACredits
         INSERT INTO  tblClubActivities
                SELECT     a.[cmemID],
                           [actID] = @in_intActID,
                           [caCredits]	= @in_intCACredits
                FROM       tblClubMembership a
                LEFT JOIN  tblClubActivities b
                ON         a.[cmemID] = b.[cmemID]
                WHERE      b.[cmemID] IS NULL
                AND        a.clubID = @in_intClubID
    What this is doing is inserting some of the proper ClubMembers but not all of them. I can't see where I'm going wrong on this but hopefully somebody else will.

    I'm * hoping * that this is a pretty easy fix because I am pretty new to SQL and what I have is over my head. I could write the code so that on the .NET side it gets a list, then loops through but that would require multiple trips to the database. I want to avoid this at all costs if at all possible.

    Any idea's? I've searched the forums and looked through the OnLine Books but the stuff that seemed like it might be what I need was, well, confusing. I'm apologize if this is a really dumb question or really simple but I've been working on it for over a week and just can't get my head wrapped around how to do this.

    Thank you in advance for any and all help that you can give. If I need to provide anything else, please let me know. I've tried to include everything I think might be needed.

    Oh, sorry. I'm working with Visual Basic .NET 2005 and MSDE 2000.

    Ever-hopefule programmer-in-training
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts