Data Type error

[Overmyhead2]

[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '156e' to a column of data type int.
Getting this error.
ItemMaster.ItemID col has some itemid's that are characters. 156e is an itemid.
If I add this 156e to the GetAllItems query, then I get this error.
Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to float. I've tried cast and convert but get errors. Not real sure where to place them.
Here's code:

<cfquery name="GetAllItems" datasource="WS">
      SELECT itemID, Descr
      FROM itemMaster
	  Where Itemid in (707,906,829)
	 
      ORDER BY Descr
</cfquery>

 <cfquery name="GetAllSites" datasource="WS">
     SELECT SiteId, Name
     FROM Site
     Where SiteID  not in (238,230,727,239,240,800,233
	 ,210,390,927,080,253,973,065,232,237,241,244,290,
	 035,380,211,231,305,'Central')
     ORDER BY Name
</cfquery>	

<div id="content">
  <div class="feature"> 
   <td valign="top" width="195"><a href="/" title="FWISD Child Nrtrition Services AlaCarte Chart Page" accesskey="1"></a><img src="../images/apples1.jpg" width="54" height="55" />
    
	 <CFIF isdefined ("form.sitepick")>
<cfparam name="sitepick" default="#form.sitepick#">
<cfelse>
<cfparam name="sitepick" default="001"> 
    </CFIF>
	  <CFIF isdefined ("form.month")>
<cfparam name="month" default="#form.month#">
<cfelse>
<cfparam name="month" default = "11">
     </CFIF>
    <CFIF isdefined ("form.itempick")>
<cfparam name="itempick" default="#form.itempick#">
<cfelse>
<cfparam name="itempick" default = "829">
   </CFIF>
		 
	    <cfform action = "#CGI.script_name#" 
		 method="post" preservedata="yes"  preloader="no" format="flash" Height="325" width="600" 
		  skin="halogreen">	  
      <div align="left">
	      <cfformgroup type="tabnavigator">
          <cfformgroup type="page" label="AlaCarte Items Sold">
            <cfformgroup type="horizontal">
              <cfoutput>
                <cfselect name = "sitepick" size = "1"  required = "Yes" 
				 message = "Select site Name" query = "GetAllsites"
				  display ="name" value ="siteid" queryPosition="Below">
                <option value = "001" selected>001</option>
                </cfselect>
                <cfset months = ArrayNew(1)> 
                       <cfloop from="1" to="#DateFormat("12/31/2008", "mm")#" index="j"> 
                         <cfset months[j] = DateFormat("#j#/1/2008", "mmmm")> 
                   </cfloop>		 
                        <cfselect name="month" >
                                <cfloop from="1" to="#ArrayLen(months)#" index="i">
								<cfparam name="selectmonth" default="11">
                                      <cfif #selectmonth# eq i>
                                        <option value="#i#" selected>#months[i]#</option>
                                        <cfelse>
                                        <option value="#i#">#months[i]#</option>
                                      </cfif>
                              </cfloop>			
                </cfselect>
				  <cfselect name = "itempick" size = "1"  required = "Yes" 
				   message = "Select Item" query = "GetAllitems"
				  display ="descr" value ="itemid" queryPosition="Below">
                  <option value = "" selected></option>
                </cfselect>
                 <cfinput name="submit" value="Select" type="submit" />	
		    </cfoutput>            </cfformgroup>
          </cfformgroup>
        </cfformgroup>    
    </cfform>
<cfdump var=#form#> 
	 <cfif #sitepick# IS "All">              
<cfquery name="allData" datasource="WS">
SELECT QtySoldMealF, QtySoldMealR, QtySoldMealP, salesDate, 
QtySoldAlaCarteF, QtySoldAlaCarteR, QtySoldAlaCarteP, QtySoldAlaCarteA,  ItemID
FROM  Sales
WHERE itemid=#itempick#
AND datepart(m,SalesDate)=#form.month#
</cfquery>
<cfelse>
<cfquery name="allData" datasource="WS">
SET DATEFORMAT mdy
SELECT QtySoldMealF, QtySoldMealR, QtySoldMealP, salesDate,
QtySoldAlaCarteF, QtySoldAlaCarteR, QtySoldAlaCarteP, QtySoldAlaCarteA,  ItemID
FROM  Sales
WHERE itemid=#itempick#
AND SiteID=#sitepick#
AND datepart(m,SalesDate)=#month#
order by salesdate
</cfquery>
</cfif>
<!--- Get information from the database --->
<cfquery dbtype = "query" name="AlaCarte" >
  SELECT  salesDate, 
  SUM(QtySoldMealF)+ SUM(QtySoldMealR)+ SUM(QtySoldMealP)
  + sum(QtySoldAlaCarteR)+ sum(QtySoldAlaCarteF)+
  sum(QtySoldAlaCarteP)+ sum(QtySoldAlaCarteA) AS Total
  FROM allData  
  GROUP by salesDate 
  order by salesdate 
</CFQUERY>
 <cfif isdefined ("alacarte.salesdate")> 
<cfloop index="i" from="1" to="#Alacarte.RecordCount#">
  <cfset alacarte.salesdate[i]=#DateFormat(alacarte.salesdate[i], "d")#>
</cfloop> 
</cfif>
 <cfdump var=#itempick#> 
<cfquery name="setName" datasource="WS">
SELECT itemid,descr
FROM  ItemMaster
WHERE ItemID=#itempick#
</cfquery>
<cfif not isdefined ("form.sitepick")>
<cfparam name="sitepick" default="001">
<cfparam name="itempick" default="829">
<cfparam name="month" default="1">
  </cfif>
  <cfdump var=#form#>

Appreciate any ideas!

Victoria

 

[bborissov]

instead of using numbers in your query use chars, if the column is (n/var)char:

<cfquery name="GetAllItems" datasource="WS">
      SELECT itemID, Descr
      FROM itemMaster
      Where Itemid in ('707','906','829')
      ORDER BY Descr
</cfquery>

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SQLSister]

  SELECT itemID, Descr
      FROM itemMaster
      Where Itemid in ('707','906','829','156e' )

if itemid is a varchar, then you should have all items inthe In clause surrounded by single quotes. other wise it is implicitly trying to convert them from numbers and this obviously won't work where it isn't a number.

"NOTHING is more important in a database than integrity." ESquared

 

[Overmyhead2]

by adding 156e to the query, I get this error:
Error converting data type varchar to float


Victoria

 

[tlbroadbent]

The advice you received to use single quotes will solve the problem. SQL Server is interpreting 156e as a floating point number in scientific notation. 156e is equivalent to 156e0. If you enclose the string in quotes, SQL will treat it as a string.

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)