Results 1 to 34 of 34

Thread: [RESOLVED] VB only save one records to access db

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    Resolved [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 ?
    Attached Images Attached Images   

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB only save one records to access db

    Quote Originally Posted by Robban98422 View Post
    I have 3 databases connected to this project
    Cars
    Parts
    Service
    Hopefully you actually mean one database containing three tables.

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    Re: VB only save one records to access db

    Quote Originally Posted by jmcilhinney View Post
    Hopefully you actually mean one database containing three tables.


    Hm i have 3 databases, that controls 3 diffrents tabs, works fine, execpt this one.


    Name:  Capture.JPG
Views: 473
Size:  66.5 KB

    i can add serval records at once and hit "save" and it will work.

    but i cant save a second time..

    ive have attached the correct error message now. and also how the access db looksName:  db.jpg
Views: 540
Size:  27.1 KB
    cant figure out what i did diffrent on this one.

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: VB only save one records to access db

    Quote Originally Posted by Robban98422 View Post
    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.

  7. #7
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  9. #9
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  10. #10
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: VB only save one records to access db

    Quote Originally Posted by kpmc View Post
    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.

  11. #11
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: VB only save one records to access db

    Quote Originally Posted by kpmc View Post
    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.

  13. #13
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: VB only save one records to access db

    Quote Originally Posted by kpmc View Post
    Actually your code will throw the same exception he is getting when inserting a new row.
    No it wont. Test it, I have.

  15. #15
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    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.

  16. #16
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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

  17. #17
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    Re: VB only save one records to access db

    Quote Originally Posted by kpmc View Post
    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.

  18. #18

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    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

  19. #19
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

    Re: VB only save one records to access db

    Quote Originally Posted by wes4dbt View Post
    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

  20. #20
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  21. #21
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  22. #22

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    Re: VB only save one records to access db

    Quote Originally Posted by wes4dbt View Post
    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.

  23. #23
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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=

  24. #24

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    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.

  25. #25
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  26. #26
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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?

  27. #27

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    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

  28. #28
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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.

  29. #29
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  30. #30

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    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.

  31. #31
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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

  32. #32
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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

  33. #33

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    10

    Re: [RESOLVED] VB only save one records to access db

    Quote Originally Posted by wes4dbt View Post
    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.

  34. #34
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,195

    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
  •  



Click Here to Expand Forum to Full Width