invalid column name '-'

sql, sql-server, sql-server-2008, sql-server-express

Solution

Looks like the error is caused by this:

(ar.caption & "-" & ch.cyear) AS [شهر],

In SQL Server you should use `'` for strings and `+` for concatenations, like this:

(ar.caption + '-' + ch.cyear) AS [شهر],

Problem

I am using SQL Server express edition on my computer and trying to execute the following query from a vb.net application however I got this error Invalid column name '-'. In the query I use the '-' character in order to perform subtraction between some of the selected fields where I think that it should work fine as documented at the `MSDN`. Here is the query: ``` SELECT r.ID AS [المعرّف], ch.ID AS [معرّف القيمة], r.active AS مفعّل, en.ename AS [الموتور], b.location AS [عنوان العلبة], c.clientname AS [المشترك], p.ampere AS [أمبير], cl.fullname AS [الجابي], b.code AS [رمز العلبة], ec.code AS [الرمز في العلبة], ch.previousvalue AS [القيمة السابقة], ch.currentvalue AS [القيمة الحاليّة], r.insurance AS [تأمين], ( ( SELECT SUM(total) FROM CounterHistory coh WHERE coh.regid = r.ID AND ( coh.cyear < 2017 OR ( coh.cmonth < 5 AND coh.cyear = 2017 ) ) ) - ( SELECT IsNull(Sum(pyy.pvalue), 0) FROM CounterHistory coh, Payment pyy WHERE pyy.counterhistoryid = coh.ID AND coh.regid = r.ID AND ( coh.cyear < 2017 OR ( coh.cmonth < 5 AND coh.cyear = 2017 ) ) ) ) AS [مكسورات], ch.notes AS ملاحظات, (ar.caption & "-" & ch.cyear) AS [شهر], (b.code & ec.code) AS [رمز مفتاح], ch.monthlyfee AS [رسم اشتراك], (ch.currentvalue - ch.previousvalue) AS [فرق عداد], ch.kilowattprice AS [سعر الكيلو], (((ch.currentvalue - ch.previousvalue) * ch.kilowattprice) + roundvalue) AS [مطلوب كيلو], total + discount AS [المجموع], discount AS [حسم], ( SELECT IsNull(Sum(pyy.pvalue), 0) FROM CounterHistory coh, Payment pyy WHERE pyy.counterhistoryid = coh.ID AND coh.regid = r.ID AND coh.cmonth = 5 AND coh.cyear = 2017 ) AS [مدفوع], ( total - ( SELECT IsNull(Sum(pyy.pvalue), 0) FROM CounterHistory coh, Payment pyy WHERE pyy.counterhistoryid = coh.ID AND coh.regid = r.ID AND coh.cmonth = 5 AND coh.cyear = 2017 ) - discount ) AS [باقي] FROM Registration r, Client c, ElectricBox b, ECounter ec, CounterHistory ch, Package p, Engine en, Collector cl, ArabicMonth ar WHERE r.packageid = p.ID AND ch.cmonth = ar.ID AND r.counterid = ec.ID AND ec.boxid = b.ID AND r.clientid = c.ID AND ch.regid = r.ID AND b.engineid = en.ID AND b.collectorid = cl.ID AND ch.cmonth = 5 AND ch.cyear = 2017 AND ( DatePart("yyyy", r.registrationdate) < 2017 OR ( DatePart("m", r.registrationdate) <= 5 AND DatePart("yyyy", r.registrationdate) = 2017 ) ) ORDER BY cl.fullname, b.code, ec.code ```

Original source