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.
Waldis_1 0 Newbie Poster
Reverend Jim 4,968 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster
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 0 Newbie Poster
How can I solve that?, the application's path in the code is for the main database.
Santanu.Das 125 Santanu Das
Post your snippet to understand your problem.
Waldis_1 0 Newbie Poster
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 0 Newbie Poster
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 125 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 0 Newbie Poster
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 125 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 0 Newbie Poster
Sorry, it didn't work. It worked several times and then all is lost. it happens when I reopen the form.
Waldis_1 0 Newbie Poster
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 125 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 0 Newbie Poster
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 125 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 17 Junior Poster
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 0 Newbie Poster
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 125 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 0 Newbie Poster
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 0 Newbie Poster
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 125 Santanu Das
If your purpose is solved, please mark it solved.
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.