locked
Problems saving data using BindingNavigator and BindingSource RRS feed

  • Question

  • Hi Everyone,

    Can someone recheck my logic?

    I am trying to write the data inside the Customers form into the dbo.tblCustomers table inside the SFDC_SQL_MDI database and it isn't working.

    In the frmCustomer_Load event, I populate the Customers and Countries data tables . Bind the table's columns to the proper controls on the Customers form. Finally, I set the combo boxes to nothing selected.

    Private Sub frmCustomers_Load(sender As Object, _
                                      e As EventArgs) _
                                      Handles Me.Load
            dtCustomers = objSQL.dt_MSSQL_Retrive_Data(strSQLCnn, "tblCustomers")
            dtCountries = objSQL.dt_MSSQL_Retrive_Data(strSQLCnn, "tblCountries")
            Call bs_Customers(dtCustomers, dtCountries)
            'Set the ComboBoxes.SelectedIndex to -1
            'No Selection made
            Country.SelectedIndex = -1
            State.SelectedIndex = -1
    End Sub

    The control binding routine is:

    Private Sub bs_Customers(ByVal dtCustomers As DataTable, _
                                 ByVal dtCountries As DataTable)
            'Purpose:       Binds the Form to the appropriate text 
            '               or combo boxes on the frmCustomers form
            'Parameters:    dtCountries As DataTable - tblCountries in Database
            '               dtCustomers As DataTable - tblCustomers in Database
            '               dtStates As DataTable - tblStates in Database
            'Returns:       Nothing - Information is bound to the form
    
            'Set BindingSources
            'Me.bsCountries = New BindingSource
            'Me.bsCountries.DataSource = dtCountries
            Me.bsCustomers = New BindingSource
            Me.bsCustomers.DataSource = dtCustomers
            'Me.bsCity = New BindingSource
            'Me.bsCity.DataSource = dtCity
    
            'Have the controls been data bound?
            If Customer.DataBindings.Count > 0 Then
                Exit Sub
            Else
                'Binding the controls to the appropate DataColumn(Field)
                'Deactivated because the User does not need to see it
                'txtCustID.DataBindings.Add() _
                '     ("Text", Me.bsCustomers, "CustID")
                Customer.DataBindings.Add _
                    ("Text", Me.bsCustomers, "CustName", True)
                Identifier.DataBindings.Add _
                    ("Text", Me.bsCustomers, "Identifier", True)
                Address1.DataBindings.Add _
                    ("Text", Me.bsCustomers, "Address_1", True)
                Address2.DataBindings.Add _
                    ("Text", Me.bsCustomers, "Address_2", True)
                Country.DataBindings.Add("SelectedValue", _
                                         Me.bsCustomers, _
                                         "CountryID", True)
                'Sets specific properties for the ComboBox
                Country.ValueMember = "CountryID"
                Country.DisplayMember = "Country"
                Country.DataSource = dtCountries
    
                State.DataBindings.Add("SelectedValue", Me.bsCustomers, "StateID", True)
                txtZip.DataBindings.Add _
                    ("Text", Me.bsCustomers, "Zip", True)
    
                navCustomers.BindingSource = Me.bsCustomers
            End If
    End Sub
    

    The code to "save" the information inside the Customers form is:

    Private Sub SaveToolStripButton_Click(sender As Object, _
                                              e As EventArgs) _
                                              Handles SaveToolStripButton.Click
            'Purpose:           Save changes into the Customers Table
            'Local Variables:   daSQL As SqlDataAdapter
            '                   cbSQL As SqlCommandBuilder
            '                   strSQL As String
            'Returns:           Nothing - Table is updated inside the subroutine
            Dim daSQL As SqlDataAdapter
            Dim cbSQL As SqlCommandBuilder
            Dim strSQL As String
            'End edits to Navigator control
            navCustomers.BindingSource.EndEdit()
            Using cnn As New SqlConnection(strSQLCnn)
                Try
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    Else
                        cnn.Open()
                        strSQL = "SELECT * FROM dbo.tblCustomers;"
                        daSQL = New SqlDataAdapter(strSQL, cnn)
                        'Build Commands for add, delete, and update
                        cbSQL = New SqlCommandBuilder(daSQL)
                        'Update the Customer table
                        daSQL.Update(bsCustomers.DataSource)
                        Msg = "The dbo.tblCustomers table from the SFDC_SQL_MDI " & _
                              "database has been successfully updated."
                        MessageBox.Show(Msg)
                    End If
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                End Try
            End Using
    End Sub
    

    I think I am not using something properly in the SaveToolStripButton_Click event. When I step through the event I activated I the DataSet Visualizer at daSQL.Update(bsCustomers.DataSource) It shows an empty table.

    What is going wrong?




    MRM256

    Saturday, August 11, 2018 5:04 PM

