Link to home
Create AccountLog in
Avatar of Nigel-SA
Nigel-SA

asked on

Error Executing Database Query (SQL Issue)

I'm receiving the error below! Is there an easier way of coding it? Strange it worked with CF7 but not with CF9.  Thanks for any help!!!

Error:
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

<cfquery name="E23LRecordset" datasource="E23LDB">
	
	SELECT *,
	
	IIf(week1weight >0,(FORMAT((((week1weight-startweight)/startweight)*100), "##")),0) AS week1lost,
	IIf(week2weight >0,(FORMAT((((week2weight-startweight)/startweight)*100), "##")),week1lost) AS week2lost,
	IIf(week3weight >0,(FORMAT((((week3weight-startweight)/startweight)*100), "##")),week2lost) AS week3lost,
	IIf(week4weight >0,(FORMAT((((week4weight-startweight)/startweight)*100), "##")),week3lost) AS week4lost,
	IIf(week5weight >0,(FORMAT((((week5weight-startweight)/startweight)*100), "##")),week4lost) AS week5lost,
	IIf(week6weight >0,(FORMAT((((week6weight-startweight)/startweight)*100), "##")),week5lost) AS week6lost,
	
	IIf(week7weight >0,(FORMAT((((week7weight-startweight)/startweight)*100), "##")),week6lost) AS week7lost,	
	IIf(week8weight >0,(FORMAT((((week8weight-startweight)/startweight)*100), "##")),week7lost) AS week8lost,
	IIf(week9weight >0,(FORMAT((((week9weight-startweight)/startweight)*100), "##")),week8lost) AS week9lost,
	IIf(week10weight >0,(FORMAT((((week10weight-startweight)/startweight)*100), "##")),week9lost) AS week10lost,
	IIf(week11weight >0,(FORMAT((((week11weight-startweight)/startweight)*100), "##")),week10lost) AS week11lost,
	IIf(week12weight >0,(FORMAT((((week12weight-startweight)/startweight)*100), "##")),week11lost) AS week12lost,

	IIf(week13weight >0,(FORMAT((((week13weight-startweight)/startweight)*100), "##")),week12lost) AS week13lost,	
	IIf(week14weight >0,(FORMAT((((week14weight-startweight)/startweight)*100), "##")),week13lost) AS week14lost,
	IIf(week15weight >0,(FORMAT((((week15weight-startweight)/startweight)*100), "##")),week14lost) AS week15lost,
	IIf(week16weight >0,(FORMAT((((week16weight-startweight)/startweight)*100), "##")),week15lost) AS week16lost,
	IIf(week17weight >0,(FORMAT((((week17weight-startweight)/startweight)*100), "##")),week16lost) AS week17lost,
	IIf(week18weight >0,(FORMAT((((week18weight-startweight)/startweight)*100), "##")),week17lost) AS week18lost,
	
	IIf(week19weight >0,(FORMAT((((week19weight-startweight)/startweight)*100), "##")),week18lost) AS week19lost,	
	IIf(week20weight >0,(FORMAT((((week20weight-startweight)/startweight)*100), "##")),week19lost) AS week20lost,
	IIf(week21weight >0,(FORMAT((((week21weight-startweight)/startweight)*100), "##")),week20lost) AS week21lost,
	IIf(week22weight >0,(FORMAT((((week22weight-startweight)/startweight)*100), "##")),week21lost) AS week22lost,
	IIf(week23weight >0,(FORMAT((((week23weight-startweight)/startweight)*100), "##")),week22lost) AS week23lost,
	IIf(week24weight >0,(FORMAT((((week24weight-startweight)/startweight)*100), "##")),week23lost) AS week24lost,
		
	IIf(week1weight >0,(FORMAT((week1weight*703)/(tall*tall), "##")),0) AS BMI1,
	IIf(week2weight >0,(FORMAT((week2weight*703)/(tall*tall), "##")),0) AS BMI2,
	IIf(week3weight >0,(FORMAT((week3weight*703)/(tall*tall), "##")),0) AS BMI3,
	IIf(week4weight >0,(FORMAT((week4weight*703)/(tall*tall), "##")),0) AS BMI4,
	IIf(week5weight >0,(FORMAT((week5weight*703)/(tall*tall), "##")),0) AS BMI5,
	IIf(week6weight >0,(FORMAT((week6weight*703)/(tall*tall), "##")),0) AS BMI6,
	
	IIf(week7weight >0,(FORMAT((week7weight*703)/(tall*tall), "##")),0) AS BMI7,
	IIf(week8weight >0,(FORMAT((week8weight*703)/(tall*tall), "##")),0) AS BMI8,
	IIf(week9weight >0,(FORMAT((week9weight*703)/(tall*tall), "##")),0) AS BMI9,
	IIf(week10weight >0,(FORMAT((week10weight*703)/(tall*tall), "##")),0) AS BMI10,
	IIf(week11weight >0,(FORMAT((week11weight*703)/(tall*tall), "##")),0) AS BMI11,
	IIf(week12weight >0,(FORMAT((week12weight*703)/(tall*tall), "##")),0) AS BMI12,	
	
	IIf(week13weight >0,(FORMAT((week13weight*703)/(tall*tall), "##")),0) AS BMI13,
	IIf(week14weight >0,(FORMAT((week14weight*703)/(tall*tall), "##")),0) AS BMI14,
	IIf(week15weight >0,(FORMAT((week15weight*703)/(tall*tall), "##")),0) AS BMI15,
	IIf(week16weight >0,(FORMAT((week16weight*703)/(tall*tall), "##")),0) AS BMI16,
	IIf(week17weight >0,(FORMAT((week17weight*703)/(tall*tall), "##")),0) AS BMI17,
	IIf(week18weight >0,(FORMAT((week18weight*703)/(tall*tall), "##")),0) AS BMI18,	

	IIf(week19weight >0,(FORMAT((week19weight*703)/(tall*tall), "##")),0) AS BMI19,
	IIf(week20weight >0,(FORMAT((week20weight*703)/(tall*tall), "##")),0) AS BMI20,
	IIf(week21weight >0,(FORMAT((week21weight*703)/(tall*tall), "##")),0) AS BMI21,
	IIf(week22weight >0,(FORMAT((week22weight*703)/(tall*tall), "##")),0) AS BMI22,
	IIf(week23weight >0,(FORMAT((week23weight*703)/(tall*tall), "##")),0) AS BMI23,
	IIf(week24weight >0,(FORMAT((week24weight*703)/(tall*tall), "##")),0) AS BMI24,	

	IIf(week1weight >0,(FORMAT((startweight-week1weight), "##")),0) AS lost1,
	IIf(week2weight >0,(FORMAT((startweight-week2weight), "##")),0) AS lost2,	
	IIf(week3weight >0,(FORMAT((startweight-week3weight), "##")),0) AS lost3,
	IIf(week4weight >0,(FORMAT((startweight-week4weight), "##")),0) AS lost4,
	IIf(week5weight >0,(FORMAT((startweight-week5weight), "##")),0) AS lost5,
	IIf(week6weight >0,(FORMAT((startweight-week6weight), "##")),0) AS lost6,

	IIf(week7weight >0,(FORMAT((startweight-week7weight), "##")),0) AS lost7,
	IIf(week8weight >0,(FORMAT((startweight-week8weight), "##")),0) AS lost8,	
	IIf(week9weight >0,(FORMAT((startweight-week9weight), "##")),0) AS lost9,
	IIf(week10weight >0,(FORMAT((startweight-week10weight), "##")),0) AS lost10,
	IIf(week11weight >0,(FORMAT((startweight-week11weight), "##")),0) AS lost11,
	IIf(week12weight >0,(FORMAT((startweight-week12weight), "##")),0) AS lost12,

	IIf(week13weight >0,(FORMAT((startweight-week13weight), "##")),0) AS lost13,
	IIf(week14weight >0,(FORMAT((startweight-week14weight), "##")),0) AS lost14,	
	IIf(week15weight >0,(FORMAT((startweight-week15weight), "##")),0) AS lost15,
	IIf(week16weight >0,(FORMAT((startweight-week16weight), "##")),0) AS lost16,
	IIf(week17weight >0,(FORMAT((startweight-week17weight), "##")),0) AS lost17,
	IIf(week18weight >0,(FORMAT((startweight-week18weight), "##")),0) AS lost18,
	
	IIf(week19weight >0,(FORMAT((startweight-week19weight), "##")),0) AS lost19,
	IIf(week20weight >0,(FORMAT((startweight-week20weight), "##")),0) AS lost20,	
	IIf(week21weight >0,(FORMAT((startweight-week21weight), "##")),0) AS lost21,
	IIf(week22weight >0,(FORMAT((startweight-week22weight), "##")),0) AS lost22,
	IIf(week23weight >0,(FORMAT((startweight-week23weight), "##")),0) AS lost23,
	IIf(week24weight >0,(FORMAT((startweight-week24weight), "##")),0) AS lost24
				
	
	FROM USERS
	
	WHERE DisplayChoice = 'all'
	
	ORDER BY FIRST_NAME
	</cfquery>

Open in new window

Avatar of Hammo777
Hammo777

Nothing jumps out at me as being wrong.  That error can mean that one of your field names is incorrect.  If you have misspelled one for example.
ASKER CERTIFIED SOLUTION
Avatar of Hammo777
Hammo777

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Nigel-SA

ASKER

Thanks guys for your help - good suggestions. Actually CF9 doesn't like ## so I re-wrote the line "      IIf(week1weight >0,(ROUND((((week1weight-startweight)/startweight)*100), 1)),0) AS week1lost," -
which is still not the best but it works.