locked
Another user has deleted this record - Oracle + ADO + Ria Services + LightSwitch RRS feed

  • Question

  • Good morning everyone,
    I'm trying to update a table in oracle using ADO .NET + Ria Services + LightSwitch.
    Everything is working fine, only when I save any changes made to the data I always get this data conflict message:

    "Another user has deleted this record."

    I'm not able to understand where is my error.
    I appreciate any help to solve this problem.

    My Class....

    Imports System.ComponentModel.DataAnnotations
    Imports Oracle.DataAccess.Client
    Imports System.Text
    
    Public Class NotaFiscal
    
    #Region "Campos"
    
        <Key()>
        Public Property ID As Integer
        Public Property NumeroNotaFiscal As Nullable(Of Integer)
        Public Property DataEmissao As Date
        Public Property DataSaida As Date
        Public Property Cliente As String
        Public Property UF As String
        Public Property Cidade As String
        Public Property Bairro As String
        Public Property Endereco As String
        Public Property Email As String
    
    #End Region
    
    #Region "Métodos"
    
        Private Shared Function CreateDataRecord(Record As IDataRecord) As NotaFiscal
            Return New NotaFiscal With {
                .ID = CInt(Record("CD_NOTA_FISCAL")),
                .NumeroNotaFiscal = CInt(Record("NR_ID_NOTA_FISCAL")),
                .Endereco = If(IsDBNull(Record("DS_ENDERECO")), String.Empty, Record("DS_ENDERECO")),
                .DataEmissao = CDate(Record("DT_EMISSAO")),
                .DataSaida = CDate(Record("DT_SAIDA")),
                .Bairro = If(IsDBNull(Record("NM_BAIRRO")), String.Empty, Record("NM_BAIRRO")),
                .Cidade = If(IsDBNull(Record("NM_CIDADE")), String.Empty, Record("NM_CIDADE")),
                .Cliente = If(IsDBNull(Record("NM_CLIENTE")), String.Empty, Record("NM_CLIENTE")),
                .UF = If(IsDBNull(Record("NM_UF")), String.Empty, Record("NM_UF")),
                .Email = If(IsDBNull(Record("EMAIL")), String.Empty, Record("EMAIL"))
              }
        End Function
    
        Public Shared Function GetAllNotaFiscal() As IQueryable(Of NotaFiscal)
            Dim Lista As New List(Of NotaFiscal)
            Return Lista.AsQueryable
        End Function
    
        Public Shared Function NotaFiscalPorNumero(Numero As Nullable(Of Integer)) As IQueryable(Of NotaFiscal)
            Dim Lista As New List(Of NotaFiscal)
            Dim StrConn As String = My.Settings.MvProducao
            'criar o comando
            Dim Sql As New StringBuilder
            Sql.Append("SELECT * FROM DBAMV.NOTA_FISCAL WHERE NR_ID_NOTA_FISCAL = :NR_ID_NOTA_FISCAL")
            'atribuir os parâmetros
            Using Conn = New OracleConnection(StrConn)
                Using Cmd = Conn.CreateCommand
                    With Cmd
                        .CommandType = CommandType.Text
                        .CommandText = Sql.ToString
                        .Parameters.Add(New OracleParameter(":NR_ID_NOTA_FISCAL", OracleDbType.Int64, 0)).Value = Numero
                    End With
                    'executar instrução
                    Try
                        Conn.Open()
                        Using Dr = Cmd.ExecuteReader
                            If Dr.HasRows Then
                                While Dr.Read
                                    Lista.Add(CreateDataRecord(Dr))
                                End While
                            End If
                            Return Lista.AsQueryable
                        End Using
                    Catch ex As Exception
                        Throw New Exception(ex.Message)
                    End Try
                End Using
            End Using
        End Function
    
        Public Shared Sub UpdateNotaFiscal(t As NotaFiscal)
            Dim StrConn As String = My.Settings.MvProducao
            'criar comando
            Dim Sql As New StringBuilder()
            With Sql
                .Append("UPDATE DBAMV.NOTA_FISCAL SET ")
                .Append("  DS_ENDERECO = :DS_ENDERECO, ")
                .Append("  DT_EMISSAO = :DT_EMISSAO, ")
                .Append("  DT_SAIDA = :DT_SAIDA, ")
                .Append("  NM_BAIRRO = :NM_BAIRRO, ")
                .Append("  NM_CIDADE = :NM_CIDADE, ")
                .Append("  NM_CLIENTE = :NM_CLIENTE, ")
                .Append("  NM_UF = :NM_UF, ")
                .Append("  EMAIL = :EMAIL ")
                .Append("WHERE CD_NOTA_FISCAL = :CD_NOTA_FISCAL")
            End With
            'atribuir parametros
            Using Conn = New OracleConnection(StrConn)
                Using Cmd As OracleCommand = Conn.CreateCommand
                    With Cmd
                        .CommandType = CommandType.Text
                        .CommandText = Sql.ToString
                        .Parameters.Add(New OracleParameter(":DS_ENDERECO", OracleDbType.Varchar2, 100)).Value = t.Endereco
                        .Parameters.Add(New OracleParameter(":DT_EMISSAO", OracleDbType.Date, 0)).Value = t.DataEmissao
                        .Parameters.Add(New OracleParameter(":DT_SAIDA", OracleDbType.Date, 0)).Value = t.DataSaida
                        .Parameters.Add(New OracleParameter(":NM_BAIRRO", OracleDbType.Varchar2, 60)).Value = t.Bairro
                        .Parameters.Add(New OracleParameter(":NM_CIDADE", OracleDbType.Varchar2, 100)).Value = t.Cidade
                        .Parameters.Add(New OracleParameter(":NM_CLIENTE", OracleDbType.Varchar2, 60)).Value = t.Cliente
                        .Parameters.Add(New OracleParameter(":NM_UF", OracleDbType.Varchar2, 2)).Value = t.UF
                        .Parameters.Add(New OracleParameter(":EMAIL", OracleDbType.Varchar2, 200)).Value = t.Email
                        .Parameters.Add(New OracleParameter(":CD_NOTA_FISCAL", OracleDbType.Int64, 0)).Value = t.ID
                    End With
                    'executar instrução
                    Try
                        Conn.Open()
                        Cmd.ExecuteNonQuery()
                    Catch ex As OracleException
                        Throw New Exception(ex.Message)
                    End Try
                End Using
            End Using
        End Sub
    
    #End Region
    
    End Class

    My DomainService....

    Option Compare Binary
    Option Infer On
    Option Strict On
    Option Explicit On
    
    Imports System
    Imports System.Collections.Generic
    Imports System.ComponentModel
    Imports System.ComponentModel.DataAnnotations
    Imports System.Linq
    Imports System.ServiceModel.DomainServices.Hosting
    Imports System.ServiceModel.DomainServices.Server
    
    
    'TODO: Create methods containing your application logic.
    'TODO: add the EnableClientAccessAttribute to this class to expose this DomainService to clients.
    Public Class DbamvDomainService
        Inherits DomainService
    
    #Region "Nota Fiscal"
    
        <Query(IsDefault:=True)>
        Public Function GetAllNotaFiscal() As IQueryable(Of NotaFiscal)
            Return NotaFiscal.GetAllNotaFiscal
        End Function
    
        Public Function NotaFiscalPorNumero(Numero As Nullable(Of Integer)) As IQueryable(Of NotaFiscal)
            Return NotaFiscal.NotaFiscalPorNumero(Numero)
        End Function
    
        Public Sub UpdateNotaFiscal(T As NotaFiscal)
            NotaFiscal.UpdateNotaFiscal(T)
        End Sub
    
    #End Region
    
        Protected Overrides Function Count(Of T)(query As System.Linq.IQueryable(Of T)) As Integer
            Return query.Count
        End Function
    
    End Class

    Wednesday, December 12, 2012 11:39 AM

