Data loss in MS Access Database

[Waldis_1]

I am adding data to a table in MS Access (2010 Version) from VB 2010 (using INSERT INTO statement) but when I exit the database and enter again the records are lost. Is there any known condition that causes that loss?.

Thans in advance.

[Reverend Jim]

I suspect you are updating the data in a dataset (in-memory copy of the database) but not updating the actual data in the database itself.

[Waldis_1]

How can I solve that?, the application's path in the code is for the main database.

[Santanu.Das]

Post your snippet to understand your problem.

[Waldis_1]

Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
                 If Me.txtmontoprestamo.Text = "" Then
                MsgBox("Debe digitar el monto a prestar.", MsgBoxStyle.Critical)
                Exit Sub
            Else

                Dim name As String
                Dim lastname As String
                Dim id As String
                Dim nickname As String
                Dim business As String
                Dim noced As String
                'Dim shift As String
                Dim pin As Integer
                Dim bankname As String
                Dim localuser As String = GetUserName()
                Dim phone As String
                id = Me.gridclientes.CurrentRow.Cells(0).Value
                bankname = Me.gridclientes.CurrentRow.Cells(1).Value
                pin = Me.gridclientes.CurrentRow.Cells(2).Value
                name = Me.gridclientes.CurrentRow.Cells(3).Value
                lastname = Me.gridclientes.CurrentRow.Cells(4).Value
                nickname = Me.gridclientes.CurrentRow.Cells(5).Value
                noced = Me.gridclientes.CurrentRow.Cells(6).Value
                business = Me.gridclientes.CurrentRow.Cells(7).Value
                phone = Me.gridclientes.CurrentRow.Cells(8).Value


                Call tasaintereses() 'Asignar la tasa de interés según el monto.

                Dim montoprestamo = Decimal.ToInt32(Me.txtmontoprestamo.Text)
                Dim tasainteres = Decimal.ToDouble(Me.txtinteres.Text)

                Dim cmd As OleDb.OleDbCommand
                Dim archivo As String
                archivo = Application.StartupPath
                Dim path As String
                path = archivo & "\prestamos.accdb"
                'clientes = path

                Dim connection = " Provider=Microsoft.ace.OLEDB.12.0;" & _
                           " Data Source= " & path
                Dim conn As New OleDb.OleDbConnection(connection)
                conn.Open()

                If Me.txtmontoprestamo.Text = "" Then
                    MsgBox("Debe digitar el monto del préstamo.", MsgBoxStyle.Information, "Importante")
                    Me.txtmontoprestamo.Select()
                    Exit Sub

                Else

                End If

                Dim fecha As Date = Format((Now()), "long date")

                If MessageBox.Show("Están todos los datos correctos?.", "Nuevos Usuarios", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                    Try
                        'En esta parte va el código para insertar los datos en la base de datos.
                        cmd = New OleDbCommand("insert into tblprestamosinteres(codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & vbCrLf &
                                            " Values(empid,fecha, id, nombres,lastname, nickname, noced, business, pin, bankname, montoprestamo, tasainteres, localuser)", conn)

                        cmd.Parameters.AddWithValue("@codigoprestamo", empid)
                        cmd.Parameters.AddWithValue("@fechaprestamo", fecha)
                        cmd.Parameters.AddWithValue("@idcliente", id)
                        cmd.Parameters.AddWithValue("@nombres", name)
                        cmd.Parameters.AddWithValue("@apellidos", lastname)
                        cmd.Parameters.AddWithValue("@apodo", nickname)
                        cmd.Parameters.AddWithValue("@nocedula", noced)
                        cmd.Parameters.AddWithValue("@empresa", business)
                        cmd.Parameters.AddWithValue("@PIN", pin)
                        cmd.Parameters.AddWithValue("@banco", bankname)
                        cmd.Parameters.AddWithValue("@montoprestamo", montoprestamo)
                        cmd.Parameters.AddWithValue("@interes", tasainteres)
                        cmd.Parameters.AddWithValue("@usuariointerno", localuser)



                        conn.State.ToString()


                        cmd.ExecuteNonQuery()
                        conn.Close()
                        conn.Open()
                        MsgBox(conn.State.ToString)
                    Catch ex As OleDb.OleDbException
                        MessageBox.Show(ex.Message)
                    End Try

                    MsgBox("Todos los datos fueron insertados correctamente.", MsgBoxStyle.Information)
                    Me.txtmontoprestamo.Text = Nothing
                    Me.txtmontointeres.Text = ""
                    Me.txtinteres.Text = ""
                    Me.txtbuscarnombres.Select()
                    gridprestamointeres.DataSource = ""

                    Else
                    Exit Sub

                    End If
                    End Sub

[Waldis_1]

The code sometimes doesn't work adding data to the database (and no errors are displayed) and the data that is added is lost when I close the database. Messages are in spanish because I am from Dominican Republic.

[Santanu.Das]

There is a small mistake in your data insertion SQL Statement in line no. 60 to 61. Why are you insert a vbCrlf Command into the SQL Statement?

Replace from lin no.60 to 61 with the following. It should be fulfild your requirments.

cmd = New OleDb.OleDbCommand("insert into tblprestamosinteres(codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & _
"Values(empid,fecha, id, nombres,lastname, nickname, noced, business, pin, bankname, montoprestamo, tasainteres, localuser)", conn)

[Waldis_1]

No mistakes while I add the data to the database, I even opened the table several times to be sure that the data was still there and it was!!. I believe the problem is when the form is loaded, something clean up the table. I am adding the Form Load Code so any of you maybe can see what I can't.

Private Sub Form4_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'PrestamosDataSet2.tblprestamosinteres' table. You can move, or remove it, as needed.
        Me.TblprestamosinteresTableAdapter.Fill(Me.PrestamosDataSet2.tblprestamosinteres)
        'TODO: This line of code loads data into the 'PrestamosDataSet1.tblclientes' table. You can move, or remove it, as needed.
        Me.TblclientesTableAdapter.Fill(Me.PrestamosDataSet1.tblclientes)
        Me.gridclientes.RowHeadersVisible = False
        'gridclientes.Dock = DockStyle.Fill
        'gridclientes.BackgroundColor = Color.Empty
        gridclientes.DefaultCellStyle.SelectionBackColor = Color.LightGray
        gridclientes.DefaultCellStyle.SelectionForeColor = Color.Black
        gridclientes.AutoSizeRowsMode = _
       DataGridViewAutoSizeRowsMode.DisplayedCells

        gridclientes.BorderStyle = BorderStyle.Fixed3D
        Me.txtinteres.Enabled = False
        Me.txtmontointeres.Enabled = False

        Dim userName = GetUserName()
        'Dim user = My.User.CurrentPrincipal
        'MsgBox(user)
        Me.lblusuarrio.Visible = True
        Me.lblusuarrio.Text = "Usuario: " & userName

        'Formato grid préstamos.
        Me.TblprestamosinteresTableAdapter.Fill(Me.PrestamosDataSet2.tblprestamosinteres)
        Me.gridprestamointeres.RowHeadersVisible = False
        Me.gridclientes.Sort(gridclientes.Columns(1), ListSortDirection.Ascending)

        'gridprestamointeres.Dock = DockStyle.Fill
        'gridprestamointeres.BackgroundColor = Color.Empty
        gridprestamointeres.Sort(gridprestamointeres.Columns(1), ListSortDirection.Ascending)
        gridprestamointeres.DefaultCellStyle.SelectionBackColor = Color.Coral
        gridprestamointeres.DefaultCellStyle.SelectionForeColor = Color.Black
        gridprestamointeres.AutoSizeRowsMode = _
       DataGridViewAutoSizeRowsMode.DisplayedCells

        Me.gridprestamointeres.BorderStyle = BorderStyle.Fixed3D
    End Sub

[Santanu.Das]

You did a great mistake to assign the values in respect of Data Fields through Parameters. You wrote the variable names in the SQL Statement in the place of the Parameters.

The following codes you wrote in line 60 & 61.
cmd = New OleDbCommand("insert into tblprestamosinteres(codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & vbCrLf &
" Values(empid,fecha, id, nombres,lastname, nickname, noced, business, pin, bankname, montoprestamo, tasainteres, localuser)", conn)

Replace them with

cmd = New OleDbCommand("insert into tblprestamosinteres (codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & _
                       " Values (@codigoprestamo,@fechaprestamo, @idcliente, @nombres, @apellidos, @apodo, @nocedula, @empresa, @PIN, @banco, @montoprestamo, @interes, @usuariointerno)", conn)

[Waldis_1]

Sorry, it didn't work. It worked several times and then all is lost. it happens when I reopen the form.

[Waldis_1]

Does it matter that I take the information from a datagrid and assign it to the variables?. I have others tables with textboxes containing the data and I don't have this problem with them.

Dim name As String
        Dim lastname As String
        Dim id As String
        Dim nickname As String
        Dim business As String
        Dim noced As String
        Dim pin As Integer
        Dim bankname As String
        Dim localuser As String = GetUserName()
        Dim phone As String
        id = Me.gridclientes.CurrentRow.Cells(0).Value
        bankname = Me.gridclientes.CurrentRow.Cells(1).Value
        pin = Me.gridclientes.CurrentRow.Cells(2).Value
        name = Me.gridclientes.CurrentRow.Cells(3).Value
        lastname = Me.gridclientes.CurrentRow.Cells(4).Value
        nickname = Me.gridclientes.CurrentRow.Cells(5).Value
        noced = Me.gridclientes.CurrentRow.Cells(6).Value
        business = Me.gridclientes.CurrentRow.Cells(7).Value
        phone = Me.gridclientes.CurrentRow.Cells(8).Value

[Santanu.Das]

No, this is quite right. But, on my opinion your problem is in Data insertion portion. I do not understand why are you use the line conn.State.ToString() and why are you close the connection on that position.
I make some modification in your entire codification. It could solve your problem.

   Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        If Me.txtmontoprestamo.Text = "" Then
            MsgBox("Debe digitar el monto a prestar.", MsgBoxStyle.Critical)
            Exit Sub
        Else
            Dim name As String = Me.gridclientes.CurrentRow.Cells(3).Value
            Dim lastname As String = Me.gridclientes.CurrentRow.Cells(4).Value
            Dim id As String = Me.gridclientes.CurrentRow.Cells(0).Value
            Dim nickname As String = Me.gridclientes.CurrentRow.Cells(5).Value
            Dim business As String = Me.gridclientes.CurrentRow.Cells(7).Value
            Dim noced As String = Me.gridclientes.CurrentRow.Cells(6).Value
            'Dim shift As String
            Dim pin As Integer = Me.gridclientes.CurrentRow.Cells(2).Value
            Dim bankname As String = Me.gridclientes.CurrentRow.Cells(1).Value
            Dim localuser As String = GetUserName()
            Dim phone As String = Me.gridclientes.CurrentRow.Cells(8).Value

            Call tasaintereses() 'Asignar la tasa de interés según el monto.

            Dim montoprestamo = Decimal.ToInt32(Me.txtmontoprestamo.Text)
            Dim tasainteres = Decimal.ToDouble(Me.txtinteres.Text)



            Dim cmd As OleDb.OleDbCommand
            Dim archivo As String
            archivo = Application.StartupPath
            Dim path As String
            path = archivo & "\prestamos.accdb"

            Dim connection = " Provider=Microsoft.ace.OLEDB.12.0;" & _
                       " Data Source= " & path
            Dim conn As New OleDb.OleDbConnection(connection)
            conn.Open()

            If Me.txtmontoprestamo.Text = "" Then
                MsgBox("Debe digitar el monto del préstamo.", MsgBoxStyle.Information, "Importante")
                Me.txtmontoprestamo.Select()
                Exit Sub
            Else
            End If


            Dim fecha As Date = Format((Now()), "long date")
            If MessageBox.Show("Están todos los datos correctos?.", "Nuevos Usuarios", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                Try
                    'En esta parte va el código para insertar los datos en la base de datos.
                    cmd.CommandType = CommandType.Text
                    cmd = New OleDbCommand("insert into tblprestamosinteres (codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & _
                                        "Values (@codigoprestamo,@fechaprestamo, @idcliente, @nombres, @apellidos, @apodo, @nocedula, @empresa, @PIN, @banco, @montoprestamo, @interes, @usuariointerno)", conn)

                    cmd.Parameters.AddWithValue("@codigoprestamo", empid)
                    cmd.Parameters.AddWithValue("@fechaprestamo", fecha)
                    cmd.Parameters.AddWithValue("@idcliente", id)
                    cmd.Parameters.AddWithValue("@nombres", name)
                    cmd.Parameters.AddWithValue("@apellidos", lastname)
                    cmd.Parameters.AddWithValue("@apodo", nickname)
                    cmd.Parameters.AddWithValue("@nocedula", noced)
                    cmd.Parameters.AddWithValue("@empresa", business)
                    cmd.Parameters.AddWithValue("@PIN", pin)
                    cmd.Parameters.AddWithValue("@banco", bankname)
                    cmd.Parameters.AddWithValue("@montoprestamo", montoprestamo)
                    cmd.Parameters.AddWithValue("@interes", tasainteres)
                    cmd.Parameters.AddWithValue("@usuariointerno", localuser)

                    cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()
                    cmd.Dispose()
                    MsgBox("Database is updated")
                Catch ex As OleDb.OleDbException
                    MessageBox.Show(ex.Message)
                End Try

                MsgBox("Todos los datos fueron insertados correctamente.", MsgBoxStyle.Information)
                Me.txtmontoprestamo.Text = String.Empty
                Me.txtmontointeres.Text = String.Empty
                Me.txtinteres.Text = String.Empty
                Me.txtbuscarnombres.Select()
                gridprestamointeres.DataSource = Nothing

            Else
                'Nothing to do

            End If

            conn.Close()
            conn.Dispose()
        End If

    End Sub

But I do not know the codifications in GetUserName() and tasaintereses().
This was not possible to me to run the codes.
After using the codes if you get the same problem. Don't be exited. Examine all of your database related codes correctly. Wish, you can get your point of mistake.

[Waldis_1]

conn.State.ToString is to know if the connection is open or closed (not further use), GetUserName and tasadeinteres are functions to get the user name logged in the PC and tasadeinteres returns the interest rate of a loan. GetUserName is assigned to localuser variable and tasadeinteres is assigned to a textbox to make the calculation in the form and then assigned to monointeres variable.

[Santanu.Das]

Had you run the codes I posted?.
This is not the proper place to know is the connection open or not.

If the connection State was not opened you got an exception at the line where you assign the SQL Statement to the OleDbCommand Object.

The proper place to check the State of the Connection is at the line where you call the Open() functon for Connection object.

You can do it like this.

Try
            If conn.State = ConnectionState.Open Then conn.Close()

            conn.Open()

        Catch ex As OleDb.OleDbException

            MessageBox.Show(ex.Message)
            Exit Sub

        End Try

[savedlema]

Sorry for the trouble.
I would suggest the following:

Create a new form in your application just for testing this.
Likewise,create a new table in your database.

Put a textbox and a Button1 in your form.
Put a simple code in Button1 that will post just simple text data to your database table. Say, create a code to post just text from a single textbox.
See if this works and come back and give feedback.

[Waldis_1]

It's weird, with your code running in my PC (inside VB2010 project development environment)I inserted 20 records and then problem happened again but when I run the code in my office's PC using the exe file only I entered 80 records without problems (opening and closing the form several times). My laptop has Windows 8.1-Office 2010 and in my workplace Windows 7-Office 2010. As I didn't make many practices at home I will do it when I arrive hoping that the problem is solved and so I can pass to the next level. I'll let you know the results later.

[Santanu.Das]

My environment also Windows7 and vs2010.
These type could be happened for several reaason.
Check your "Advance Compile Options" of your project in "ProjectProperties>Compile" Tab. Make sure the "Tergate CPU" is "x86".

Secondly, another reason could be "Direction" Less Parameters.

You can try it by a small modification

1) Add a "New" keyword before ObjectType where you declare the OleDbCommand variable, like this

Dim cmd As New OleDb.OleDbCommand

2) Replace the following section

