This might be an issue I've had to deal with before. Do something to change the "fingerprint" (no idea what the correct term would be) of the query - or restart cf. By change the "fingerprint", it could be something as simple ad swapping p.* and pt.* (swapping as in their ordinal position in the select clause). In the past, what has happened is I'll have some query with at least one * in the select statement followed by one or more columns and I add a column to whatever I was selecting * from. If the query stays the same, it uses the same result-set template (again, im making up words - no idea what its really called) It doesn't know to check the underlying structure of the table. But the newly added column gets returned by the db - offsetting all the columns in the rest of the result set.
So as crazy as it sounds - just change something in the sql, and give it a try. It doesn't have to actually change anything about what the query does - adding ,getdate() as helloworld will fix it - and then you can undo the change after one successful run. I assume this is something on CF's side - as restarting the CF service also fixes it. On Tue, Feb 1, 2011 at 4:23 PM, Debbie Morris <deb_mor...@comcast.net>wrote: > > Since everyone should be in a SQL Join state of mind...here's another one. > > I have a weird issue that I haven't been able to narrow down yet. I'm > trying to add a new field to one of my tables to store some additional > information, but once I add the column, my previously working query breaks. > > Here's the query (obviously the person that originally wrote it is in the > 'the fewer characters, the better' camp): > > SELECT p.*, pt.*, pm.Type AS mtype, s.fname AS sfname, s.lname AS slname, > pa.DateActive AS PenAmountDate, pa.MonthlyAmount AS PenAmt, pa.Note AS > PenAmtNote, s.SSN AS sssn, s.DOB AS sdob, spm.Type AS smt > > FROM PensionerMedicalType spm > INNER JOIN PensionerSpouse s ON spm.MedicalID = s.MedicalType > RIGHT OUTER JOIN PensionersActive p > INNER JOIN PensionerType pt ON p.PensionerType = pt.PensionerTID > LEFT OUTER JOIN PensionAmounts pa ON p.PenActID = pa.PenID > LEFT OUTER JOIN PensionerMedicalType pm ON p.MedicalType = pm.MedicalID ON > s.SpouseID = p.Spouse > WHERE (p.PenActID = <cfqueryparam cfsqltype="cf_sql_integer" value="#id#"> > AND pa.active = 1) > > > As soon as I add a column named 'lifeInsType' to the PensionersActive (p) > table, I get the following error when the same query runs: > > Error Executing Database Query. > [Macromedia][SQLServer JDBC Driver]Value can not be converted to requested > type. > > What am I overlooking? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
