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!

Currency sign problem

Hi,
I have a problem with an ASP page pulling data from a SQL2000 server.
Expected behaviour is that when prices are displayed they are displayed in
pounds as this is the default locale of the server. However they are
displayed in dollars.
I've checked the default locale and the user locale of the (windows 2000)
server and these are both British england. The language for the SQL user is
also British english.
Are there any other settings in SQL 2000 server that could affect this?
Thanks,
TimSQL Server displays money data types as numbers. It does not have any =currency symbols. How are you displaying the data within your active =server page? Perhaps you need to make sure that the code which displays =the data is correct. If it is, one more thing to check would be to make =sure that a smart developer has not hard coded the currency display =within the stored procedure or sql statement which returns the data.
-- Keith, SQL Server MVP
"Tim Harrison" <tim_eridge@.hotmail.com> wrote in message =news:OeHqV8uSDHA.2148@.TK2MSFTNGP11.phx.gbl...
> Hi,
> > I have a problem with an ASP page pulling data from a SQL2000 server.
> Expected behaviour is that when prices are displayed they are =displayed in
> pounds as this is the default locale of the server. However they are
> displayed in dollars.
> > I've checked the default locale and the user locale of the (windows =2000)
> server and these are both British england. The language for the SQL =user is
> also British english.
> > Are there any other settings in SQL 2000 server that could affect =this?
> > Thanks,
> > Tim
> >

CrystalReportViewer check for guest login

Hi all

I have added crystalreportviewer using ASP .net (VB .net)

I was able to view the report when I run the web page on
my server. Everything seems fine on the server.

However, if I access the crystal report on a client machine,
it will prompt for guest login and password to the server machine.

I am using anonymous access for my IIS setting.
I can access the rest of the .net webpage without any problem.
However, when I enter the webpage with the crystalreportviewer,
it will prompt for guest password.

Even after I have keyed in the guest password, mutiple prompts appear again. The server event log show this error -
The server was unable to logon the Windows NT account 'IUSR_Machinename' due to the following error: Logon failure: unknown user name or bad password. The data is the error code.

After canceling the prompt a few times, it will show the page with
the report and its data but the sort tree and reportviewer
functions bar on top will show all weird marking. :(

For testing, I have tried adding just a crystalreportviewer object
on a empty webpage. I did not link the crystalreportviewer to
any .rpt file and the same prompt appear. Its seems that the
crystalreportviewer object need to be authenticated on the server
when accessing from the client machine.

I am using MS studio 2003 and IIS 5.1 on XP professional server.

Please help advice.

Thanks.
DerekHi all,

I have found the solution myself.

Give access rights to this folder
C:\Program Files\Microsoft Visual Studio .NET 2003\Crystal Reports

to the IUSR_<MACHINENAME> anonymous account.

This is because all the .net dll for crystal report is installed in
this folder. Hence the correct access should be provided to this
folder.

Regards,
Derek

CrystalReportViewer and ASP.Net

Hi Sir
I am Asad, software developer. I am working in ASP.Net2005 with VB.Net 2005 and using CR.Net 2005. I having some problems.
Whenever i click some button on crystalreportviewer's toolbar, the page bocomes empty. Report becomes invisible.
I got ur address from Code Project while searching abt reports problem.

Plz help Me.

Regards
Asad NaeemDid you find an answer to your problem? I believe I have the same problem - I put together a CRViewer project using VB.net (2003). When reports are displayed with crosstabs, there is nothing there. I also get an error when the report has drill down group level code (for header display)i.e, "DrillDownGroupLevel=0" the remaining text does not appear to be part of the formula. All of these reports work fine using Crystal (10) on our server, which is where they are created. The reports are saved with data, so it's not a databse issue at all. Drill down is OKed in the properties of the viewer, so that's not an issue..

Is this some sort of versioning problem? Any tips on finding the source of the problem would be appreciated.|||Hi...
Yes i found the solution. Infact the buttons of the toolbar are server side controls. I have applied the condition in the Page_Load i.e,
if Not PostBack() then .... End If.
The scenerio was:::
I have used the typed report (report bidded with typed dataset)
When i click the button the page starts rendering and and dataset and everything becoes empty and due to the condition the dataset did not fill again that is why reports becmes invisible. I found the solution which
i have used cache for this purpose. At the time of loading the page, i saved the dataset in the cache with the time duration of 10 mins. Whenever i click any toolbar button the data is retrieved from the cached every time before 10 mins paases. After 10 mins page again retrieves the data from the database otherwise it retrieves the data from the cache. In this way dataset is never empty.

Contacts
00923334471758
usan807@.hotmail.com
asad.naeem@.gmail.com
anuab_lg@.yahoo.co.uk

Regards
Asad Naeem|||Sounds like yours was a bit different than mine - one thing I have found - If I recreate the report using the Crystal designer in VB.Net (2003), than the crosstab(s) shows. If I open the original report in the (VB) designer, it looks OK (you see the crosstabs, headings etc. in the designer), but even if I do a save as, there is no data when the report is viewed, so it looks like it's at least somewhat related to the fact that the original crosstab reports were created in CR10. I don't know enough about this yet, but I wouldn't think that should be the case. Am I wrong on that?

I've seen other people have had similar problems, but most of the questions have gone unanswered. Others have indicated that things go blank when they do something that requires the screen to be redrawn - could the problem have anything to do with Windows.Drawing routines that might be treated differently from one version to another?