Tricky one - CR9, .NET, Stored Procs, SQL - it's a doozy

[ChainsawJoe]

I'll try and explain this as best I can. I've got a .aspx page that successfully displays a Crystal Report. However, when I use a CR that uses a certain Stored Procedure, then I get this error;

Exception of type System.Exception was thrown

Bear with me for a moment - it gets better. The SP I use dynamically creates a SQL statement, then execs it. This is allll good. Query analyser using the exact parameters passed to the .aspx page - perfect.

Next up - the SQL statement that's created includes a couple of datetime parameters, so they're

converted

to varchars. There's a shitload more going on, but they're the cause of the error since removing that section means the CR displays a-ok (but with incorrect data, obviously).

The final straw - if I take the full SQL statement that's created (I

print

ed instead of

exec

uted it) and use it to create a

Command

in CR, then that also works a-ok (but obviously without parameters, therefore quite useless).

Ok. Over to you guys. What do I do to get this working?! Following are a selection of code snippets:

Buiding the SQL statement;

set @mainSelect		= '
SELECT
	distinct
	pn.person_PKID,
	coalesce(ltrim(rtrim(pn.forename))+'' ''+ltrim(rtrim(pn.surname)),''Guest'') as fullname,
	kbi.item_PKID,
	p.Title,
	lh.dateStamp,
	lh.hittype,
	pg.group_FKID,
	g.name as groupname
	

FROM
	LogHits lh
	INNER JOIN Person pn ON lh.person_FKID = pn.Person_PKID 
	INNER JOIN KnowledgeBaseItem kbi ON lh.hit_FKID = kbi.Item_PKID
	INNER JOIN Pages p 
	INNER JOIN PageWebs pw ON p.Page_PKID = pw.Page_FKID ON kbi.Page_FKID = p.Page_PKID
	LEFT OUTER JOIN PersonGroup pg ON lh.person_FKID = pg.Person_FKID
	LEFT OUTER JOIN [Group] g ON g.Group_PKID = pg.group_FKID
	LEFT OUTER JOIN KnowledgeBaseItemSelectedSubtypes kbss ON kbi.Item_PKID = kbss.Item_FKID
WHERE     
	(pw.Web_FKID = 4) 
AND 
	(lh.hitType in (''KB'',''DL'',''EM'')) 
AND
	(lh.datestamp>='''+convert(varchar(25),@dateStart,100)+''')
AND
	(lh.datestamp<='''+convert(varchar(25),@dateEnd,100)+''')'

The resulting SQL statement:

SELECT
	distinct
	pn.person_PKID,
	coalesce(ltrim(rtrim(pn.forename))+' '+ltrim(rtrim(pn.surname)),'Guest') as fullname,
	kbi.item_PKID,
	p.Title,
	lh.dateStamp,
	lh.hittype,
	pg.group_FKID,
	g.name as groupname
	

FROM
	LogHits lh
	INNER JOIN Person pn ON lh.person_FKID = pn.Person_PKID 
	INNER JOIN KnowledgeBaseItem kbi ON lh.hit_FKID = kbi.Item_PKID
	INNER JOIN Pages p 
	INNER JOIN PageWebs pw ON p.Page_PKID = pw.Page_FKID ON kbi.Page_FKID = p.Page_PKID
	LEFT OUTER JOIN PersonGroup pg ON lh.person_FKID = pg.Person_FKID
	LEFT OUTER JOIN [Group] g ON g.Group_PKID = pg.group_FKID
	LEFT OUTER JOIN KnowledgeBaseItemSelectedSubtypes kbss ON kbi.Item_PKID = kbss.Item_FKID
WHERE     
	(pw.Web_FKID = 4) 
AND 
	(lh.hitType in ('KB','DL','EM')) 
AND
	(lh.datestamp>='Sep  1 2003 12:00AM')
AND
	(lh.datestamp<='Sep 26 2003 12:00AM')

Resulting error:

Exception of type System.Exception was thrown. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Exception: Exception of type System.Exception was thrown.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[Exception: Exception of type System.Exception was thrown.]
   CrystalDecisions.Web.ReportAgent.u(Boolean N) +358
   CrystalDecisions.Web.CrystalReportViewer.OnPreRender(EventArgs e) +108
   System.Web.UI.Control.PreRenderRecursiveInternal() +62
   System.Web.UI.Control.PreRenderRecursiveInternal() +125
   System.Web.UI.Control.PreRenderRecursiveInternal() +125
   System.Web.UI.Page.ProcessRequestMain() +1489

TIA everyone!



--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------

 

[ChainsawJoe]

forget that... ummm... basically, i'd been passing the dates to the CR using request.querystring.. and they were being posted... ooops... that's almost 12 hours wasted on that one anyway...



--------------------------------------------------

http://www.chainsawjoe.com

- better than toast.
Penguins - better than

http://www.chainsawjoe.com

--------------------------------------------------