Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error resolving parameter...

Status
Not open for further replies.
timmoser

timmoser

Programmer
Aug 31, 2002
41
US
No the parameter exist! I am converting a web page from Sybase to MSSQL. Not much different (TSQL) in fact everything has run smoothly with one exception.

The page runs a query but the very next line that references the query throws an error: "Error resolving parameter". Makes no sense. I ran the SP local on MSSQL and it works fine.

I created a code snippet from the page that gets the same error as the original page.

Error below...

Code: 
<cfquery datasource=&quot;theds&quot; name=&quot;getSumWeek&quot; >
	s_it_tt_getsumweek '23061', '12/28/2003'
</cfquery>
<cfoutput>#getSumWeek.recordcount#</cfoutput>

I'm thinking the error being thrown is misleading; maybe it's something else. Below are the results of the SP when run local with MSSQL tools...

Code: 
week_date total holiday vacation ato mato personal illness internal externl admin dif divday juryduty lofab sterm lterm other unauthab itwork presaleswork milduty nopay                                    
2003-12-28 00:00:00	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0
2003-12-21 00:00:00	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0	.0

I'm putting the SP code below it's a long one. Thanks for your thoughts.

Code: 
CREATE PROCEDURE dbo.s_it_tt_getsumweek
@uid VARCHAR(10),
@weekending smalldatetime
AS
BEGIN
-- have reached the max number of tables allowed in one query
-- so added into #temp -- 
-- then use union to add extra rows
SELECT week_date AS week_date,
    		ISNULL(SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday),0) AS total,
		holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
				FROM it_tt_activity a
				WHERE a.activity = 'Holiday' AND a.uid = @uid AND a.week_date = z.week_date),0),
		vacation = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
				FROM it_tt_activity b
				WHERE b.activity = 'Vacation' AND b.uid = @uid AND b.week_date = z.week_date),0),
		ato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity c
			  WHERE c.activity = 'Authorized Time Off' AND c.uid = @uid AND c.week_date = z.week_date),0),
		mato = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity d
			  WHERE d.activity = 'Management Time Off' AND d.uid = @uid AND d.week_date = z.week_date),0),
		personal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
				  FROM it_tt_activity e
				  WHERE e.activity = 'Floating Holiday' AND e.uid = @uid AND e.week_date = z.week_date),0),
		illness = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity f
			  WHERE f.activity = 'Illness' AND f.uid = @uid AND f.week_date = z.week_date),0),
		internal = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity g
			  WHERE g.acttype = 'DecisionOne Work' AND g.uid = @uid AND g.week_date = z.week_date),0),
		externl = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity h
			  WHERE h.acttype = 'Customer Work' AND h.uid = @uid AND h.week_date = z.week_date),0),
 		admin = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity i
			  WHERE i.acttype = 'Administrative' AND i.uid = @uid AND i.week_date = z.week_date),0),
		dif = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity j
			  WHERE j.activity = 'Death in Family' AND j.uid = @uid AND j.week_date = z.week_date),0),
		divday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity k
			  WHERE k.activity = 'Diversity Day' AND k.uid = @uid AND k.week_date = z.week_date),0),
		juryduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity l
			  WHERE l.activity = 'Jury Duty' AND l.uid = @uid AND l.week_date = z.week_date),0),
 		lofab = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity m
			  WHERE m.activity = 'Leave of Absence' AND m.uid = @uid AND m.week_date = z.week_date),0),
		sterm = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
			  FROM it_tt_activity o
			  WHERE o.activity = 'Short Term Disability' AND o.uid = @uid AND o.week_date = z.week_date),0)
  		INTO #tempAct1
		FROM it_tt_activity z
		WHERE z.uid=@uid
		GROUP BY z.week_date
		--ORDER BY z.week_date DESC
		--AND z.week_date>=@weekending

-- need to add as aditional query, because we reached the max allowed tables (16) in the query I had to remove one, I was at 17.
SELECT z.week_date AS week_date,
	unauthab = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity a
	WHERE a.activity = 'Unauthorized Absence' AND a.uid = @uid AND a.week_date = z.week_date),0),
	nopay = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity a
	WHERE a.activity = 'No Pay' AND a.uid = @uid AND a.week_date = z.week_date),0),
	milduty = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity a
	WHERE a.activity = 'Military Duty' AND a.uid = @uid AND a.week_date = z.week_date),0),
	other = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity b
	WHERE b.acttype = 'Other' AND b.uid = @uid AND b.week_date = z.week_date),0),

	lterm = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity c
	WHERE  c.activity = 'Long Term Disability' AND c.uid = @uid AND c.week_date = z.week_date),0),

	itwork = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity c
	WHERE  c.acttype = 'IT Work' AND c.uid = @uid AND c.week_date = z.week_date),0),

	presaleswork = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
	FROM it_tt_activity c
	WHERE  c.acttype = 'Presales Work' AND c.uid = @uid AND c.week_date = z.week_date),0)
