VB.net Invalid Column name in SQL Statement

Hi. I am getting an error that Check1 is an invalid Column name when I know that
it is the addition syntax - how do I solve this



    sSQL = "SELECT InvoiceLines.InvoiceHeaderID, InvoiceLines.GrossAmount, InvoiceLines.VATAmount, InvoiceLines.exVAT, [VATAmount]+[exVAT] AS Check1, [GrossAmount]-[Check1] AS Difference, Category1, Category2, Category3"
                sSQL = sSQL & " FROM InvoiceLines"
                Dim connection As New SqlConnection(My.Settings.CS_Setting)
                oSQLAdapter_Query2 = New SqlDataAdapter(sSQL, connection)
                oSQLAdapter_Query2.Fill(oSQLTable_Query2)
                Me.DataGridView2.DataSource = oSQLTable_Query2
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CodeCruiserConnect With a Mentor Commented:
Are you getting it at the bold expression?

 sSQL = "SELECT InvoiceLines.InvoiceHeaderID, InvoiceLines.GrossAmount, InvoiceLines.VATAmount, InvoiceLines.exVAT, [VATAmount]+[exVAT] AS Check1, [GrossAmount]-[Check1] AS Difference, Category1, Category2, Category3"
                sSQL = sSQL & " FROM InvoiceLines"
                Dim connection As New SqlConnection(My.Settings.CS_Setting)
                oSQLAdapter_Query2 = New SqlDataAdapter(sSQL, connection)
                oSQLAdapter_Query2.Fill(oSQLTable_Query2)
                Me.DataGridView2.DataSource = oSQLTable_Query2

Does it work if you remove that expression?
0
 
TheSailorConnect With a Mentor Commented:
Don't think that will work with [Check1]

Why not just use

 sSQL = "SELECT InvoiceLines.InvoiceHeaderID, InvoiceLines.GrossAmount, InvoiceLines.VATAmount, InvoiceLines.exVAT, [VATAmount]+[exVAT] AS Check1, [GrossAmount]-[VATAmount]-[exVAT] AS Difference, Category1, Category2, Category3"
0
 
Jared_SConnect With a Mentor Commented:
I think CodeCruiser is right. You can't use [Check1] to calculate the difference.

Try replacing the [Difference] calculation with

 [GrossAmount] - ([VATAmount]+[exVAT] ) as Difference
0
 
deightonConnect With a Mentor Commented:
you can't use a column defined in the query like that, but you can query the query and then use the defined field - that can be a way of avoiding adding the calculation multiple times.

sSQL = "SELECT DQ.*, DQ.[GrossAmount] - DQ.[Check1] AS Difference FROM (SELECT InvoiceLines.InvoiceHeaderID, InvoiceLines.GrossAmount, InvoiceLines.VATAmount, InvoiceLines.exVAT, [VATAmount]+[exVAT] AS Check1,  Category1, Category2, Category3"
                sSQL = sSQL & " FROM InvoiceLines) AS DQ "
                Dim connection As New SqlConnection(My.Settings.CS_Setting)
                oSQLAdapter_Query2 = New SqlDataAdapter(sSQL, connection)
                oSQLAdapter_Query2.Fill(oSQLTable_Query2)
                Me.DataGridView2.DataSource = oSQLTable_Query2
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
All Courses

From novice to tech pro — start learning today.