WHat is wrong with my query? MSSQL 2008

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to float.

update vendors set vendorname = '#vendorname#'
            , contactfirstname = '#contactfirstname#'
            , contactlastname = '#contactlastname#'
            , contactfirstname2 = '#contactfirstname2#'
            , contactlastname2 = '#contactlastname2#'
            , address = '#address#'
            , city = '#city#'
            , state  = '#state#'
            , zip = '#zip#'
            , phone = '#phone#'
            , tollfree = '#tollfree#'
            , fax = '#fax#'
            , website = '#website#'
            , category = '#category#'
            , products = '#products#'
            , mempricing = '#mempricing#'
            , mempricing2 = '#mempricing2#'
            , mempricing3 = '#mempricing3#'
            , mempricing4 = '#mempricing4#'
            , minShipment = '#minShipment#'
            , freightpolicy1 = '#freightpolicy1#'
            , freightpolicy2 = '#freightpolicy2#'
            , orderplacement = '#orderplacement#'
            , terms = '#terms#'
            , schedule = '#schedule#'
            , memallowance = '#memallowance#'
            , memallowance2 = '#memallowance2#'
            , memallowance3 = '#memallowance3#'
            , misc = '#misc#'
            , pdffile = '#pdffile#'
        where vid = #vid#
pulse239Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eugene ZCommented:
you need to find what column is not "valid" .
Try to use ISNUMERIC function for validation, preferably on #var# level in your application.
It determines whether an expression is a valid numeric type.
http://technet.microsoft.com/en-us/library/ms186272(v=sql.105).aspx

--

you need to check "numeric" table columns

e.g looks like  vid can be a good start point
vid = #vid#
0
pulse239Author Commented:
I still get an error. :(

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'YES'.
 
The error occurred in line 34

32 :             , misc = '#misc#'
33 :             , pdffile = '#pdffile#'
34 :         where vid = #IsNumeric(vid)#
35 :     </cfquery>
0
PortletPaulfreelancerCommented:
>>varchar to float
perhaps look for float fields in the table, e.g.
SELECT
      c.name
    , c.column_id
    , c.is_nullable
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE object_id = OBJECT_ID('dbo.vendors')
AND t.name = 'float'
;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ggzfabCommented:
To test for numeric, try:

 where IsNumeric(vid)

Open in new window

0
Eugene ZCommented:
can you set on you CF form limitation\validation for the "bad' field(s)  to check if it is numeric?\
check

this CF code
"How can I validate that data entered is numeric and ignore if field is blank? "
http://bytes.com/topic/coldfusion/answers/720616-how-can-i-validate-data-entered-numeric-ignore-if-field-blank

1.<cfif (Attributes.icq NEQ "")>
2.   <cfif IsNumeric (Attributes.icq) AND IsValid("integer", Attributes.icq)>
3.     <cfset Variables.oStudentProfile.icq = Attributes.icq />
4.   <cfelse>   
5.     <cfset temp = Request.oClientError.setError("icq", "Your FLD must consist of numbers only.") />
6.   </cfif>
7. </cfif>

Open in new window

0
gdemariaCommented:
the problem may be that vid is a numeric field but #vid# is alphanumeric

where vid = #vid#

You want to test #vid# to make sure it is valid before the query

<cfif NOT isNumeric(vid)>
    <cfthrow message="invalid vid, must be numeric">
</cfif>

<cfquery....
0
pulse239Author Commented:
It's bizarre that it won't take. The only field in the database that is not an nvarchar is vid which is and int. I do have float on the phone numbers. Could this be causing issues?

Also, I have 2 tables with different data that I am trying to update and while I allow null values, the MSSQL 2008 database is trying to add some of the fields to the first table instead of skipping it through to the 2nd insert statement. Any ideas? I have the field 'email' added to the end of the table and it still returns an error.

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'test@test.com', email.
 
The error occurred in D:/: line 51

49 :     <cfquery name="insertVendor" datasource="mydata">
50 :         insert into vendors (vendorname,contactfirstname,contactlastname,contactfirstname2,contactlastname2,address,city,state,zip,phone,tollfree,fax,website,category,products,mempricing,mempricing2,mempricing3,mempricing4,minShipment,freightpolicy1,freightpolicy2,orderplacement,terms,schedule,memallowance, memallowance2,memallowance3,misc,email,pdffile)
51 :             values ('#vendorname#','#contactfirstname#','#contactlastname#','#contactfirstname2#','#contactlastname2#','#address#','#city#','#state#','#zip#','#phone#','#tollfree#','#fax#','#website#','#category#','#products#','#mempricing#''#mempricing2#','#mempricing3#','#mempricing4#','#minShipment#''#freightpolicy1#','#freightpolicy2#','#orderplacement#,'#terms#','#schedule#','#memallowance#','#memallowance2#','#memallowance3#','#misc#','#email#','#pdffile#')
52 :     </cfquery>
53 : </cfif>
0
pulse239Author Commented:
I finally got it. I changed the floats to nvarchar and it is functional now. Thank you for your assistance.

In the future, how to I insert a float variable into the database? Does it require single quotes or is it treated like an integer where there are no quotes.
0
gdemariaCommented:
I number does not need quotes, but you could be in trouble if there is no value in the variable.

update myTable
   set myNumber = #theValue#


if variable #theValue# is empty, this will cause an error.   If it's ok to have the value be 0 (sometimes NULL and zero must be kept different)  you could do myNumber= #val(theNUmber)#   which will put a zero if the variable is empty.

Or use a CFIF...

set myNumber = <cfif theValue is "">NULL<cfelse>#theValue#</cfif>


Also use <cfqueryparam when you have values coming from form or url values to keep the database safe from sql injection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.