INTO #tempAct2
FROM it_tt_activity z
WHERE z.uid=@uid
GROUP BY z.week_date

-- add the fields we couldn't include above
SELECT a.week_date, a.total, a.holiday, a.vacation, a.ato, a.mato, a.personal, a.illness, a.internal, a.externl, a.admin, a.dif, a.divday, 
a.juryduty, a.lofab, a.sterm, b.lterm, b.other, b.unauthab, b.itwork, b.presaleswork, b.milduty, b.nopay
INTO #tempAct
FROM #tempAct1 a, #tempAct2 b
WHERE a.week_date=b.week_date

UPDATE #tempAct
SET admin=(admin-(unauthab+holiday+vacation+ato+mato+illness+juryduty+milduty+nopay))


DECLARE @lastWeekInQ smalldatetime
DECLARE @addedWeek1 smalldatetime
DECLARE @firstWeekInQ smalldatetime
DECLARE @addedBackWeek1 smalldatetime

SELECT @lastWeekInQ = (SELECT MAX(week_date)	FROM #tempAct)
SELECT @firstWeekInQ = (SELECT MIN(week_date) FROM #tempAct)

IF(@lastWeekInQ IS NULL)
BEGIN
	-- user has no recs, poss new employee
	SELECT @addedWeek1=@weekending
	SELECT @addedBackWeek1=DATEADD(dd,-7,@weekending)
END
ELSE
BEGIN

	SELECT @addedWeek1 = DATEADD(dd,7,@lastWeekInQ)
	SELECT @addedBackWeek1 = DATEADD(dd,-7,@firstWeekInQ)
	
	-- if this week is in the timesheet table then we need to move forward another week
	DECLARE @addedWeek2 smalldatetime
	DECLARE @ttweek smalldatetime
	SELECT @ttweek = (
			SELECT a.paydays 
				FROM it_tt_ts_timesheets a, it_tt_paypds b
				WHERE b.week_date=@addedWeek1
				AND a.uid=@uid
 			     AND a.paydays=b.paydays
	)
	
	IF @ttweek IS NOT NULL
	BEGIN
		SELECT @addedWeek2 = DATEADD(dd,7,@addedWeek1)
	END
END

IF @ttweek IS NOT NULL
BEGIN

SELECT week_date, total, holiday, vacation, ato, mato, personal, illness, internal, externl, admin, dif, divday, juryduty, lofab, sterm, lterm, other, unauthab, itwork, presaleswork, milduty, nopay
FROM #tempAct
UNION
SELECT @addedWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
UNION
SELECT @addedBackWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
UNION
SELECT @addedWeek2, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
ORDER BY week_date DESC

END 
ELSE
BEGIN

SELECT week_date, total, holiday, vacation, ato, mato, personal, illness, internal, externl, admin, dif, divday, juryduty, lofab, sterm, lterm, other, unauthab, itwork, presaleswork, milduty, nopay
FROM #tempAct
UNION
SELECT @addedWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
UNION
SELECT @addedBackWeek1, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0
ORDER BY week_date DESC
END
END
GO

Thanks

 
Sort by date Sort by votes
Have you tried called this procedure with <cfstoredproc> and passing the paramaters with <cfprocparam> instead of using <cfquery>?

Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson
 
Upvote 0 Downvote
That sounds like something worth trying. Never did it that way before. I'll check it out. Thanks
 
Upvote 0 Downvote
Ok tried that, no luck though I did not get an error. It just didn't return any records. So to narrow down the problem I ran the same sp with an ASP page. Ran into the same problem. This time the error is &quot;can not access because object is closed&quot;. So I went into the SP and started commenting out SQL. I had to bring it down to only the first select without the INTO statement for it to work. So this sounds like a SQL2000 issue. I'm going to jump into that forum to see what I can get there.

Thanks for your help.

 
Upvote 0 Downvote
No problem. I forgot to mention, to return results from a <cfstoredproc> tag, you'll need to use <cfprocresult>.

Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

FALCONSEYE
  • Locked
  • Question
Error: no known VMs. (check for corrupt jvm.cfg file) !!! How I Fixed It !!!
Replies
0
Views
838
FALCONSEYE
FALCONSEYE
HansKok
  • Locked
  • Question
Error after only changing text and not codes
Replies
0
Views
596
HansKok
HansKok
JulesSG
  • Locked
  • Question
FTP Error CS5
Replies
3
Views
285
spamjim
spamjim
malibu65k
  • Locked
  • Question
error - ColdFusion ODBC Server service is not running or has not been installed
Replies
0
Views
261
malibu65k
malibu65k
jvy2002help
  • Locked
  • Question
How to pass parameter from ColdFusion page to ColdFusion Report
Replies
0
Views
234
jvy2002help
jvy2002help

Part and Inventory Search

Sponsor

Back
Top