This project has moved and is read-only. For the latest updates, please go here.

Data not beeing saved in edb file

Aug 11, 2016 at 7:50 PM
Hi,

Maybe I'm beeing dump or missing some step, but I've created a VB.NET program to create a database, create a table, create two columns (parameter and value). I also tried to save two values ("par1" and "val1"), but when I close my program and try only to read, no to write, I got an error: "Currency not a record". I've tried to use the Esedatabase view and I can see that my table is created but is empty. This is my code:
    Public strArquivoDB As String = fCaminhoDatabase() & "clt.db" 'Nome do arquivo de Database
    Private jetINSTANCE As JET_INSTANCE
    Public jetSESID As JET_SESID
    Public jetDBID As JET_DBID
    Private jetHANDLE As JET_HANDLE
    Private jetTABLEID As JET_TABLEID
    Private jetCOLUMNDEF As JET_COLUMNDEF = New JET_COLUMNDEF()
    Private jetCOLUMNID As JET_COLUMNID

    Public Function fVerificaDBExiste(ByVal strCaminho As String) As Boolean
        'Usada para verificar se o arquivo de DB existe
        Return File.Exists(strCaminho)
    End Function
    Public Function fCriaArquivoDB(ByVal strCaminhoArquivoDB As String, ByVal esentSession As JET_SESID, ByVal esentDBID As JET_DBID) As Boolean
        'Usada para criar arquivo de database
        Try
            If Not (Directory.Exists(fCaminhoDatabase())) Then
                Try
                    Directory.CreateDirectory(fCaminhoDatabase())
                Catch ex As Exception
                    Return False
                    Exit Function
                End Try
            End If
            Api.JetCreateDatabase(esentSession, strCaminhoArquivoDB, Nothing, esentDBID, CreateDatabaseGrbit.None)
            Return True
        Catch e As Exception
            Return False
        End Try
    End Function
    Public Function fCaminhoDatabase() As String
        'Usada para retornar o caminho completo do arquivo de DB
        Return System.AppDomain.CurrentDomain.BaseDirectory() & "Database\"
    End Function
    Public Function fInicializaVariaveisDatabase() As Boolean
        'Usada para inicializar as variaveis de tratamento ESENT e carregar o Database
        Try
            ' Initialize ESENT. Setting JET_param.CircularLog to 1 means ESENT will automatically
            ' delete unneeded logfiles. JetInit will inspect the logfiles to see if the last
            ' shutdown was clean. If it wasn't (e.g. the application crashed) recovery will be
            ' run automatically bringing the database to a consistent state.
            Api.JetCreateInstance(jetINSTANCE, "InstanciaArquivoDB")
            Api.JetSetSystemParameter(jetINSTANCE, JET_SESID.Nil, JET_param.CircularLog, 1, Nothing)
            Api.JetSetSystemParameter(jetINSTANCE, JET_SESID.Nil, JET_param.LogFilePath, 0, fCaminhoDatabase())
            Api.JetSetSystemParameter(jetINSTANCE, JET_SESID.Nil, JET_param.SystemPath, 0, fCaminhoDatabase())
            Api.JetSetSystemParameter(jetINSTANCE, JET_SESID.Nil, JET_param.TempPath, 0, fCaminhoDatabase())
            Api.JetSetSystemParameter(jetINSTANCE, JET_SESID.Nil, JET_param.BaseName, 0, "clt")
            Api.JetInit(jetINSTANCE)
            Api.JetBeginSession(jetINSTANCE, jetSESID, Nothing, Nothing)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function fFinalizaDatabase() As Boolean
        'Usada para finalizar a sessao de acesso ao ESENT DB
        Try
            Api.JetCloseDatabase(jetSESID, jetDBID, CloseDatabaseGrbit.None)
            Api.JetEndSession(jetSESID, EndSessionGrbit.None)
            Api.JetTerm(jetINSTANCE)
            Return True
        Catch
            Return False
        End Try
    End Function
    Public Function fAbreDatabase() As Boolean
        'Usada para abrir arquivo database
        Try
            Api.JetAttachDatabase(jetSESID, strArquivoDB, AttachDatabaseGrbit.None)
            Api.JetOpenDatabase(jetSESID, strArquivoDB, Nothing, jetDBID, OpenDatabaseGrbit.Exclusive)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function fCriaEstruturaDatabase() As Boolean
        'Usada para criar estrutura de tabelas
        Try
            Api.JetBeginTransaction(jetSESID)
            Api.JetCreateTable(jetSESID, jetDBID, "Parametros", 0, 100, jetTABLEID)
            jetCOLUMNDEF.coltyp = JET_coltyp.Text
            jetCOLUMNDEF.cp = JET_CP.ASCII
            Api.JetAddColumn(jetSESID, jetTABLEID, "Parametro", jetCOLUMNDEF, Nothing, 0, jetCOLUMNID)
            Api.JetAddColumn(jetSESID, jetTABLEID, "Valor", jetCOLUMNDEF, Nothing, 0, jetCOLUMNID)
            Api.JetCommitTransaction(jetSESID, CommitTransactionGrbit.None)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function fInsereParametro(ByVal strParametro As String, ByVal strValor As String) As Boolean
        Try
            'Dim jTABLE As New Table(jetSESID, jetDBID, "Parametros", OpenTableGrbit.None)
            Dim jTABLE As JET_TABLEID
            Api.JetBeginTransaction(jetSESID)
            Api.OpenTable(jetSESID, jetDBID, "Parametros", OpenTableGrbit.None, jTABLE)
            Api.JetPrepareUpdate(jetSESID, jTABLE, JET_prep.Insert)
            Dim jCOLUMNIDS As IDictionary(Of String, JET_COLUMNID) = Api.GetColumnDictionary(jetSESID, jTABLE)
            Dim ColunaParametro As JET_COLUMNID = jCOLUMNIDS("Parametro")
            Dim ColunaValor As JET_COLUMNID = jCOLUMNIDS("Valor")
            Api.SetColumn(jetSESID, jTABLE, ColunaParametro, strParametro, Encoding.Unicode)
            Api.SetColumn(jetSESID, jTABLE, ColunaValor, strValor, Encoding.Unicode)
            Api.JetUpdate(jetSESID, jTABLE)
            Api.JetCommitTransaction(jetSESID, CommitTransactionGrbit.WaitLastLevel0Commit)
            Api.JetCloseTable(jetSESID, jTABLE)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Function fLeParametros() As String
        Try
            Dim jTABLE As JET_TABLEID
            Api.JetBeginTransaction(jetSESID)
            Api.OpenTable(jetSESID, jetDBID, "Parametros", OpenTableGrbit.None, jTABLE)
            Dim jCOLUMNIDS As IDictionary(Of String, JET_COLUMNID) = Api.GetColumnDictionary(jetSESID, jTABLE)
            Dim ColunaParametro As JET_COLUMNID = jCOLUMNIDS("Parametro")
            Dim ColunaValor As JET_COLUMNID = jCOLUMNIDS("Valor")
            Api.TryMoveFirst(jetSESID, jTABLE)
            Dim buffer As String = String.Empty
            Do
                buffer = buffer & "Parametro: " & Api.RetrieveColumnAsString(jetSESID, jTABLE, ColunaParametro)
                buffer = buffer & " | Valor: " & Api.RetrieveColumnAsString(jetSESID, jTABLE, ColunaValor) & vbCrLf
            Loop While Api.TryMoveNext(jetSESID, jTABLE)
            Api.JetCloseTable(jetSESID, jTABLE)
            Return buffer
        Catch ex As Exception
            Return False
        End Try
    End Function
