none
VB2010 Dataset problem RRS feed

  • Question

  • I Create at two table in MS access and one is Employee table and the second one is education table

    and I load it to VB2010.

    and now in my dataset  i have the code :

    Imports System.Data.OleDb

    Public Class AccessIDHelper
        ''' <summary>
        ''' Retrieves the primary key autonumber values from Access
        ''' </summary>
        ''' <remarks></remarks>
        Public Shared Sub SetPrimaryKey(ByVal trans As OleDbTransaction, _
                                        ByVal e As OleDbRowUpdatedEventArgs)
            If e.Status = UpdateStatus.Continue AndAlso _
               e.StatementType = StatementType.Insert Then

                ' If this is an INSERT operation...
                Dim pk = e.Row.Table.PrimaryKey
                ' and a primary key column exists...
                If pk IsNot Nothing AndAlso pk.Count = 1 Then
                    Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", trans.Connection, trans)
                    ' Execute the post-update query to fetch new @@Identity
                    e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar)
                    e.Row.AcceptChanges()
                End If
            End If
        End Sub
    End Class

    Namespace EDDataSetTableAdapters

        Partial Public Class EMrecordTableAdapter

            Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                            ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                            Handles _adapter.RowUpdated

                AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
            End Sub
        End Class

        Partial Public Class EducationTableAdapter

            Private Sub _adapter_RowUpdated(ByVal sender As Object, _
                                            ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs) _
                                            Handles _adapter.RowUpdated

                AccessIDHelper.SetPrimaryKey(Me.Transaction, e)
            End Sub
        End Class
    End Namespace

    Partial Class EDDataSet

        Partial Class EmrecordDataTable
            Private Sub EmrecordDataTable_TableNewRow(ByVal sender As Object, _
                                                        ByVal e As System.Data.DataTableNewRowEventArgs) _
                                                        Handles Me.TableNewRow
                'Set defaults so that constraints don't fail when EndEdit is called
                Dim cat = CType(e.Row, EmrecordRow)
                cat.FirstName = "[new]"
            End Sub
        End Class

        Partial Class EducationDataTable

            Private Sub EducationDataTable_TableNewRow(ByVal sender As Object, _
                                                      ByVal e As System.Data.DataTableNewRowEventArgs) _
                                                      Handles Me.TableNewRow
                'Set defaults so that constraints don't fail when EndEdit is called
                Dim product = CType(e.Row, EducationRow)
                ' product.Discontinued = False
                product.Title = "[new]"
            End Sub
        End Class

    End Class

    and in my form this is my code:

    Public Class Form2

        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

            Me.WindowState = FormWindowState.Maximized

        End Sub

        Private Sub EmRecordBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmRecordBindingNavigatorSaveItem.Click
            Me.Validate()
            Me.EmRecordBindingSource.EndEdit()
            Me.EducationBindingSource.EndEdit()
            ' Me.EmRecordTableAdapter.Update(Me.EDDataSet.EmRecord)
            'Me.EducationTableAdapter.Update(Me.EDDataSet.Education)
            Me.TableAdapterManager.UpdateAll(Me.EDDataSet)

        End Sub

        Private Sub Form2_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            Me.WindowState = FormWindowState.Maximized

            'TODO: This line of code loads data into the 'EDDataSet.EmRecord' table. You can move, or remove it, as needed.
            Me.EmRecordTableAdapter.Fill(Me.EDDataSet.EmRecord)
            Me.EducationTableAdapter.Fill(Me.EDDataSet.Education)
        End Sub

        Private Sub EducationDataGridView_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles EducationDataGridView.Enter
            EmRecordBindingSource.EndEdit()

        End Sub

        Private Sub QToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles QToolStripMenuItem.Click

        End Sub

        Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click
            End

        End Sub

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)



        End Sub

        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim searchstring As String = TextBox1.Text
            Dim index As Integer = ListBox1.FindString(searchstring)
            If searchstring <> String.Empty Then

                ListBox1.FindString(searchstring)
            End If

            If index <> -1 Then
                ListBox1.SetSelected(index, True)
            Else
            End If
        End Sub

        Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            addForm.Show()
            Me.Hide()

        End Sub
    End Class

    Now my Problem is i add new record is OK

    but when i edit the existing record the problem appears in my tableadapterManager kindly see below the error message.

    Me.TableAdapterManager.UpdateAll(Me.EDDataSet)    Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

     

    Thank you
    Friday, November 4, 2011 2:29 AM

Answers

  • Hi vb newly user,

    Relate to operating database, you can use ADO.NET, here's a sample you can refer to it and if you have problem, please feel free to let me know.

    Public Function CreateCmdsAndUpdate(ByVal connectionString As String, _
        ByVal queryString As String) As DataSet

        Using connection As New SqlConnection(connectionString)
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(queryString, connection)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)

            connection.Open()

            Dim customers As DataSet = New DataSet
            adapter.Fill(customers)

            ' Code to modify data in DataSet here

            adapter.Update(customers)

            Return customers
        End Using
    End Function

     

    After researching, I found these links can help you:

    ADO.NET Architecture

    .Net Framework Data Provider

    ADO.NET Code Example

    Retrieving and Modifying Data in ADO.NET

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Saturday, November 12, 2011 2:26 AM
    Moderator

All replies

  • Hi vb newly user,

        I don't find your dataadapter's updatecommand from your code, have you specify it to your dataadapter? More about the dataadapter's update method, please refer here. If you have any question, please feel free to let me know.

    Best Regards.


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, November 7, 2011 3:06 AM
    Moderator
  • Hi vb newly user,

    Have you solved your issue? If you have any problem, please feel free to post here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, November 10, 2011 3:26 AM
    Moderator
  • No i dont know how to create a update database in code.the code above is for adding and deleting only.please help me how to create a editing and updating database

     

    Saturday, November 12, 2011 12:16 AM
  • Hi vb newly user,

    Relate to operating database, you can use ADO.NET, here's a sample you can refer to it and if you have problem, please feel free to let me know.

    Public Function CreateCmdsAndUpdate(ByVal connectionString As String, _
        ByVal queryString As String) As DataSet

        Using connection As New SqlConnection(connectionString)
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(queryString, connection)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)

            connection.Open()

            Dim customers As DataSet = New DataSet
            adapter.Fill(customers)

            ' Code to modify data in DataSet here

            adapter.Update(customers)

            Return customers
        End Using
    End Function

     

    After researching, I found these links can help you:

    ADO.NET Architecture

    .Net Framework Data Provider

    ADO.NET Code Example

    Retrieving and Modifying Data in ADO.NET

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Saturday, November 12, 2011 2:26 AM
    Moderator