R
Rinso
Guest
VB.Net 3.5, VS2008 with Access 2013 dB behind
I have been trying for 3 days now to get a rather simple databinding to update a database using a data adapter. I have another form for another db table that has almost identical coding and works.
My Data consists of one row only in the database and is created using a Data AdapterSelect statement "Select * FROM Preferences" as follows;
Public Function CreatePreferencesDataAdapter(ByVal selectCommand As String, _
ByVal connection As OleDbConnection) As OleDbDataAdapter
Try
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(selectCommand, connection)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
adapter.InsertCommand = New OleDbCommand("INSERT INTO Preferences (EclecticStartDate, EclecticEndDate, ChampionshipDate1, " & _
"ChampionshipDate2, ChampionshipDate3, ChampionshipDate4, " & _
"CourseId, GradeId, LadiesCompFee, MensCompFee, ProShopCompFeePercent, EclecticDivisor) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?);")
adapter.UpdateCommand = New OleDbCommand("UPDATE Preferences SET EclecticStartDate = ?, EclecticEndDate = ?, ChampionshipDate1 = ?, " & _
"ChampionshipDate2 = ?, ChampionshipDate3 = ?, ChampionshipDate4 = ?, " & _
"CourseId = ?, GradeId = ?, LadiesCompFee = ?, MensCompFee = ?, ProShopCompFeePercent = ?, EclecticDivisor = ? " & _
"WHERE PreferenceId = ?;")
adapter.DeleteCommand = New OleDbCommand("DELETE FROM Preferences WHERE PreferencesId = ?;")
Create the parameters.
With adapter.InsertCommand.Parameters
.Add("EclecticStartDate", OleDbType.Date, 7, "EclecticStartDate")
.Add("EclecticEndDate", OleDbType.Date, 7, "EclecticEndDate")
.Add("ChampionshipDate1", OleDbType.Date, 7, "ChampionshipDate1")
.Add("ChampionshipDate2", OleDbType.Date, 7, "ChampionshipDate2")
.Add("ChampionshipDate3", OleDbType.Date, 7, "ChampionshipDate3")
.Add("ChampionshipDate4", OleDbType.Date, 7, "ChampionshipDate4")
.Add("CourseId", OleDbType.BigInt, 8, "CourseId")
.Add("GradeId", OleDbType.BigInt, 8, "GradeId")
.Add("LadiesCompFee", OleDbType.Single, 4, "LadiesCompFee")
.Add("MensCompFee", OleDbType.Single, 4, "MensCompFee")
.Add("ProShopCompFeePercent", OleDbType.Single, 4, "ProShopCompFeePercent")
.Add("EclecticDivisor", OleDbType.Integer, 4, "EclecticDivisor")
End With
With adapter.UpdateCommand.Parameters
.Add("EclecticStartDate", OleDbType.Date, 7, "EclecticStartDate")
.Add("EclecticEndDate", OleDbType.Date, 7, "EclecticEndDate")
.Add("ChampionshipDate1", OleDbType.Date, 7, "ChampionshipDate1")
.Add("ChampionshipDate2", OleDbType.Date, 7, "ChampionshipDate2")
.Add("ChampionshipDate3", OleDbType.Date, 7, "ChampionshipDate3")
.Add("ChampionshipDate4", OleDbType.Date, 7, "ChampionshipDate4")
.Add("CourseId", OleDbType.BigInt, 8, "CourseId")
.Add("GradeId", OleDbType.BigInt, 8, "GradeId")
.Add("LadiesCompFee", OleDbType.Single, 4, "LadiesCompFee")
.Add("MensCompFee", OleDbType.Single, 4, "MensCompFee")
.Add("ProShopCompFeePercent", OleDbType.Single, 4, "ProShopCompFeePercent")
.Add("EclecticDivisor", OleDbType.Integer, 4, "EclecticDivisor")
.Add("PreferenceId", OleDbType.BigInt, 8, "PreferenceId")
End With
adapter.DeleteCommand.Parameters.Add("PreferenceId", OleDbType.BigInt, 8, "PreferenceId").SourceVersion = DataRowVersion.Original
Return adapter
Catch ex As Exception
MessageBox.Show("Error : " & ex.Message, "CreatePreferencesDataAdapter", MessageBoxButtons.OK)
Return Nothing
End Try
End Function
I then Fill a datatable and add it to the dsGolfScorer dataset. dtGrades has already been done and is working. I also load "Course" data in a similar fashion.
PreferencesDataAdapter.Fill(dtPreferences)
dsGolfScorer.Tables.Add(dtGrades) --- Table 0
dsGolfScorer.Tables.Add(dtPreferences) --- Table 1
I then load my form and set bindings
Private bIsInitialising As Boolean = True
Private WithEvents bsGrades As New BindingSource
Private WithEvents bsPrefs As New BindingSource
Private WithEvents bsCourses As New BindingSource
Public Sub New()
This call is required by the Windows Form Designer.
InitializeComponent()
Add any initialization after the InitializeComponent() call.
bsGrades.DataSource = dsGolfScorer
bsGrades.DataMember = dsGolfScorer.Tables(0).TableName
bsPrefs.DataSource = dsGolfScorer
bsPrefs.DataMember = dsGolfScorer.Tables(1).TableName
bsCourses.DataSource = dsGolfScorer
bsCourses.DataMember = dsGolfScorer.Tables(2).TableName
End Sub
Private Sub frmPreferences_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
bIsInitialising = True
cboGrades.DisplayMember = "GradeName"
cboGrades.ValueMember = "GradeId"
cboGrades.DataSource = bsGrades
Dim bp As Binding = New Binding("SelectedValue", bsPrefs, "GradeId")
cboGrades.DataBindings.Add(bp)
cboCourses.DisplayMember = "CourseName"
cboCourses.ValueMember = "CourseId"
cboCourses.DataSource = bsCourses
Dim bc As Binding = New Binding("SelectedValue", bsPrefs, "CourseId")
cboCourses.DataBindings.Add(bc)
Dim bLCF As Binding = New Binding("Text", bsPrefs, "LadiesCompFee")
Dim bMCF As Binding = New Binding("Text", bsPrefs, "MensCompFee")
Dim bPSP As Binding = New Binding("Text", bsPrefs, "ProShopCompFeePercent")
AddHandler bLCF.Format, AddressOf SingleToCurrencyString
AddHandler bLCF.Parse, AddressOf CurrencyStringToSingle
AddHandler bMCF.Format, AddressOf SingleToCurrencyString
AddHandler bMCF.Parse, AddressOf CurrencyStringToSingle
AddHandler bPSP.Format, AddressOf SingleToPercentString
AddHandler bPSP.Parse, AddressOf PercentStringToSingle
DateTimePickerESD.DataBindings.Add("value", bsPrefs, "EclecticStartDate")
DateTimePickerEED.DataBindings.Add("Value", bsPrefs, "EclecticEndDate")
DateTimePickerCD1.DataBindings.Add("Value", bsPrefs, "ChampionshipDate1")
DateTimePickerCD2.DataBindings.Add("Value", bsPrefs, "ChampionshipDate2")
DateTimePickerCD3.DataBindings.Add("Value", bsPrefs, "ChampionshipDate3")
DateTimePickerCD4.DataBindings.Add("Value", bsPrefs, "ChampionshipDate4")
txtLadiesCompFee.DataBindings.Add(bLCF)
txtMensCompFee.DataBindings.Add(bMCF)
txtProShopPercent.DataBindings.Add(bPSP)
NumericUpDownEclecticDivisor.DataBindings.Add("Value", bsPrefs, "EclecticDivisor")
lblPreferenceId.DataBindings.Add("text", bsPrefs, "PreferenceId")
Catch ex As Exception
MessageBox.Show("Error : " & ex.Message, "Load Preferences", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
bIsInitialising = False
End Try
End Sub
Once I make changes I press a button that fires an Update Sub
Private Sub UpdatedB()
Try
Me.Validate()
GolfScorerConn.ConnectionString = String.Format("{0} {1}", My.Settings.GSCnn, dBaseName)
bsGrades.EndEdit()
bsCourses.EndEdit()
bsPrefs.EndEdit()
PreferencesDataAdapter.InsertCommand.Connection = GolfScorerConn
PreferencesDataAdapter.UpdateCommand.Connection = GolfScorerConn
PreferencesDataAdapter.DeleteCommand.Connection = GolfScorerConn
PreferencesDataAdapter.Update(dtPreferences)
dsGolfScorer.AcceptChanges()
Catch ex As Exception
MessageBox.Show("Error : " & ex.Message, "btnOK", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
This saves changes to the dtPreferences properly - I can exit the form and re-enter and see the new information.
It does not Save these changes to the Access database.
Any help greatly appreciated.
TIA
Continue reading...
I have been trying for 3 days now to get a rather simple databinding to update a database using a data adapter. I have another form for another db table that has almost identical coding and works.
My Data consists of one row only in the database and is created using a Data AdapterSelect statement "Select * FROM Preferences" as follows;
Public Function CreatePreferencesDataAdapter(ByVal selectCommand As String, _
ByVal connection As OleDbConnection) As OleDbDataAdapter
Try
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(selectCommand, connection)
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
adapter.InsertCommand = New OleDbCommand("INSERT INTO Preferences (EclecticStartDate, EclecticEndDate, ChampionshipDate1, " & _
"ChampionshipDate2, ChampionshipDate3, ChampionshipDate4, " & _
"CourseId, GradeId, LadiesCompFee, MensCompFee, ProShopCompFeePercent, EclecticDivisor) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?);")
adapter.UpdateCommand = New OleDbCommand("UPDATE Preferences SET EclecticStartDate = ?, EclecticEndDate = ?, ChampionshipDate1 = ?, " & _
"ChampionshipDate2 = ?, ChampionshipDate3 = ?, ChampionshipDate4 = ?, " & _
"CourseId = ?, GradeId = ?, LadiesCompFee = ?, MensCompFee = ?, ProShopCompFeePercent = ?, EclecticDivisor = ? " & _
"WHERE PreferenceId = ?;")
adapter.DeleteCommand = New OleDbCommand("DELETE FROM Preferences WHERE PreferencesId = ?;")
Create the parameters.
With adapter.InsertCommand.Parameters
.Add("EclecticStartDate", OleDbType.Date, 7, "EclecticStartDate")
.Add("EclecticEndDate", OleDbType.Date, 7, "EclecticEndDate")
.Add("ChampionshipDate1", OleDbType.Date, 7, "ChampionshipDate1")
.Add("ChampionshipDate2", OleDbType.Date, 7, "ChampionshipDate2")
.Add("ChampionshipDate3", OleDbType.Date, 7, "ChampionshipDate3")
.Add("ChampionshipDate4", OleDbType.Date, 7, "ChampionshipDate4")
.Add("CourseId", OleDbType.BigInt, 8, "CourseId")
.Add("GradeId", OleDbType.BigInt, 8, "GradeId")
.Add("LadiesCompFee", OleDbType.Single, 4, "LadiesCompFee")
.Add("MensCompFee", OleDbType.Single, 4, "MensCompFee")
.Add("ProShopCompFeePercent", OleDbType.Single, 4, "ProShopCompFeePercent")
.Add("EclecticDivisor", OleDbType.Integer, 4, "EclecticDivisor")
End With
With adapter.UpdateCommand.Parameters
.Add("EclecticStartDate", OleDbType.Date, 7, "EclecticStartDate")
.Add("EclecticEndDate", OleDbType.Date, 7, "EclecticEndDate")
.Add("ChampionshipDate1", OleDbType.Date, 7, "ChampionshipDate1")
.Add("ChampionshipDate2", OleDbType.Date, 7, "ChampionshipDate2")
.Add("ChampionshipDate3", OleDbType.Date, 7, "ChampionshipDate3")
.Add("ChampionshipDate4", OleDbType.Date, 7, "ChampionshipDate4")
.Add("CourseId", OleDbType.BigInt, 8, "CourseId")
.Add("GradeId", OleDbType.BigInt, 8, "GradeId")
.Add("LadiesCompFee", OleDbType.Single, 4, "LadiesCompFee")
.Add("MensCompFee", OleDbType.Single, 4, "MensCompFee")
.Add("ProShopCompFeePercent", OleDbType.Single, 4, "ProShopCompFeePercent")
.Add("EclecticDivisor", OleDbType.Integer, 4, "EclecticDivisor")
.Add("PreferenceId", OleDbType.BigInt, 8, "PreferenceId")
End With
adapter.DeleteCommand.Parameters.Add("PreferenceId", OleDbType.BigInt, 8, "PreferenceId").SourceVersion = DataRowVersion.Original
Return adapter
Catch ex As Exception
MessageBox.Show("Error : " & ex.Message, "CreatePreferencesDataAdapter", MessageBoxButtons.OK)
Return Nothing
End Try
End Function
I then Fill a datatable and add it to the dsGolfScorer dataset. dtGrades has already been done and is working. I also load "Course" data in a similar fashion.
PreferencesDataAdapter.Fill(dtPreferences)
dsGolfScorer.Tables.Add(dtGrades) --- Table 0
dsGolfScorer.Tables.Add(dtPreferences) --- Table 1
I then load my form and set bindings
Private bIsInitialising As Boolean = True
Private WithEvents bsGrades As New BindingSource
Private WithEvents bsPrefs As New BindingSource
Private WithEvents bsCourses As New BindingSource
Public Sub New()
This call is required by the Windows Form Designer.
InitializeComponent()
Add any initialization after the InitializeComponent() call.
bsGrades.DataSource = dsGolfScorer
bsGrades.DataMember = dsGolfScorer.Tables(0).TableName
bsPrefs.DataSource = dsGolfScorer
bsPrefs.DataMember = dsGolfScorer.Tables(1).TableName
bsCourses.DataSource = dsGolfScorer
bsCourses.DataMember = dsGolfScorer.Tables(2).TableName
End Sub
Private Sub frmPreferences_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
bIsInitialising = True
cboGrades.DisplayMember = "GradeName"
cboGrades.ValueMember = "GradeId"
cboGrades.DataSource = bsGrades
Dim bp As Binding = New Binding("SelectedValue", bsPrefs, "GradeId")
cboGrades.DataBindings.Add(bp)
cboCourses.DisplayMember = "CourseName"
cboCourses.ValueMember = "CourseId"
cboCourses.DataSource = bsCourses
Dim bc As Binding = New Binding("SelectedValue", bsPrefs, "CourseId")
cboCourses.DataBindings.Add(bc)
Dim bLCF As Binding = New Binding("Text", bsPrefs, "LadiesCompFee")
Dim bMCF As Binding = New Binding("Text", bsPrefs, "MensCompFee")
Dim bPSP As Binding = New Binding("Text", bsPrefs, "ProShopCompFeePercent")
AddHandler bLCF.Format, AddressOf SingleToCurrencyString
AddHandler bLCF.Parse, AddressOf CurrencyStringToSingle
AddHandler bMCF.Format, AddressOf SingleToCurrencyString
AddHandler bMCF.Parse, AddressOf CurrencyStringToSingle
AddHandler bPSP.Format, AddressOf SingleToPercentString
AddHandler bPSP.Parse, AddressOf PercentStringToSingle
DateTimePickerESD.DataBindings.Add("value", bsPrefs, "EclecticStartDate")
DateTimePickerEED.DataBindings.Add("Value", bsPrefs, "EclecticEndDate")
DateTimePickerCD1.DataBindings.Add("Value", bsPrefs, "ChampionshipDate1")
DateTimePickerCD2.DataBindings.Add("Value", bsPrefs, "ChampionshipDate2")
DateTimePickerCD3.DataBindings.Add("Value", bsPrefs, "ChampionshipDate3")
DateTimePickerCD4.DataBindings.Add("Value", bsPrefs, "ChampionshipDate4")
txtLadiesCompFee.DataBindings.Add(bLCF)
txtMensCompFee.DataBindings.Add(bMCF)
txtProShopPercent.DataBindings.Add(bPSP)
NumericUpDownEclecticDivisor.DataBindings.Add("Value", bsPrefs, "EclecticDivisor")
lblPreferenceId.DataBindings.Add("text", bsPrefs, "PreferenceId")
Catch ex As Exception
MessageBox.Show("Error : " & ex.Message, "Load Preferences", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
bIsInitialising = False
End Try
End Sub
Once I make changes I press a button that fires an Update Sub
Private Sub UpdatedB()
Try
Me.Validate()
GolfScorerConn.ConnectionString = String.Format("{0} {1}", My.Settings.GSCnn, dBaseName)
bsGrades.EndEdit()
bsCourses.EndEdit()
bsPrefs.EndEdit()
PreferencesDataAdapter.InsertCommand.Connection = GolfScorerConn
PreferencesDataAdapter.UpdateCommand.Connection = GolfScorerConn
PreferencesDataAdapter.DeleteCommand.Connection = GolfScorerConn
PreferencesDataAdapter.Update(dtPreferences)
dsGolfScorer.AcceptChanges()
Catch ex As Exception
MessageBox.Show("Error : " & ex.Message, "btnOK", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
This saves changes to the dtPreferences properly - I can exit the form and re-enter and see the new information.
It does not Save these changes to the Access database.
Any help greatly appreciated.
TIA
Continue reading...