All replies

  • It looks like you are making it far more sophisticated than it should be.

    Your problem, maybe the fact you are not calling .EndEdit on the bindingsource prior to the update, or at least I dont see the call in your code anyplace


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Saturday, August 11, 2018 5:07 PM
  • Hi Gtripodi,

    It is under the strSQL declaration line.

     Dim strSQL As String
     'End edits to Navigator control
      navCustomers.BindingSource.EndEdit()


    MRM256

    Saturday, August 11, 2018 5:11 PM
  • Hi Gtripodi,

    It is under the strSQL declaration line.

     Dim strSQL As String
     'End edits to Navigator control
      navCustomers.BindingSource.EndEdit()


    MRM256

    ok, so when you say that it is not working, are there any exceptions? what exactly is not working

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Saturday, August 11, 2018 5:13 PM
  • Is this homework?


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Saturday, August 11, 2018 5:20 PM
  • The CommandBuilder isn't being used correctly... you need to set the select command on the adapter before adding the command builder.  Then the changes have to occur after that is setup, otherwise they won't be seen.

    Review the documentation here to understand how it works.

    It doesn't look like your current data access strategy lends itself well to using auto generated commands...


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Saturday, August 11, 2018 6:55 PM
  • Sorry for the delay in responding.

    What I mean by "Not working" is it is running through the code with NO errors, but the information is NOT written into the database table.

    My guess is there is a disconnect somewhere between my BindingSource and BindingNavigator controls.

    Thanks,


    MRM256

    Sunday, August 12, 2018 3:13 PM
  • Hi,

    When the datatable is not obtained from the data source table, but is customized (obtained from other data sources, or simply built manually, obtained from the modified datagridview), there is no relative deletion and modification concept above.

    modify the code: daSQL.Update(bsCustomers.DataSource)

     daSQL.Update(table)

    Can you understand what I mean?

    Best Regards,

    Alex



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, August 13, 2018 6:53 AM
  • Hello again,

    This is a pet project and I can only work on it during my off days. Currently, my off days are; Friday, Saturday, and Sunday.

    I still have not found the cause as to why the data entered into the Customer's form is not being written into the SQL Server database table tblCustomers.

    I enter data into the form:

    The form controls are suppose to be bound to the proper columns in the database's table tblCustomers. However, I can not find a way to look at what is stored in the BindingSource. 

    So it might be a good idea to first see if anything is inside the binding source. How do we view what is inside a BindingSource?

    Thanks,


    MRM256

    Friday, August 17, 2018 1:37 PM
  • Hi,

    I checked some information, the  Datasource needs to be implemented to add, delete, update the corresponding SQL statement.

    Try adding the following code before the code.

     Dim dataDir As String = AppDomain.CurrentDomain.BaseDirectory
    
            If dataDir.EndsWith("\bin\Debug\") OrElse dataDir.EndsWith("\bin\Release\") Then
                dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDir)
            End If

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 22, 2018 6:03 AM