-
Feb 26th, 2018, 05:22 PM
#1
Thread Starter
New Member
[RESOLVED] VB only save one records to access db
Hello.
Iam working on a little software for fun.
Right now i am at "car service maintence"
I have 3 databases connected to this project
Cars
Parts
Service
The first two works fine, i can save,remove,edit etcetc
But the last one "service" iam only able to save once, then i get an error.
Iam sorry that the interface is in Swedish. mostly..
"Data sparad" = Data saved.
And the second windows, explains itself.
As you can see i can save the first record "batteri" 50:-
But when i try to add "Olja" 500:-
ive got an error
I use the exact same code on the savebutton as the other ones wich is
Try
ServiceBindingSource1.EndEdit()
ServiceTableAdapter1.Update(ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
Catch ex As Exception
MessageBox.Show("Error fix your ****")
End Try
The combobox is set to "short text" in access.
Any ideas ?
-
Feb 26th, 2018, 05:54 PM
#2
Re: VB only save one records to access db
Change
Code:
MessageBox.Show("Error fix your ****")
to
Code:
MessageBox.Show(ex.ToString)
Then post back what the error is.
-
Feb 26th, 2018, 06:41 PM
#3
Re: VB only save one records to access db
Yeah, that ex object contains all kinds of interesting information. While I've been known to write messages like that, you really need more information when an exception is thrown, and that object has it. I tend to like ex.Message in error messages, and ex.ToString for logging. Nobody really likes to see what comes out of ex.ToString, but it sure can be useful.
My usual boring signature: Nothing
-
Feb 26th, 2018, 09:09 PM
#4
Re: VB only save one records to access db
Originally Posted by Robban98422
I have 3 databases connected to this project
Cars
Parts
Service
Hopefully you actually mean one database containing three tables.
-
Feb 27th, 2018, 05:02 AM
#5
Thread Starter
New Member
Re: VB only save one records to access db
-
Feb 27th, 2018, 05:16 AM
#6
Re: VB only save one records to access db
Originally Posted by Robban98422
Hm i have 3 databases
Why? There are reasons to have multiple databases behind one application but it is usually because those databases already exist. If you're creating these databases specifically for your application, you should create just one database and add multiple tables to it. It's crazy to do it any other way.
As for the issue, a concurrency exception is legitimately thrown when two users try to save data that interferes with each other. For example, user A retrieves data from table 1 and then user B retrieves the same data, user B makes changes and saves them and then user A makes changes and tries to save them too. When user A tries to save, their changes would overwrite user B's changes so a concurrency exception is raised when the system detects that the data in the database does not match the data that user A retrieved.
In many cases though, faulty code will cause a concurrency exception to be incorrectly thrown when it does things it shouldn't with the data. For example, if you call Fill on a data adapter to populate a DataTable, then make some changes, then call AcceptChanges, then make more changes, then try to save. In that case, the system thinks that the data you retrieved is what you had after calling AcceptChanges and that will not match what's in the database. There are various other possibilities too, e.g. you try to save data to the wrong columns.
We can't know exactly what you're doing wrong based on the information you provided but it will be something along those lines.
-
Feb 27th, 2018, 08:18 AM
#7
Re: VB only save one records to access db
I dont employ Try blocks until production, instead of using that msgbox you should simply comment the try block and it will break where the problem is.
Your problem can be resolved by clearing the rows in your datatable after update then refilling
Finally, ditch Mr. Wizard and learn about DataAdapters
...just my 2 cents
-
Feb 27th, 2018, 01:00 PM
#8
Re: VB only save one records to access db
Yeah, jmc is right. You should only use one database. That database would contain your three tables, no need to have a separate database for each table, it just complicate things.
As for your error, we can't tell what's causing the concurrency problem without seeing the relevant code.
-
Feb 27th, 2018, 01:09 PM
#9
Re: VB only save one records to access db
His error is coming from inserting a new row vs updating an existing row. Need to refill the datatable after the update and he will be fine.
-
Feb 27th, 2018, 01:32 PM
#10
Re: VB only save one records to access db
Originally Posted by kpmc
His error is coming from inserting a new row vs updating an existing row. Need to refill the datatable after the update and he will be fine.
You do not have to refill the datatable after calling the Tableadapter.Update method. Unless your using multiple tableadapters/datatables for the same database table for some strange reason.
This example
Code:
Public Class Form2
Private Sub CropsBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles CropsBindingNavigatorSaveItem.Click
Me.Validate()
Me.CropsBindingSource.EndEdit()
Dim var As Integer = Me.CropsTableAdapter.Update(Me.WaterDataSet.Crops)
End Sub
Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'WaterDataSet.Crops' table. You can move, or remove it, as needed.
Me.CropsTableAdapter.Fill(Me.WaterDataSet.Crops)
End Sub
End Class
You can Add/Delete/Update rows and press Save as many times as you want and never have to refill the datatable.
-
Feb 27th, 2018, 01:57 PM
#11
Re: VB only save one records to access db
The TableAdapter must have some kind of vodo outside of the dataadapter, such as refilling the datatable after an insert
-
Feb 27th, 2018, 02:08 PM
#12
Re: VB only save one records to access db
Originally Posted by kpmc
The TableAdapter must have some kind of vodo outside of the dataadapter, such as refilling the datatable after an insert
There's no vodo, the dataadapter acts the same.
Code:
Imports System.Data.SqlClient
Public Class Form9
Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
Private da As New SqlDataAdapter("Select BookId, BookName from Books Order By BookId", con)
Private dt As New DataTable
Private cmdbld As New SqlCommandBuilder(da)
Private bs As New BindingSource
Private Sub Form9_Load(sender As Object, e As EventArgs) Handles Me.Load
da.Fill(dt)
bs.DataSource = dt
Me.DataGridView1.DataSource = bs
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
bs.EndEdit()
da.Update(dt)
End Sub
End Class
As long as you use the datatable for your Add/Edit/Deletes, you never have to refill the datatable.
When you call the TableAdapter/Dataadapter Update method, the database table is updated with the modified rows from the datatable. Then the modified datatable rows rowstate is set to UnChanged.
-
Feb 27th, 2018, 02:13 PM
#13
Re: VB only save one records to access db
Actually your code will throw the same exception he is getting when inserting a new row.
-
Feb 27th, 2018, 02:21 PM
#14
Re: VB only save one records to access db
Originally Posted by kpmc
Actually your code will throw the same exception he is getting when inserting a new row.
No it wont. Test it, I have.
-
Feb 27th, 2018, 02:26 PM
#15
Re: VB only save one records to access db
at some point it will throw the exception. maybe its when you try to update the new row.
-
Feb 27th, 2018, 02:26 PM
#16
Re: VB only save one records to access db
Even if you insert a new row manually without using the datagridview, you still don't need to refill the datatable.
Code:
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Dim row As DataRow = dt.NewRow
row("BookName") = "Insert Row"
dt.Rows.Add(row)
End Sub
-
Feb 27th, 2018, 02:43 PM
#17
Re: VB only save one records to access db
Originally Posted by kpmc
at some point it will throw the exception. maybe its when you try to update the new row.
This can happen if your using an Identity/AutoNumber field and didn't take the step of retrieving the value when you updated the database. You can do that using the dataadapters "RowUpdated" event.
btw - Yes the TableAdapter does retrieve that value for you (Sql Server does). To be honest, I avoid using Identity/AutoNumber field because of these types of problems. I just create the new rows primary key
myself before I call the Update method.
Last edited by wes4dbt; Feb 27th, 2018 at 03:59 PM.
-
Feb 27th, 2018, 02:46 PM
#18
Thread Starter
New Member
Re: VB only save one records to access db
Thanks for all the help, i have some coding to try out.
I wish i could just show you everything at once,
Becouse i have 2 working databases with the exact same settings on the same application that works like a charm,
And ofc i can see now that i only need one database with three tables, i did begin programming two weeks ago with visual basic, so yeah i do the noob misstakes
Maybe 3 databases at one application is cousing trouble.
I dont know if you have the time, or if this is giving you any knowledge, but will input the whole formcode
Code:
Imports System.Data.OleDb
Imports System.IO
Public Class Form2
Dim imgName As String
Dim daImage As OleDbDataAdapter
Dim dsImage As DataSet
Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'ServiceDataSet1.Service' table. You can move, or remove it, as needed.
Me.ServiceTableAdapter1.Fill(Me.ServiceDataSet1.Service)
'TODO: This line of code loads data into the 'ServiceDataSet.Service' table. You can move, or remove it, as needed
'TODO: This line of code loads data into the 'Parts1DataSet.Parts' table. You can move, or remove it, as needed.
Me.PartsTableAdapter.Fill(Me.Parts1DataSet.Parts)
'TODO: This line of code loads data into the 'WranglerDataSet.Wrangler' table. You can move, or remove it, as needed.
Me.WranglerTableAdapter.Fill(Me.WranglerDataSet.Wrangler)
End Sub
Private Sub WranglerBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) Handles WranglerBindingNavigatorSaveItem.Click
Me.Validate()
Me.WranglerBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.WranglerDataSet)
End Sub
Private Sub TabPage1_Click(sender As Object, e As EventArgs) Handles TabPage1.Click
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
WranglerBindingSource.EndEdit()
WranglerTableAdapter.Update(WranglerDataSet.Wrangler)
MessageBox.Show("Data sparad")
Catch ex As Exception
MessageBox.Show("Error fix your ****")
End Try
End Sub
Private Sub NavNext_Click(sender As Object, e As EventArgs) Handles BtnNext.Click
WranglerBindingSource.MoveNext()
End Sub
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Try
Dim dlgImage As FileDialog = New OpenFileDialog()
dlgImage.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
If dlgImage.ShowDialog() = DialogResult.OK Then
imgName = dlgImage.FileName
Dim newimg As New Bitmap(imgName)
CarPicPictureBox.SizeMode = PictureBoxSizeMode.StretchImage
CarPicPictureBox.Image = DirectCast(newimg, Image)
End If
dlgImage = Nothing
Catch ae As System.ArgumentException
imgName = " "
MessageBox.Show(ae.Message.ToString())
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
Try
Dim dlgImage As FileDialog = New OpenFileDialog()
dlgImage.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
If dlgImage.ShowDialog() = DialogResult.OK Then
imgName = dlgImage.FileName
Dim newimg As New Bitmap(imgName)
CarkvittoPictureBox.SizeMode = PictureBoxSizeMode.StretchImage
CarkvittoPictureBox.Image = DirectCast(newimg, Image)
End If
dlgImage = Nothing
Catch ae As System.ArgumentException
imgName = " "
MessageBox.Show(ae.Message.ToString())
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
Private Sub CarPicPictureBox_Click(sender As Object, e As EventArgs) Handles CarPicPictureBox.Click
Using f2 As New Form3(CarPicPictureBox.Image)
f2.ShowDialog()
End Using
End Sub
Private Sub CarkvittoPictureBox_Click(sender As Object, e As EventArgs) Handles CarkvittoPictureBox.Click
Using f2 As New Form3(CarkvittoPictureBox.Image)
f2.ShowDialog()
End Using
End Sub
Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
Form4.Show()
End Sub
Private Sub PartNameLabel_Click(sender As Object, e As EventArgs)
End Sub
Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
PartsBindingSource.MoveNext()
End Sub
Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
PartsBindingSource.AddNew()
End Sub
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
Try
PartsBindingSource.EndEdit()
PartsTableAdapter.Update(Parts1DataSet.Parts)
MessageBox.Show("Data sparad")
Catch ex As Exception
MessageBox.Show("Error fix your ****")
End Try
End Sub
Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
PartsBindingSource.MovePrevious()
End Sub
Private Sub Parts_PositionChanged(sender As Object, e As EventArgs) Handles PartsBindingSource.PositionChanged
TextBox1.Text = String.Format("{0} av {1}", PartsBindingSource.Position + 1, PartsBindingSource.Count)
End Sub
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
End Sub
Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
Try
Dim dlgImage As FileDialog = New OpenFileDialog()
dlgImage.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
If dlgImage.ShowDialog() = DialogResult.OK Then
imgName = dlgImage.FileName
Dim newimg As New Bitmap(imgName)
PartIMGPictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
PartIMGPictureBox1.Image = DirectCast(newimg, Image)
End If
dlgImage = Nothing
Catch ae As System.ArgumentException
imgName = " "
MessageBox.Show(ae.Message.ToString())
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
Private Sub PartIMGPictureBox_Click(sender As Object, e As EventArgs) Handles PartIMGPictureBox.Click
Using f2 As New Form3(PartIMGPictureBox.Image)
f2.ShowDialog()
End Using
End Sub
Private Sub PartKvittoPictureBox_Click(sender As Object, e As EventArgs) Handles PartKvittoPictureBox.Click
Using f2 As New Form3(PartKvittoPictureBox.Image)
f2.ShowDialog()
End Using
End Sub
Private Sub Button11_Click(sender As Object, e As EventArgs) Handles Button11.Click
Dim total As String = 0
For i As Integer = 0 To PartsDataGridView.RowCount - 1
total += PartsDataGridView.Rows(i).Cells(1).Value
'Change the number 2 to your column index number (The first column has a 0 index column)
'In this example the column index of Price is 2
Next
txtTotal.Text = total + (" kr")
End Sub
Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
Try
Dim dlgImage As FileDialog = New OpenFileDialog()
dlgImage.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"
If dlgImage.ShowDialog() = DialogResult.OK Then
imgName = dlgImage.FileName
Dim newimg As New Bitmap(imgName)
PartKvittoPictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
PartKvittoPictureBox1.Image = DirectCast(newimg, Image)
End If
dlgImage = Nothing
Catch ae As System.ArgumentException
imgName = " "
MessageBox.Show(ae.Message.ToString())
Catch ex As Exception
MessageBox.Show(ex.Message.ToString())
End Try
End Sub
Private Sub Button12_Click(sender As Object, e As EventArgs) Handles Button12.Click
Application.Exit()
End
End Sub
Private Sub Button13_Click(sender As Object, e As EventArgs) Handles Button13.Click
WranglerBindingSource.MovePrevious()
End Sub
Private Sub Button15_Click(sender As Object, e As EventArgs) Handles Button15.Click
WranglerBindingSource.AddNew()
End Sub
Private Sub Button14_Click(sender As Object, e As EventArgs)
End Sub
Private Sub TextBox5_TextChanged(sender As Object, e As EventArgs) Handles TextBox5.TextChanged
End Sub
Private Sub Button14_Click_1(sender As Object, e As EventArgs) Handles Button14.Click
TextBox5.Text = TextBox2.Text * TextBox3.Text / 2540 * 2 + TextBox4.Text
End Sub
Private Sub Button16_Click(sender As Object, e As EventArgs) Handles Button16.Click
txtWatt.Text = txtVolt.Text * txtAmp.Text
End Sub
Private Sub PartIMGPictureBox1_Click(sender As Object, e As EventArgs) Handles PartIMGPictureBox1.Click
Using f2 As New Form3(PartIMGPictureBox1.Image)
f2.ShowDialog()
End Using
End Sub
Private Sub PartKvittoPictureBox1_Click(sender As Object, e As EventArgs) Handles PartKvittoPictureBox1.Click
Using f2 As New Form3(PartKvittoPictureBox1.Image)
f2.ShowDialog()
End Using
End Sub
Private Sub Button17_Click(sender As Object, e As EventArgs) Handles btnrapport.Click
PartReport.Show()
End Sub
Private Sub MetroComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs)
End Sub
Private Sub TypeComboBox_SelectedIndexChanged(sender As Object, e As EventArgs)
End Sub
Private Sub PictureBox3_Click(sender As Object, e As EventArgs)
End Sub
Private Sub Button17_Click_1(sender As Object, e As EventArgs) Handles Button17.Click
ServiceBindingSource1.MovePrevious()
End Sub
Private Sub Button18_Click(sender As Object, e As EventArgs) Handles Button18.Click
ServiceBindingSource1.MoveNext()
End Sub
Private Sub Button19_Click(sender As Object, e As EventArgs) Handles Button19.Click
ServiceBindingSource1.AddNew()
End Sub
Private Sub Button20_Click(sender As Object, e As EventArgs) Handles Button20.Click
Try
ServiceBindingSource1.EndEdit()
ServiceTableAdapter1.Update(ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub
Private Sub TextBox6_TextChanged(sender As Object, e As EventArgs) Handles TextBox6.TextChanged
End Sub
Private Sub Service_PositionChanged(sender As Object, e As EventArgs) Handles ServiceBindingSource1.PositionChanged
TextBox6.Text = String.Format("{0} av {1}", ServiceBindingSource1.Position + 1, ServiceBindingSource1.Count)
End Sub
Private Sub TabPage4_Click(sender As Object, e As EventArgs) Handles TabPage4.Click
End Sub
Private Sub Button21_Click(sender As Object, e As EventArgs) Handles Button21.Click
Dim total As String = 0
For i As Integer = 0 To ServiceDataGridView.RowCount - 1
total += ServiceDataGridView.Rows(i).Cells(1).Value
'Change the number 2 to your column index number (The first column has a 0 index column)
'In this example the column index of Price is 2
Next
TextBox7.Text = total + (" kr")
End Sub
Private Sub TypeComboBox_SelectedIndexChanged_1(sender As Object, e As EventArgs)
End Sub
Private Sub TypeComboBox_SelectedIndexChanged_2(sender As Object, e As EventArgs) Handles TypeComboBox.SelectedIndexChanged
If TypeComboBox.SelectedItem().ToString() = "Olja" Then
IkonPictureBox.Image = My.Resources.Olja
End If
If TypeComboBox.SelectedItem().ToString() = "Glykol" Then
IkonPictureBox.Image = My.Resources.glykol
End If
If TypeComboBox.SelectedItem().ToString() = "Batteri" Then
IkonPictureBox.Image = My.Resources.battery
End If
If TypeComboBox.SelectedItem().ToString() = "Bromsar" Then
IkonPictureBox.Image = My.Resources.broms
End If
If TypeComboBox.SelectedItem().ToString() = "Vindrutetorkare" Then
IkonPictureBox.Image = My.Resources.wipers
End If
If TypeComboBox.SelectedItem().ToString() = "Tändstift" Then
IkonPictureBox.Image = My.Resources.spark
End If
If TypeComboBox.SelectedItem().ToString() = "Luftfilter" Then
IkonPictureBox.Image = My.Resources.Luftfilter
End If
If TypeComboBox.SelectedItem().ToString() = "Annat" Then
IkonPictureBox.Image = My.Resources.other
End If
End Sub
Private Sub ServiceDataGridView_CellContentClick(sender As Object, e As DataGridViewCellEventArgs)
End Sub
Private Sub Button22_Click(sender As Object, e As EventArgs) Handles Button22.Click
ServiceBindingSource1.RemoveCurrent()
End Sub
Private Sub ServiceDataGridView_CellContentClick_1(sender As Object, e As DataGridViewCellEventArgs) Handles ServiceDataGridView.CellContentClick
End Sub
End Class
And as i said, i have only done this for two weeks, so the code would prob wont be good, but hey, iam learning
Last edited by si_the_geek; Feb 27th, 2018 at 02:53 PM.
Reason: altered tags
-
Feb 27th, 2018, 02:57 PM
#19
Re: VB only save one records to access db
Originally Posted by wes4dbt
This can happen if your using an Identity/AutoNumber field and didn't take the step of retrieving the value when you updated the database. You can do that using the dataadapters "RowUpdated" event.
btw - Yes the TableAdapter does retrieve that value for you. To be honest, I avoid using Identity/AutoNumber field because of these types of problems. I just create the new rows primary key
myself before I call the Update method.
Ive actually never considered that condition. A very astute observation on your part. I will surly be kicking this around next chance I get
-
Feb 27th, 2018, 02:58 PM
#20
Re: VB only save one records to access db
Rob,
Don't insert the code using the Quote. Use the "#" then insert the code. That way your code will retain it's formatting and make it a lot easier for use to read.
-
Feb 27th, 2018, 04:10 PM
#21
Re: VB only save one records to access db
I can't see the problem, but I did notice the Service tableadapter/DataSet has a "1" on the end and the Part and Wrangler didn't. It makes me wonder if you might have 2 Service tableadapters/datasets on the form. You call other forms and I don't know if they change the Service data in any way.
You could try kpmc suggestion and refill the datatable.
Code:
ServiceBindingSource1.EndEdit()
ServiceTableAdapter1.Update(ServiceDataSet1.Service)
ServiceTableadapter1.Fill(ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
See if that solves the problem. But you really shouldn't have to refill the datatable if you have properly coded the Service Form.
Also, I would add the "Me" qualifier, because that's used when you first fill the datatable. It shouldn't matter but it's good to be consistent.
Code:
Me.ServiceBindingSource1.EndEdit()
Me.ServiceTableAdapter1.Update(Me.ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
Do you have more than one instance of this form open at any time?
Last edited by wes4dbt; Feb 27th, 2018 at 05:45 PM.
-
Feb 27th, 2018, 05:38 PM
#22
Thread Starter
New Member
Re: VB only save one records to access db
Originally Posted by wes4dbt
I can't see the problem, but I did notice the Service tableadapter/DataSet has a "1" on the end and the Part and Wrangler didn't. It makes me wonder if you might have 2 Service tableadapters/datasets on the form. You call other forms and I don't know if they change the Service data in any way.
You could try kpmc suggestion and refill the datatable.
Code:
ServiceBindingSource1.EndEdit()
ServiceTableAdapter1.Update(ServiceDataSet1.Service)
ServiceTableadapter1.Fill(ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
See if that solves the problem. But you really shouldn't have to refill the datatable if you have properly coded the Service Form.
Also, I would add the "Me" qualifier, because that's used when you first fill the datatable. It should mastter but it's good to be consistent.
Code:
Me.ServiceBindingSource1.EndEdit()
Me.ServiceTableAdapter1.Update(Me.ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
Do you have more than one instance of this form open at any time?
Yeah i tought i did something wrong on one of the steps when connecting to the database, so i added the service database again and then ive got the "1" in the end.
and i could not figure out how to delete a database from the project
Will try your suggestions thanks !
And no, i only have one instance open.
-
Feb 27th, 2018, 11:25 PM
#23
Re: VB only save one records to access db
I haven't read all the posts made since my last but, if the issue is that the auto-generated ID is not being retrieved when a record is inserted then the solution for both typed and untyped DataSets, i.e. table adapters or data adapters, can be found here:
http://www.vbforums.com/showthread.p...ert&highlight=
-
Feb 28th, 2018, 04:19 PM
#24
Thread Starter
New Member
Re: VB only save one records to access db
It will seem as everytime this code comes in for a second time
"ServiceTableAdapter1.Update(ServiceDataSet1.Service)"
It crashes.
It works fine the first time a press the "save" button. not the second time.
First i press "add"
"Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
PartsBindingSource.AddNew()"
Then i press "save"
Code:
Try
ServiceBindingSource1.EndEdit()
ServiceTableAdapter1.Update(ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
it gets its info from one combobox three textboxes and one picturebox
Works fine the first time, not a second time..
Think ive gonna need to redo this service tab from scratch.
-
Feb 28th, 2018, 06:50 PM
#25
Re: VB only save one records to access db
Did you try refilling the datatable as kpmc suggested? It would be a good test and provide useful information.
First i press "add"
"Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
PartsBindingSource.AddNew()"
Then i press "save"
Why are you calling AddNew on the PartsBindingSource and not ServiceBindingSource1. Also I see from the picture you posted that the service form has a Datagridview in the top right. Are the TextBoxes and the Datagridview both bound to the same bindingsource and populated by the same datasource. It's hard to understand what's going on. A more complete explanation of how you enter data on the service form would be helpful.
-
Feb 28th, 2018, 07:42 PM
#26
Re: VB only save one records to access db
Are you inserting data and not retrieving the ID(s) generated by the database or not?
-
Mar 3rd, 2018, 12:18 PM
#27
Thread Starter
New Member
Re: VB only save one records to access db
I tried to make a whole nej database project just to see where and when the problem occuer.
And it is when i use the "combo" box in the form,
The database has a hardtime dealing with them ?
EDIT: Solved!
I dont know why on earth i dont need this on the other databases, but i added this line first
"Me.ServiceDataSet1.AcceptChanges()"
And now it works like a charm..
Last edited by Robban98422; Mar 3rd, 2018 at 12:57 PM.
Reason: Solved
-
Mar 3rd, 2018, 03:32 PM
#28
Re: [RESOLVED] VB only save one records to access db
I'm curious, where did you add this line "Me.ServiceDataSet1.AcceptChanges()" . Because when you call the TableAdapters Update method, it basically does an AcceptChanges after it updates the database.
Code:
And it is when i use the "combo" box in the form,
The database has a hardtime dealing with them ?
The database doesn't know anything about where or what you do with the data you retrieve. The database and datatable are not connected. They are reconnected when you call the TableAdapters "Update" method. My guess is your using the same bindingsource/datatable for multiple things, like binding it to textboxes to enter data and using it as the combobox listitems.
-
Mar 3rd, 2018, 05:15 PM
#29
Re: [RESOLVED] VB only save one records to access db
I'd be wanting to study this more, too, because calling AcceptChanges directly is usually a bad thing to do, not a solution.
My usual boring signature: Nothing
-
Mar 3rd, 2018, 06:20 PM
#30
Thread Starter
New Member
Re: [RESOLVED] VB only save one records to access db
Sounds strange for me to becouse i did not need to use this on the other databases.
As iam a beginner and iam 3 weeks in to learning programming, i cant answer your questions but will provide what i got
The "Save" button is coded like this now
Code:
Private Sub Button20_Click(sender As Object, e As EventArgs) Handles Button20.Click
Try
Me.ServiceDataSet1.AcceptChanges()
ServiceBindingSource1.EndEdit()
ServiceTableAdapter1.Update(ServiceDataSet1.Service)
MessageBox.Show("Data sparad")
Catch ex As Exception
MessageBox.Show("Error, något gick fel")
End Try
End Sub
If i remove
Me.ServiceDataSet1.AcceptChanges()
It wont work.
-
Mar 3rd, 2018, 09:32 PM
#31
Re: [RESOLVED] VB only save one records to access db
There is something strange going on,
Either Me.ServiceDataSet1.AcceptChanges() and ServiceDataSet1 refer to two different instants of the dataset or ServiceTableAdapter1.Update(ServiceDataSet1.Service) doesn't actually update anything. Because once you call AcceptChanges on the dataset there will no changes written back to the database when you call the Update method.
This is easy to test because the Update method returns a value that indicates how many rows were modified.
Code:
Private Sub Button20_Click(sender As Object, e As EventArgs) Handles Button20.Click
Try
Me.ServiceDataSet1.AcceptChanges()
ServiceBindingSource1.EndEdit()
Dim int = ServiceTableAdapter1.Update(ServiceDataSet1.Service)
MessageBox.Show(int.ToString )
Catch ex As Exception
MessageBox.Show("Error, något gick fel")
End Try
End Sub
-
Mar 3rd, 2018, 10:35 PM
#32
Re: [RESOLVED] VB only save one records to access db
I agree with Wes4dbt. If you look at the rows in the datatable of the dataset, it will have a RowState property. If you change a row, add a row, or delete a row, then the RowState property changes from Unchanged to something else. When you call Update, all it is really doing is calling Insert for rows with RowState = Added, Update for rows with RowState=Modified, and Delete on rows with RowState = Deleted. What AcceptChanges does is set all the RowState values back to Unchanged, in which case Update will do nothing at all.
That check on the return value of Update is a good one, cause something is not happening correctly.
My usual boring signature: Nothing
-
Mar 4th, 2018, 05:35 AM
#33
Thread Starter
New Member
Re: [RESOLVED] VB only save one records to access db
Originally Posted by wes4dbt
There is something strange going on,
Either Me.ServiceDataSet1.AcceptChanges() and ServiceDataSet1 refer to two different instants of the dataset or ServiceTableAdapter1.Update(ServiceDataSet1.Service) doesn't actually update anything. Because once you call AcceptChanges on the dataset there will no changes written back to the database when you call the Update method.
This is easy to test because the Update method returns a value that indicates how many rows were modified.
Code:
Private Sub Button20_Click(sender As Object, e As EventArgs) Handles Button20.Click
Try
Me.ServiceDataSet1.AcceptChanges()
ServiceBindingSource1.EndEdit()
Dim int = ServiceTableAdapter1.Update(ServiceDataSet1.Service)
MessageBox.Show(int.ToString )
Catch ex As Exception
MessageBox.Show("Error, något gick fel")
End Try
End Sub
i tried your code and the messagebox only showed "1" thats it.
i tought i worked ok, but i realised that i cant edit an already created row and hit save, it will give me error and no change has been saved.
-
Mar 4th, 2018, 03:30 PM
#34
Re: [RESOLVED] VB only save one records to access db
If "int" equals 1 then you have problems it should be 0. Me.ServiceDataSet1 and ServiceDataSet1 are actually two different instances of the ServiceDataSet1 dataset. That really shouldn't happen, I think you may need to delete that form and create a new one. If you do, then I would suggest taking the time to do testing after each step, that will make it easier to stop the problem.
Also, I mentioned putting "Me" in front of all instances of the dataset and kpmc suggested refilling the datatable but it doesn't look like you tried either. Even with creating a new form your going have to address the issue of "ID" field that is a Autonumber. jmc has given you a link to a solution or you could use kpmc suggestion.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|