cmd = New OleDbCommand("insert into tblprestamosinteres (codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & _
                                        "Values (@codigoprestamo,@fechaprestamo, @idcliente, @nombres, @apellidos, @apodo, @nocedula, @empresa, @PIN, @banco, @montoprestamo, @interes, @usuariointerno)", conn)
                    cmd.Parameters.AddWithValue("@codigoprestamo", empid)
                    cmd.Parameters.AddWithValue("@fechaprestamo", fecha)
                    cmd.Parameters.AddWithValue("@idcliente", id)
                    cmd.Parameters.AddWithValue("@nombres", name)
                    cmd.Parameters.AddWithValue("@apellidos", lastname)
                    cmd.Parameters.AddWithValue("@apodo", nickname)
                    cmd.Parameters.AddWithValue("@nocedula", noced)
                    cmd.Parameters.AddWithValue("@empresa", business)
                    cmd.Parameters.AddWithValue("@PIN", pin)
                    cmd.Parameters.AddWithValue("@banco", bankname)
                    cmd.Parameters.AddWithValue("@montoprestamo", montoprestamo)
                    cmd.Parameters.AddWithValue("@interes", tasainteres)
                    cmd.Parameters.AddWithValue("@usuariointerno", localuser)

by the lines

cmd.CommandText = "insert into tblprestamosinteres (codigoprestamo, fechaprestamo, idcliente, nombres, apellidos, apodo, nocedula, empresa, pin, banco, montoprestamo, interes, usuariointerno) " & _
                          "Values (@codigoprestamo,@fechaprestamo, @idcliente, @nombres, @apellidos, @apodo, @nocedula, @empresa, @PIN, @banco, @montoprestamo, @interes, @usuariointerno)"

        cmd.Parameters.Add("@codigoprestamo", OleDbType.VarChar, 50).Value = empid
        cmd.Parameters("@codigoprestamo").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@fechaprestamo", OleDbType.VarChar, 50).Value = fecha
        cmd.Parameters("@fechaprestamo").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@idcliente", OleDbType.VarChar, 50).Value = id
        cmd.Parameters("@idcliente").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@nombres", OleDbType.VarChar, 50).Value = Name
        cmd.Parameters("@nombres").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@apellidos", OleDbType.VarChar, 50).Value = lastname
        cmd.Parameters("@apellidos").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@apodo", OleDbType.VarChar, 50).Value = nickname
        cmd.Parameters("@apodo").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@nocedula", OleDbType.VarChar, 50).Value = noced
        cmd.Parameters("@nocedula").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@empresa", OleDbType.VarChar, 50).Value = business
        cmd.Parameters("@empresa").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@PIN", OleDbType.VarChar, 50).Value = pin
        cmd.Parameters("@PIN").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@banco", OleDbType.VarChar, 50).Value = bankname
        cmd.Parameters("@banco").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@montoprestamo", OleDbType.VarChar, 50).Value = montoprestamo
        cmd.Parameters("@montoprestamo").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@interes", OleDbType.VarChar, 50).Value = tasainteres
        cmd.Parameters("@interes").Direction = ParameterDirection.Input

        cmd.Parameters.Add("@usuariointerno", OleDbType.VarChar, 50).Value = localuser
        cmd.Parameters("@usuariointerno").Direction = ParameterDirection.Input

        cmd.Connection = conn

Please replace the DataTypes and Size as per Database. No need to mention size for Numerical, Boolean,TextBlock or Binary datatypes. Also check the Parameter Names are correct or not.

[Waldis_1]

It's proven that the problem is when the form is opened in Visual Studio environment, I've made many tested opening the application from exe file and everything seems to be fine. Time to go to the next step. Configuration was like you suggested but I didn't change the code.

[Waldis_1]

I think I found the solution, I noticed that some open conections weren't closed (conn.open without conn.closed), I made some tests and seems to be alright. I hope this solution is for real. Thanks for assisting me.

[Santanu.Das]

If your purpose is solved, please mark it solved.