none
Unable to delete entry: "The specified index does not exist" RRS feed

  • Question

  • Hi,

    (VS2008/SqlCe 3.5)

    I'm trying to learn the basic operations with SqlCe / SqlMetal / Linq2Sql.  So I made a very simple demo with two tables; Users and Orders (Each user can hold a set of orders). I'm able to save and load data with parent/child structure intact, but I got problems when trying to delete an entry (User), and I also think the save logic is not proper.  I would really be grateful for some help to learn how to do this correct.

    Q1: Deleting:

    The exception I get is in the DeleteById method, on the line: ctx.SubmitChanges ;

    The specified index does not exist. [ UQ__Orders__0000000000000021 ]

    Q2: Saving:

    In addition to this I also find problematic the Save method. I'm not able to make the use of .Attach work, hence have to map all properties. So would be very nice to learn the proper solution for this too.

     

    Relevant code:

    Public Class UserDataService
    
      Public Function GetById(ByVal id As Integer) As Users
        Dim ctx As New TestDB(connection)
        Dim q = (From n In ctx.Users Where n.Id = id Select n).Single
        Return q
      End Function
    
      Public Sub Save(ByVal user As Users)
        Dim ctx As New TestDB(connection)
        Dim q = (From n In ctx.Users Where n.Id = user.id Select n).Single
    
        q.MapData(user)
        ' ctx.Users.Attach(user, q)       ' Does not work
        ' ctx.Users.Attach(user, True)     ' Does not work
         ctx.SubmitChanges()
      End Sub
    
      Public Sub Insert(ByVal user As Users)
        Dim ctx As New TestDB(connection)
    
        ctx.Users.InsertOnSubmit(user)
        ctx.SubmitChanges()
      End Sub
    
      Public Sub DeleteById(ByVal id As Integer)
        Dim ctx As New TestDB(connection)
        Dim q As Users = (From n In ctx.Users Where n.Id = id Select n).Single
    
        ctx.Users.DeleteOnSubmit(q)
        ctx.SubmitChanges()
      End Sub
    
    End Class
    
    
    Partial Public Class Users
    
      Public Sub MapData(ByVal row As Users)
        Me.Name = row.Name
        Me.Orders = row.Orders
        ' (No more properties in this example)
      End Sub
    
      Public Sub Save()
        Dim ds As New UserDataService
        ds.Save(Me)
      End Sub
    
      Public Shared Function GetById(ByVal userID As Integer)
        Dim ds As New UserDataService
        Return ds.GetById(userID)
      End Function
    
      Public Sub Delete(ByVal userID As Integer)
        Dim ds As New UserDataService
        ds.DeleteById(userID)
      End Sub
    
      Public Sub Delete()
        Delete(Me.Id)
      End Sub
    
      Public Sub Insert()
        Dim ds As New UserDataService
        ds.Insert(Me)
      End Sub
    
    End Class
    
    

    Database schema:

    CREATE TABLE [Orders] (
     [Name] nvarchar(100) NULL
    , [Quantity] int NULL
    , [UserId] int NULL
    , [Id] int NOT NULL IDENTITY (1,1)
    );
    GO
    CREATE TABLE [Users] (
     [Name] nvarchar(100) NULL
    , [Id] int NOT NULL IDENTITY (1,1)
    );
    GO
    ALTER TABLE [Orders] ADD CONSTRAINT [PK__Orders__000000000000004E] PRIMARY KEY ([Id]);
    GO
    ALTER TABLE [Users] ADD CONSTRAINT [PK__Users__0000000000000064] PRIMARY KEY ([Id]);
    GO
    CREATE UNIQUE INDEX [UQ__Orders__0000000000000047] ON [Orders] ([Id] ASC);
    GO
    CREATE UNIQUE INDEX [UQ__Users__000000000000005F] ON [Users] ([Id] ASC);
    GO
    ALTER TABLE [Orders] ADD CONSTRAINT [FK_Orders_Users] FOREIGN KEY ([UserId]) REFERENCES [Users]([Id]) ON DELETE NO ACTION ON UPDATE NO ACTION;
    GO

     

     

    • Moved by Tom Li - MSFT Thursday, December 30, 2010 3:29 AM (From:SQL Server Compact)
    Tuesday, December 28, 2010 11:24 AM

All replies

  • Hi dukoff,

     

    Since this issue is more related to Linq to SQL, I will move this thread to Linq to SQL forum and you will get specific support.


    Regards,
    Tom Li
    Thursday, December 30, 2010 3:29 AM
  • Ok, Thanks Tom!



    I managed now to avoid the exception, by adding deep mapping of the child object, as of code below. Still I wonder if it's better way to do this update. I turn off optimistic concurrency by setting attribute <UpdateCheck:=UpdateCheck.Never> for all columns. But the attach method still does not work with that. So how to make the attach method work at all?

     

    Public Class UserDataService
      Public Sub Save(ByVal user As Users)
        Dim ctx As New TestDB(connection)
        Dim id As Integer = user.Id
    
        If user.Id = 0 Then
          Insert(user)
        Else
          Dim q = (From n In ctx.Users Where n.Id = id Select n).Single
          q.MapData(user)
          For Each o In user.Orders
            o.Save()
          Next
          ' ctx.Users.Attach(user, q)       ' Does not work
          ' ctx.Users.Attach(user, True)       ' Does not work
        End If
    
        ctx.SubmitChanges()
        ctx.Dispose()
      End Sub
    End Class
    
    
    Public Class OrderDataService
      Public Sub Save(ByVal order As Orders)
        Dim ctx As New TestDB(connection)
    
        If order.Id = 0 Then
          Insert(order)
        Else
          Dim q = (From n In ctx.Orders Where n.Id = order.Id Select n).Single
          q.MapData(order)
        End If
        ctx.SubmitChanges()
        ctx.Dispose()
      End Sub
    End Class
    
    Partial Public Class Users
      Public Sub MapData(ByVal row As Users)
        Me.Name = row.Name
      End Sub
    End Class
    
    Partial Public Class Orders
      Public Sub MapData(ByVal row As Orders)
        Me.Name = row.Name
        Me.Quantity = row.Quantity
      End Sub
    

    Thursday, December 30, 2010 11:52 AM
  • I have just had what appears to be the same error. On deleting a record, I got the error message

    The specified index does not exist. [ PK__GeneralTexts__00000000000000E3 ]

    where the index PK__GeneralTexts__00000000000000E3 really does not exist in my database.

    This constraint refers to the table GeneralTexts, but I was not deleting a record from that table. The table from which I was deleting a record has a foreign key constraint referring to the table GeneralTexts.

    This problem appears to be an internal error in SQL Server Compact Edition.

    I think that I had deleted and redefined the primary key constraint on the table GeneralTexts, after defining a foreign key constraint referring to that table.

    I have now fixed the problem by deleting and redefining the foreign key constraint as well.

    This suggests strongly, that the definition of the foreign key constraint was still referring to the old primary key definition.

    Phil

    Wednesday, October 5, 2011 10:00 PM