All replies

  • Ria service not working to update the data,
    see the video

    Does someone's team lightswitch can help me with this problem?

    Thanks.

    • Edited by Marden LR Tuesday, December 18, 2012 5:46 PM
    Tuesday, December 18, 2012 5:41 PM
  • Hi deanvanrooyen,

    Thanks for the help, 

    Now I do not know what to do. since the first release of LightSwitch everything worked perfectly.
    How can I solve this problem? I need to use the access via ADO.NET because the table has more than 150 fields and more than 50.000 records, and I need to update only some fields.

    Wednesday, December 19, 2012 11:04 AM
  • essesntially it looks like whatever is displayed in your screen should be pulled from a single query (it still uses the paging etc) but the related entities need to be filled as well - in the same default query I believe.

    Try posting your issue on http://lightswitchhelpwebsite.com/ as well. You might run into another issue as I am not sure how the concurrency stuff will work with oracle i.e. as I have not used oracle Im not sure if  tag used in odata will work, Im assuming it will.

    Wednesday, December 19, 2012 2:13 PM
  • Hi Marden LR

    Did you find the answer to these problem. I am having the same error when trying to delete using RIA Service and Sql Server.

    Thank you

    Daniel

    Wednesday, December 3, 2014 1:09 AM
  • Hi Daniel,

    I could not make it work, I created a windows forms application and works like a charm

    Wednesday, December 3, 2014 9:55 PM
  • Just a thought - in later versions of LS, when saving an existing entity to the domain service, LS will first call the domain service default query method to load the existing entity from database, to compare the updated entity against the database version of the entity. This behavior is new in later versions of LS when using a domain service class.

    To me your problem appears to be related to the default query method for that entity not being able to load the existing entity from the database during the save operation for whatever reason. You may want to run an SQL Server trace when saving the entity to see why the default query does not return the existing entity and inspect the default entity query method on the domain service class to try and figure out why it is not able to load the existing entity.

    I'm only speculating of course and this may not be the issue, but it is worth a shot to check it.

    Hope this helps.


    Regards, Xander. My Blog

    Thursday, December 4, 2014 2:13 AM