locked
problem using the EXCEPT command in .net ? RRS feed

  • Question

  • Hi,

    I am trying to use the EXCEPT command using vb.

    In SQL it works fine but  apparently I cant use it in my vb application

    I am attaching a snnipit can someone point me to the right direction?

    Tanks.

    Patrick

    Public Shared Function getdataTable(ByVal sql As String) As DataTable
            Dim dt As New DataTable()
            Dim sqlCommand As New SqlCeCommand(sql, connDb)
            Try
                  connDb.Open()
                Dim DtReader As SqlCeDataReader = sqlCommand.ExecuteReader
                dt.Load(DtReader)
                DtReader.Dispose()
            Catch ex As Exception
                 Throw ex
                 Finally
                 connDb.Close()
            End Try
            Return dt
        End Function
    DBUtils.getdataTable("Select *  from A EXCEPT Select *  from B")


    • Edited by Patrick12_3 Tuesday, April 10, 2012 10:03 AM
    Tuesday, April 10, 2012 10:02 AM

Answers

  • I forgot to mention that you can use NOT EXISTS as well.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Patrick12_3 Tuesday, April 10, 2012 1:19 PM
    Tuesday, April 10, 2012 12:27 PM

All replies

  • What is the error you are receiving (if there is one)?  What is the expected behavior and how is your result deviating from that?  In other words, are you getting no data or different data?  Are you testing the SQL side with the CE version or with SQL Express?
    Tuesday, April 10, 2012 11:13 AM
  • Hi,

    Tanks for replaying,

     I am working with Local sdf file

    I am getting no data this is the exception:

    There was an error parsing the query.[Token line number = 1, Token line offset = 42, Token in error = EXCEPT]

    Tuesday, April 10, 2012 11:22 AM
  • Are you certain that EXCEPT is allowed in CE?  CE's T-SQL is a subset of SQL Server's.  I don;t see EXCEPT in this list:  http://msdn.microsoft.com/en-us/library/ms173372(v=SQL.110).aspx

    That's whay I was asking you where you tested the SQL where you got the correct results.

    Tuesday, April 10, 2012 11:27 AM
  •  

    My BAD, :)

    Is there an equivalent to EXCEPT? I am trying to avoid looping on all the Datatable

    I would appreciate any help on the subject

    Tanks,

     

    Patrick

    Tuesday, April 10, 2012 11:47 AM
  • How about using NOT IN instead?

    SELECT ProductID
    FROM Production.Product
    WHERE ProductID
    NOT IN (
    SELECT ProductID
    FROM Production.WorkOrder)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by Heslacher Tuesday, April 10, 2012 12:23 PM
    Tuesday, April 10, 2012 12:20 PM
  • I forgot to mention that you can use NOT EXISTS as well.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Patrick12_3 Tuesday, April 10, 2012 1:19 PM
    Tuesday, April 10, 2012 12:27 PM
  • tanks alot,

    i will try it

    Patrick

    Tuesday, April 10, 2012 1:19 PM