Results 1 to 4 of 4

Thread: Cursor problem

  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Red face Unanswered: Cursor problem

    Hi!

    I have taken over an existing project as a consultant. The project is a Webpage programmed in ASP(VB classic) with MS-SQL. This is really not my favourite platform, but I have to make a living somehow.

    I have added two extra columns to a table [group_member_project] and want to include these two columns in a Stored Procedure wich uses a cursor.

    Now, I think that I made everythin right, and the script works when I analyse it, but the IIS gives me the following error: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

    This procedure is quite long, but Im going to post it anyway:
    The only thing I have done in this script is adding to more new columns [SecurityAspects] and [MarketPotential] and added theri corresponding temporary variables. How can I solve this? Im quite desperate!


    Code:
    CREATE Procedure gl_getVoteResultForProject
    (
    	@ProjectId int
    )
    As
    	DECLARE @rowcount int
    	DECLARE @tmpProjectId int
    	DECLARE @tmpProjectName varchar(255)
    	DECLARE @tmpInnovation real
    	DECLARE @tmpUserneeds real
    	DECLARE @tmpSustainability real
    	DECLARE @tmpTransferability real
    	DECLARE @tmpSecurityAspects real
    	DECLARE @tmpMarketPotential real
    	DECLARE @tmpFinished bit
    	
    	DECLARE @chkProjectId int
    	DECLARE @chkInnovation real
    	DECLARE @chkUserneeds real
    	DECLARE @chkSustainability real
    	DECLARE @chkTransferability real
    	DECLARE @chkSecurityAspects real
    	DECLARE @chkMarketPotential real
    	DECLARE @chkGrandTotal real
    	
    	DECLARE @numMembProj int
    	
    	-- Get number of users for this project
    	SELECT     @numMembProj = COUNT(m.MemberId)
    	FROM         project p INNER JOIN
                          memeber m INNER JOIN
                          group_member gm ON m.MemberId = gm.MemberId INNER JOIN
                          group_member_project gmp ON gm.GroupMemberId = gmp.GroupMemberId ON p.ProjectId = gmp.ProjectId
    	WHERE     (p.ProjectId = @ProjectId)
    	
    	/*
    	Cursor.
    	Fetches all members in the project
    	group.
    	*/
    	DECLARE projCursor SCROLL CURSOR FOR 
    	SELECT
    		p.ProjectId,
    		p.ProjectName,
    		Innovation,
    		Userneeds,
    		Sustainability,
    		Transferability,
    		Finished
    		
    	FROM
    	         project p INNER JOIN
                          memeber m INNER JOIN
                          group_member gm ON m.MemberId = gm.MemberId INNER JOIN
                          group_member_project gmp ON gm.GroupMemberId = gmp.GroupMemberId ON p.ProjectId = gmp.ProjectId
    	WHERE     (p.ProjectId = @ProjectId)	
    	ORDER BY p.ProjectId ASC
    	/*
    	Temp table for storing the result to be returned
    	*/
    	CREATE TABLE #chTmpTable
    	( 
    	ProjectId int NULL,
    	ProjectName varchar(255) NULL,
    	Innovation real NULL,
    	Userneeds real NULL,
    	Sustainability real NULL,
    	Transferability real NULL,
    	SecurityAspects real NULL,
    	MarketPotential real NULL,
    	GrandTotal real NULL,
    	isFinishedByAll bit NOT NULL
    	)
    	
    	/*
    	Temp table for storing 'not finished by all' projects
    	*/
    	CREATE TABLE #chTmpTable2
    	( 
    	ProjectId int NOT NULL DEFAULT 1
    	)	
    	
    	
    	
    	
    	-- Default value = 0
    	SELECT @chkProjectId = 0
    	-- Open cursor
    	OPEN projCursor
    	-- get number of rows in cursor
    	SELECT @rowcount = @@CURSOR_ROWS
    	-- loop
    	WHILE @rowcount <> 0
    	BEGIN
    		-- get next record from cursor
    		FETCH FROM projCursor 
    		INTO @tmpProjectId,
    			 @tmpProjectName,
    			 @tmpInnovation,
    			 @tmpUserneeds,
    			 @tmpSustainability,
    			 @tmpTransferability,
    			 @tmpSecurityAspects,
    			 @tmpMarketPotential,
    			 @tmpFinished
    			
    			-- Add to temp table if not finished
    			IF @tmpFinished = 0
    				INSERT INTO #chTmpTable2 (ProjectId) VALUES (@tmpProjectId)
    			
    			-- no default value is specified in db, convert NULL values.
    			IF @tmpInnovation IS NULL
    				SELECT @tmpInnovation = 0
    			
    			IF @tmpUserneeds IS NULL
    				SELECT @tmpUserneeds = 0
    			
    			IF @tmpSustainability IS NULL
    				SELECT @tmpSustainability = 0
    				
    			IF @tmpTransferability IS NULL
    				SELECT @tmpTransferability = 0
    
    			IF @tmpSecurityAspects IS NULL
    				SELECT @tmpSecurityAspects = 0	
    
    			IF @tmpMarketPotential IS NULL
    				SELECT @tmpMarketPotential = 0
    			/*
    			checks if we are processing same ProjectId
    			The projects have several members that all
    			show up in this cursor
    			*/
    			IF @chkProjectId <> @tmpProjectId
    			BEGIN
    				-- new ProjectId, insert new row into temp table
    				INSERT INTO #chTmpTable
    					(ProjectId,
    					ProjectName,
    					Innovation,
    					Userneeds,
    					Sustainability,
    					Transferability,
    					SecurityAspects,
    					MarketPotential,
    					GrandTotal,
    					isFinishedByAll)
    				VALUES
    					(@tmpProjectId,
    					@tmpProjectName,
    					@tmpInnovation,
    					@tmpUserneeds,
    					@tmpSustainability,
    					@tmpTransferability,
    					@tmpSecurityAspects,
    					@tmpMarketPotential,
    					@tmpInnovation + @tmpUserneeds + @tmpSustainability + @tmpTransferability + @tmpSecurityAspects + @tmpMarketPotential,
    					1)
    				-- store away current values
    				SELECT @chkProjectId = @tmpProjectId
    				SELECT @chkInnovation = @tmpInnovation
    				SELECT @chkUserneeds = @tmpUserneeds
    				SELECT @chkSustainability = @tmpSustainability
    				SELECT @chkTransferability = @tmpTransferability
    				SELECT @chkSecurityAspects = @tmpSecurityAspects
    				SELECT @chkMarketPotential = @tmpMarketPotential
    				SELECT @chkGrandTotal = @tmpInnovation + 
    							      @tmpUserneeds +    							      @tmpSustainability + 
    							      @tmpTransferability +
    							      @tmpSecurityAspects +
    							      @tmpMarketPotential
    				
    			END
    			ELSE
    			BEGIN
    				-- same ProjectId, update existing row in temp table
    				-- add previously saved values to current values
    				
    				UPDATE #chTmpTable SET
    					Innovation = @chkInnovation + @tmpInnovation,
    					Userneeds = @chkUserneeds + @tmpUserneeds,
    					Sustainability = @chkSustainability + @tmpSustainability,
    					Transferability = @chkTransferability + @tmpTransferability,
    					SecurityAspects = @chkSecurityAspects + @tmpSecurityAspects,
    					MarketPotential = @chkMarketPotential + @tmpMarketPotential,
    					GrandTotal = @chkGrandTotal + 
    								 @tmpInnovation + 
    								 @tmpUserneeds +
    								 @tmpSustainability +
    								 @tmpTransferability +
    								 @tmpSecurityAspects + 
    								 @tmpMarketPotential
    				WHERE
    					ProjectId = @chkProjectId
    				
    				-- store away current values
    				SELECT @chkProjectId = @tmpProjectId
    				SELECT @chkInnovation = @chkInnovation + @tmpInnovation
    				SELECT @chkUserneeds = @chkUserneeds + @tmpUserneeds
    				SELECT @chkSustainability = @chkSustainability + @tmpSustainability
    				SELECT @chkTransferability = @chkTransferability + @tmpTransferability
    				SELECT @chkSecurityAspects = @chkSecurityAspects + @tmpSecurityAspects
    				SELECT @chkMarketPotential = @chkMarketPotential + @tmpMarketPotential
    				SELECT @chkGrandTotal = @chkGrandTotal + 
    										@tmpInnovation + 
    										@tmpUserneeds + 
    										@tmpSustainability + 
    										@tmpTransferability +
    										@tmpSecurityAspects +
    										@tmpMarketPotential
    				
    			END 
    		-- decrement flag
    		SELECT @rowcount = @rowcount - 1
    		
    	END
    	-- gbg collection
    	CLOSE projCursor
    	DEALLOCATE projCursor
    	
    	UPDATE #chTmpTable SET isFinishedByAll = 0
    	WHERE ProjectId IN (SELECT ProjectId FROM #chTmpTable2)
    	
    	UPDATE #chTmpTable SET
    		Innovation = Innovation/@numMembProj,
    		Userneeds = UserNeeds/@numMembProj,
    		Sustainability = Sustainability/@numMembProj,
    		Transferability = Transferability/@numMembProj,
    		SecurityAspects = SecurityAspects/@numMembProj,
    		MarketPotential = MarketPotential/@numMembProj,
    		GrandTotal = (		  (Innovation/@numMembProj) + 
    					  (UserNeeds/@numMembProj) + 
    					  (Sustainability/@numMembProj) + 
    					  (Transferability/@numMembProj) +
    					  (SecurityAspects/@numMembProj) +
    					  (MarketPotential/@numMembProj))/6
    	
    	-- return recordset to user
    	SELECT * FROM #chTmpTable ORDER BY GrandTotal DESC
    GO

    Regards, Jonas Eriksson - Sweden

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    You need to update the SELECT portion of your CURSOR definition to include the two new columns.

    - OR -

    You need to stop using cursors.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Oct 2005
    Posts
    2

    Thumbs up

    Thanks for the help!!

    This is what happens when you are in a hurry, you miss the obvious details and sercheas everywhere else for the problem.

    The problem I have NOW is that MS SQL server tells me that one of these new columns is ambigious, which it isnt. How irritating. I really hope I can come up with a solution to that problem soon.

    Btw. I know that Your not supposed to use cursors, but I have no choice this time. If I had the time I would have reprogrammed the whole site in PHP5 and MySQL and total OO. The environment I am in now is the total opposite of what I am used to.

    Thanks again!

    // Jonas

    Quote Originally Posted by Chopin
    You need to update the SELECT portion of your CURSOR definition to include the two new columns.

    - OR -

    You need to stop using cursors.

  4. #4
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    You can resolve your ambiguity issue by prepending the column names with the table alias for all columns. I like to do this anyway since it explicitly lets me know which table I am pulling the data from.

    Good luck!
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

Posting Permissions

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