none
Delete row from table gives System.NotSupportedException RRS feed

  • Question

  • Hi,

    i'm new to Linq. I'm implementing a test which has to check if a specified record in a table exists, if yes, it should delete it using the simplest way. Can me help somebody please ?

    My code throws an System.NotSupportedException ( System.Object CompareObjectEqual(System.Object, System.Object, Boolean) into SQL does not exist)

    and looks like:

           Dim s101w46_SUSDB_connectionString As String = My.Settings.SUSDBConnectionString
            Using s101w46_SUSDB As New db_s101w46DataContext(s101w46_SUSDB_connectionString)

                Dim Configurations_delete = _
                   (From configurations In s101w46_SUSDB.tbConfigurations() _
                   Where configurations.Value = "test" _
                   And configurations.Name = "test" _
                   Select configurations).SingleOrDefault

                s101w46_SUSDB.tbConfigurations.DeleteOnSubmit(Configurations_delete)

                Try
                    s101w46_SUSDB.SubmitChanges()

                Catch ex As Exception
                    Console.WriteLine(ex)
                End Try

    Thanks

    • Changed type alldo Wednesday, October 10, 2012 2:50 PM
    Wednesday, October 10, 2012 2:47 PM

Answers

  • I have no possibility to change the type - i can't make such changes into the DB. So as a workaround i will use the code without the "Value" comparison. Anyway, thanks for your help Lloyd !


    alldo

    Thursday, October 11, 2012 4:09 PM

All replies

  • Can you provide more details?

    1.  At what line in your code does the problem happen?

    2.  Did your Linq query return a result?

    3.  Your code does not take into account the fact that a row might not be found.

    LS



    Lloyd Sheen

    Wednesday, October 10, 2012 9:28 PM
  • Hi Lloyd,

    thanks for your reply. In the meantime i made a change to the code and it is operational now. The code follows:

    <TestMethod()>
        Public Sub SQL_s101w46_SUSDB_DeleteFromSUSDBtable_Test()
            Dim s101w46_SUSDB_connectionString As String = My.Settings.SUSDBConnectionString
            Using s101w46_SUSDB As New db_s101w46DataContext(s101w46_SUSDB_connectionString)
                Dim Configuration_to_delete As tbConfiguration = (From c In s101w46_SUSDB.tbConfigurations _
                                       Where c.Name = "test" _
                                       Select c).FirstOrDefault()
                s101w46_SUSDB.tbConfigurations.DeleteOnSubmit(Configuration_to_delete)
                Try
                    s101w46_SUSDB.SubmitChanges()
                Catch ex As Exception
                    Console.WriteLine(ex)
                End Try
                Assert.IsTrue(True)
            End Using
        End Sub

    In the original code i think that the "As tbConfiguration" statement was in the third line missing where is the variable "Configurations_delete" declared.

    I know that it is not prepared for the situation -the row might not by found. I will try something to write for that case :)


    alldo

    Wednesday, October 10, 2012 9:47 PM
  • I can't see the declaration being a problem since the compiler will type it automatically from the Linq statement.

    What is different is the selection criteria.  In the "working" version you do not have a check for c(configurations).value.  What type is value?  I think that your first problem is caused by the comparison of value to a string.  To test this simply put that criteria back into the Linq statement and see if an error occurs.  If so then please let us know what type of field "value" is and we might be able to get your entire statement working.

    LS


    Lloyd Sheen

    Wednesday, October 10, 2012 10:09 PM
  • Hello Lloyd, you was absolutely right. When I put the   "Where configurations.Value = "test" _"   statement back, it failes again.

    Here are the types:

    Name, nvarchar(128)

    Value, sql_variant

    How should the whole code look please?

    Thank you!


    alldo

    Wednesday, October 10, 2012 11:27 PM
  • Your problem is with your sql_variant column.  I have looked at how to use it with linq but nothing pops out that would help.  The problem is that a sql_variant can hold just about anything.  When you are attempting to use it in a compare situation linq does not know how to compare the column to a string (or anything else).

    I would suggest that you not use sql_variant with Linq.

    LS


    Lloyd Sheen

    Thursday, October 11, 2012 2:53 PM
  • I have no possibility to change the type - i can't make such changes into the DB. So as a workaround i will use the code without the "Value" comparison. Anyway, thanks for your help Lloyd !


    alldo

    Thursday, October 11, 2012 4:09 PM