Problems saving data using BindingNavigator and BindingSource

[MRM256]

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

Continue reading...