I am using a Textbox to write all information:
TextBox1.Text = "Inicializacao: " & fInicializaVariaveisDatabase().ToString()
        If fVerificaDBExiste(strArquivoDB) Then
            TextBox1.Text = TextBox1.Text & vbCrLf & "Arquivo existe: " & fVerificaDBExiste(strArquivoDB).ToString()
        Else
            TextBox1.Text = TextBox1.Text & vbCrLf & "Arquivo existe: " & fVerificaDBExiste(strArquivoDB).ToString()
            TextBox1.Text = TextBox1.Text & vbCrLf & "Criacao arquivo: " & fCriaArquivoDB(strArquivoDB, jetSESID, jetDBID).ToString()
        End If
        TextBox1.Text = TextBox1.Text & vbCrLf & "Abrir arquivo: " & fAbreDatabase().ToString()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Cria estrutura: " & fCriaEstruturaDatabase().ToString()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Insere parametro 1: " & fInsereParametro("Par1", "Vlr1").ToString()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Insere parametro 2: " & fInsereParametro("Par2", "Vlr2").ToString()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Insere parametro 3: " & fInsereParametro("Par3", "Vlr4").ToString()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Insere parametro 4: " & fInsereParametro("Par4", "Vlr3").ToString()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Ler Parametros: " & fLeParametros()
        TextBox1.Text = TextBox1.Text & vbCrLf & "Finalizacao: " & fFinalizaDatabase().ToString()
Regards
Aug 29, 2016 at 7:41 PM
There is one issue with your code, which is preventing it from running fully (and saving the data into the database). In fInsereParametro, you need to commit the transaction using either CommitTransactionGrbit.None or CommitTransactionGrbit.LazyFlush. The CommitTransactionGrbit.WaitLastLevel0Commit that you are using is meant to be used outside of open transactions in order to flush all transactions that have been committed but not yet flushed.

There is another issue which will prevent you from getting useful data out of it. You are mixing ASCII and Unicode encodings. In fCriaEstruturaDatabase you are setting up the column with JET_CP.ASCII, but in fInsereParametro you are inserting data with Encoding.Unicode, and in fLeParametros you are calling Api.RetrieveColumnAsString without specifying the Encoding, which will default to Encoding.Unicode. I would suggest just updating the columns to be JET_CP.Unicode.