数据类型varchar和varchar在subtract运算符中不兼容。 [英] The data types varchar and varchar are incompatible in the subtract operator.

问题描述

private void FRM_RPT_ONE_CHANGE_TIRE_Load(object sender, EventArgs e)
       {
           s = "SELECT s1.Car_id ,s1.Job_card ,s1.value_tire ,convert(varchar,s1.Change_date,101) as Change_date ,s1.Current_dist, s1.Prev_dist ,(s1.Current_dist - s1.Prev_dist) as Change_dist , Drivers.Driver_Name, Tire_type.Tire_desc, s1.Tire_size, Providers.Provider_desc, Front_R ,Front_L,Rare_R, Rare_L ,s1.Notes FROM Change_Tire s1  INNER JOIN Drivers ON s1.Driver_ID = Drivers.Driver_ID INNER JOIN Tire_type ON s1.Tire_id = Tire_type.Tire_id  INNER JOIN Providers ON s1.Provider_id = Providers.Provider_id where s1.Car_id = " + x + " and s1.Job_card = " + y;
           DataSet_One_Change_Tire ds = new DataSet_One_Change_Tire();
           SqlDataAdapter dataAdapter = new SqlDataAdapter(s, con);
            dataAdapter.Fill(ds.Tables["Change_Tire"]);
           CrystalReport_One_Change_tire report = new CrystalReport_One_Change_tire();
           report.SetDataSource(ds.Tables["Change_Tire"]);
           crystalReportViewer_One_Change_tire.ReportSource = report;
           crystalReportViewer_One_Change_tire.Refresh();
       }

推荐答案

您无法对字符串进行数学运算。在大多数情况下，SQL Server将尝试隐式类型转换，但如果字段包含NULL或无法转换为数值的某些内容，则会出现上述错误。将Current_dist和Prev_dist的数据类型更改为数字数据类型，如int或decimal，它将起作用。



如果要保留varchar数据类型，则必须确保您表中的所有值都可以转换为数值。最简单的方法是为NULL值设置默认值，例如：



CAST（IsNULL（sq.Current_dist，'0'）为int）



这不会阻止无法转换的字符串，但如果你的值都是可转换的，这也会有用。

You can't do a mathematical operation on strings. In most cases SQL Server will try an implicit type conversion but if the fields contain NULL or something that can't be converted to a numerical value you'll get the above error. Change the datatype of Current_dist and Prev_dist to a numeric datatype like int or decimal and it will work.

If you want to keep varchar datatype, you'll have to ensure that all values in your table can be converted to a numeric value. The simpliest thing would be to set default for NULL values like:

CAST(IsNULL(sq.Current_dist, '0') as int)

This won't prevent from strings that can't be converted but if your values are all convertable, this will work too.

这篇关于数据类型varchar和varchar在subtract运算符中不兼容。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！