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! :eek:

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 - SwedenYou need to update the SELECT portion of your CURSOR definition to include the two new columns.

- OR -

You need to stop using cursors.|||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

You need to update the SELECT portion of your CURSOR definition to include the two new columns.

- OR -

You need to stop using cursors.|||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!