Invalid column name '-'

I am using express edition of SQL Server on my machine and trying to execute the following query from vb.net application however I got this error

Invalid column name '-'.

In the query I am using the '-' character to perform a subtraction between some of the selected fields where I think it should work fine as described in MSDN

.

Here is the request:

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

      

+3


source to share


1 answer


It looks like the error is caused by this:

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

      



In SQL Server, you should use '

for strings and +

for concatenations, for example:

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

      

+4


source







All Articles