Thread: Chapter 11
View Single Post
  #1 (permalink)  
Old June 2nd, 2006, 04:09 AM
zaidqais zaidqais is offline
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Chapter 11

hi
in cahpter 11 in the book Beginning Visual Basic 2005
i have to creat new stored produce

and this is his code


Code:
CREATE  PROCEDURE usp_InsertTimeSheet
(
   @TimeSheetID      UNIQUEIDENTIFIER,
   @UserID           UNIQUEIDENTIFIER,
   @WeekEndingDate   DATETIME
)
AS

DECLARE @GroupID UNIQUEIDENTIFIER,
        @ProjectID UNIQUEIDENTIFIER,
        @TimeSheetDate DATETIME,
        @i TINYINT

-- Get the GroupID that the user belongs to
SELECT @GroupID = GroupID FROM Users WHERE UserID = @UserID

DECLARE Project_Cursor CURSOR FOR SELECT ProjectID 
   FROM GroupProjects WHERE GroupID = @GroupID

BEGIN TRANSACTION
   BEGIN TRY
      -- Insert the time sheet
      INSERT INTO TimeSheets
         (TimeSheetID, UserID, WeekEndingDate, Submitted, ApprovalDate,
         ManagerID, LastUpdateDate)
         VALUES(@TimeSheetID, @UserID, @WeekEndingDate, 0, NULL,
         NULL, GETDATE())
   END TRY
   BEGIN CATCH
      ROLLBACK TRANSACTION
      RAISERROR('Insert into TimeSheets failed.',18,1)
      RETURN
   END CATCH

   -- Set the initial time sheet date to the beginning of the week
   SET @TimeSheetDate = @WeekEndingDate - 4

   -- Set up a loop to insert time sheet items for 5 days
   SET @i = 1
   WHILE (@i < 6)

      BEGIN
      -- Open the cursor
      OPEN Project_Cursor

      -- Get the first row of data from the cursor into our variable
      FETCH NEXT FROM Project_Cursor INTO @ProjectID

      WHILE @@FETCH_STATUS = 0
         BEGIN
         BEGIN TRY
            -- Insert the time sheet item
            INSERT INTO TimeSheetItems
            (TimeSheetItemID, TimeSheetID, ProjectID, Hours, TimeSheetDate)
               VALUES(NEWID(), @TimeSheetID, @ProjectID, 0, @TimeSheetDate)
         END TRY
         BEGIN CATCH
            ROLLBACK TRANSACTION
            RAISERROR('Insert into TimeSheetItems failed.',18,1)
            RETURN
         END CATCH
         -- Get the next row of data from the cursor into our variable
         FETCH NEXT FROM Project_Cursor INTO @ProjectID
         END

      CLOSE Project_Cursor

      -- Increment the date by one day
      SET @TimeSheetDate = @TimeSheetDate + 1
      -- Increment the loop counter by one
      SET @i = @i + 1
      END

-- Deallocate cursor
DEALLOCATE Project_Cursor

-- Commit all inserts
COMMIT TRANSACTION
and i cant there is error
and this the messege

Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 21
Line 21: Incorrect syntax near 'TRY'.
Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 28
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 33
Incorrect syntax near the keyword 'END'.
Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 51
Line 51: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 56
Line 56: Incorrect syntax near 'TRY'.
Msg 170, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 61
Line 61: Incorrect syntax near 'CATCH'.
Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 64
Incorrect syntax near the keyword 'END'.
Msg 156, Level 15, State 1, Procedure usp_InsertTimeSheet, Line 72
Incorrect syntax near the keyword 'END'.

can you help me and told me what is the error and how to fix it
i am using sql server 2005 which comes with VS.2005
thank you