Adding record with controls bound in code

[Greg]

Binding Manager & dataset - won't add record
I've got an untyped dataset with controls bound through code. The user
can select a question number from a bound combobox, and the question
number and question text are displayed in bound textboxes. This part
works fine.
When I go to add a new record, the textboxes clear as they should. I
enter a new question number and tab to the textbox for the question
text. At this point, the text for the previously selected question
(whose number is still displayed in the combobox) reappears. If I then
change the text anyway and try to save, it tries to update the
previously selected record instead of adding a new one. I think it has
something to do with the position of the datset not being changed,
although the bindingmanagerbase position is. This code is almost
directly from chapter 6 in Murach's book, VB.Net database programming
with ado.net. Thanks for any help.

Private Sub BindControls()
Try
txtQuestion.DataBindings.Add("Text", dsQuestion.Tables _
("QS"), "QText")
txtQuNum.DataBindings.Add("Text", dsQuestion.Tables _
("QS"), "Q#")
cboQuNum.DataSource = dsQuestion.Tables("QS")
cboQuNum.DisplayMember = "Q#"
Catch ex As Exception
MessageBox.Show(ex.Message, "Binding Controls")
End Try
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAdd.Click
Try
bmbQuestion.AddNew()
SetButtons(False)
btnSave.Enabled = False
blnNewRow = True
txtQuestion.Clear()
txtQuNum.Clear()
txtQuNum.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Adding Record")
End Try
End Sub

Private Sub cboQuNum_SelectedIndexChanged(ByVal sender As
System.Object, _
ByVal e As System.EventArgs) Handles
cboQuNum.SelectedIndexChanged
If Not blnLoading Then
bmbQuestion.Position = cboQuNum.SelectedIndex
SetButtons(True)
btnSave.Enabled = dsQuestion.HasChanges
txtQuNum.Focus()
End If
End Sub

[Cor Ligthert]

Hi Greg,

I cannot really get behind your code, one of the things is that i think that
you have a routine which create a new row.

Private Sub btnAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAdd.Click
Try
bmbQuestion.AddNew()

Adding this here
\\\
BindingContext(DataSetName, "TableName").Position =
dataset.tables(0).rows.count -1
///
And than you can see what that does for you?

Cor

[Cor Ligthert]

Hi Greg,

I cannot really get behind your code, one of the things is that i think that
you have a routine which create a new row.

Private Sub btnAdd_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnAdd.Click
Try
bmbQuestion.AddNew()

Adding this here
\\\
BindingContext(DataSetName, "TableName").Position =
dataset.tables(0).rows.count -1
///
And than you can see what that does for you?

Cor

[Greg]

Thanks, I'll try that. I didn't post all the code because most of it
seemed to work ok. Like I said, with a couple exceptions, this is the
same as the code (that works) in a book. The differences are I'm using
Access, not msde, and calling the subs after a combobox (not the one
posted) selection instead of form load.
I can't use wizards or set data binding in the Properties window
because the user can select any of @ 60 different db's. Each client
has their own db, in a separate directory, etc (I didn't design this
setup).
Maybe because of this I also get problems such as "Syntax error
(missing operator) in query expression..." when trying to update the
db after a delete command, for example. The commandbuilder doesn't
seem to be picking up the bound parameters.
I'll try your suggestion Monday when I'm back at work. I can do a
cmd.ExecuteNonQuery each time a change is made, but I want to update
just the dataset, then commit all changes at once.

[Greg]

Thanks, I'll try that. I didn't post all the code because most of it
seemed to work ok. Like I said, with a couple exceptions, this is the
same as the code (that works) in a book. The differences are I'm using
Access, not msde, and calling the subs after a combobox (not the one
posted) selection instead of form load.
I can't use wizards or set data binding in the Properties window
because the user can select any of @ 60 different db's. Each client
has their own db, in a separate directory, etc (I didn't design this
setup).
Maybe because of this I also get problems such as "Syntax error
(missing operator) in query expression..." when trying to update the
db after a delete command, for example. The commandbuilder doesn't
seem to be picking up the bound parameters.
I'll try your suggestion Monday when I'm back at work. I can do a
cmd.ExecuteNonQuery each time a change is made, but I want to update
just the dataset, then commit all changes at once.

[Greg]

OK, that seemed to put the dataset position at the last existing
record, not at the new one, but at least it wasn't on the current
record. Taking out the -1 puts me at the new record.
Now I get a syntax error when I update the db. I was under the
impression the commandbuilder built the update query. Do I have to
build it manually, a/o manually supply the parameters? If it helps,
here's most of the code that's involved (the <x>module code is for a
different tab page):

Private Sub BindControls()
Try
txtQuestion.DataBindings.Add(New Binding("Text",
dsQuestion, "QS.QText"))
txtQuNum.DataBindings.Add(New Binding("Text", dsQuestion,
"QS.Q#"))
cboQuNum.DataSource = dsQuestion.Tables("QS")
cboQuNum.DisplayMember = "Q#"
dgModule.DataSource = dsModule
dgModule.DataMember = "[Mod List]"
Catch ex As Exception
MessageBox.Show(ex.Message, "Binding Controls")
End Try
End Sub

Private Sub GetData()
Try
CreateADOObjects()
FillTables() 'fill datsets
bmbQuestion = Me.BindingContext(dsQuestion, "QS")
bmbModule = Me.BindingContext(dsModule, "[Mod List]")
BindControls()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Getting Data")
End Try
End Sub

Private Sub CreateADOObjects()
cn.ConnectionString = mstrFullPath
Try
cmdQuestion.Connection = cn
Dim strQuestionSelect As String
strQuestionSelect = "SELECT * FROM QS"
cmdQuestion.CommandText = strQuestionSelect
daQuestion.SelectCommand = cmdQuestion

cmdModule.Connection = cn
Dim strModuleSelect As String
strModuleSelect = "SELECT * FROM [Mod List]"
cmdModule.CommandText = strModuleSelect
daModule.SelectCommand = cmdModule

'Shouldn't this build the update, insert & delete
commands?
cbQuestion = New OleDbCommandBuilder(daQuestion)
cbModule = New OleDbCommandBuilder(daModule)
Catch ex As Exception
MessageBox.Show(ex.ToString, "Creating Objects")
Me.Close()
End Try
End Sub

Private Sub UpdateDB()
Try
With daQuestion
.Update(dsQuestion, "QS")
dsQuestion.Clear()
.Fill(dsQuestion, "QS")
End With
Catch ex As Exception
MessageBox.Show(ex.ToString & vbCrLf, "Error Updating
Database")
End Try
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSave.Click
blnLoading = True
UpdateDB()
blnLoading = False
btnSave.Enabled = False
cboQuNum.Focus()
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click
If ValidateData() Then
bmbQuestion.EndCurrentEdit()
If blnNewRow Then
cboQuNum.SelectedIndex = bmbQuestion.Count - 1
blnNewRow = False
End If
SetButtons(True)
btnSave.Enabled = True
cboQuNum.Focus()
End If
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
Try
bmbQuestion.AddNew()
BindingContext(dsQuestion, "QS").Position =
dsQuestion.Tables("QS").Rows.Count

SetButtons(True)
btnSave.Enabled = True
blnNewRow = True
txtQuestion.Clear()
txtQuNum.Clear()
txtQuNum.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Adding Record")
End Try
End Sub

Private Sub cboQuNum_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cboQuNum.SelectedIndexChanged
If Not blnLoading Then
bmbQuestion.Position = cboQuNum.SelectedIndex
SetButtons(True)
btnSave.Enabled = dsQuestion.HasChanges
txtQuNum.Focus()
End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnDelete.Click
Dim ans As DialogResult

ans = MessageBox.Show("Delete question #: " & cboQuNum.Text &
"?", "Confirm Delete", MessageBoxButtons.YesNo,
MessageBoxIcon.Question)
If ans = DialogResult.Yes Then
dblOldNum = CDbl(txtQuNum.Text)
bmbQuestion.RemoveAt(bmbQuestion.Position)
cboQuNum.Focus()
Else
MessageBox.Show("Delete of question #: " & cboQuNum.Text &
" cancelled.", "Delete Cancelled")
End If
End Sub

[Greg]

OK, that seemed to put the dataset position at the last existing
record, not at the new one, but at least it wasn't on the current
record. Taking out the -1 puts me at the new record.
Now I get a syntax error when I update the db. I was under the
impression the commandbuilder built the update query. Do I have to
build it manually, a/o manually supply the parameters? If it helps,
here's most of the code that's involved (the <x>module code is for a
different tab page):

Private Sub BindControls()
Try
txtQuestion.DataBindings.Add(New Binding("Text",
dsQuestion, "QS.QText"))
txtQuNum.DataBindings.Add(New Binding("Text", dsQuestion,
"QS.Q#"))
cboQuNum.DataSource = dsQuestion.Tables("QS")
cboQuNum.DisplayMember = "Q#"
dgModule.DataSource = dsModule
dgModule.DataMember = "[Mod List]"
Catch ex As Exception
MessageBox.Show(ex.Message, "Binding Controls")
End Try
End Sub

Private Sub GetData()
Try
CreateADOObjects()
FillTables() 'fill datsets
bmbQuestion = Me.BindingContext(dsQuestion, "QS")
bmbModule = Me.BindingContext(dsModule, "[Mod List]")
BindControls()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Getting Data")
End Try
End Sub

Private Sub CreateADOObjects()
cn.ConnectionString = mstrFullPath
Try
cmdQuestion.Connection = cn
Dim strQuestionSelect As String
strQuestionSelect = "SELECT * FROM QS"
cmdQuestion.CommandText = strQuestionSelect
daQuestion.SelectCommand = cmdQuestion

cmdModule.Connection = cn
Dim strModuleSelect As String
strModuleSelect = "SELECT * FROM [Mod List]"
cmdModule.CommandText = strModuleSelect
daModule.SelectCommand = cmdModule

'Shouldn't this build the update, insert & delete
commands?
cbQuestion = New OleDbCommandBuilder(daQuestion)
cbModule = New OleDbCommandBuilder(daModule)
Catch ex As Exception
MessageBox.Show(ex.ToString, "Creating Objects")
Me.Close()
End Try
End Sub

Private Sub UpdateDB()
Try
With daQuestion
.Update(dsQuestion, "QS")
dsQuestion.Clear()
.Fill(dsQuestion, "QS")
End With
Catch ex As Exception
MessageBox.Show(ex.ToString & vbCrLf, "Error Updating
Database")
End Try
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnSave.Click
blnLoading = True
UpdateDB()
blnLoading = False
btnSave.Enabled = False
cboQuNum.Focus()
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnUpdate.Click
If ValidateData() Then
bmbQuestion.EndCurrentEdit()
If blnNewRow Then
cboQuNum.SelectedIndex = bmbQuestion.Count - 1
blnNewRow = False
End If
SetButtons(True)
btnSave.Enabled = True
cboQuNum.Focus()
End If
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
Try
bmbQuestion.AddNew()
BindingContext(dsQuestion, "QS").Position =
dsQuestion.Tables("QS").Rows.Count

SetButtons(True)
btnSave.Enabled = True
blnNewRow = True
txtQuestion.Clear()
txtQuNum.Clear()
txtQuNum.Focus()
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Adding Record")
End Try
End Sub

Private Sub cboQuNum_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cboQuNum.SelectedIndexChanged
If Not blnLoading Then
bmbQuestion.Position = cboQuNum.SelectedIndex
SetButtons(True)
btnSave.Enabled = dsQuestion.HasChanges
txtQuNum.Focus()
End If
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnDelete.Click
Dim ans As DialogResult

ans = MessageBox.Show("Delete question #: " & cboQuNum.Text &
"?", "Confirm Delete", MessageBoxButtons.YesNo,
MessageBoxIcon.Question)
If ans = DialogResult.Yes Then
dblOldNum = CDbl(txtQuNum.Text)
bmbQuestion.RemoveAt(bmbQuestion.Position)
cboQuNum.Focus()
Else
MessageBox.Show("Delete of question #: " & cboQuNum.Text &
" cancelled.", "Delete Cancelled")
End If
End Sub

[Cor Ligthert]

Hi Greg,

In another newsgroup there is someone with almost the same problem as you.
I do never use that bindingcontext.addnew

Instead for that I use
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)

where ds is the datasetname and 0 the table index , for the last may also be
used the tablename between "".

Can you try that instead, because while testing the problem, I got results I
could not understand too. Maybe I do tomorrow however not today.

And that position answer is wrong, I thought it was a function to a dataset
update you was using.
The position has a zero index so count is always one behind the last index.

I hope this helps anyway?

Cor

[Cor Ligthert]

Hi Greg,

In another newsgroup there is someone with almost the same problem as you.
I do never use that bindingcontext.addnew

Instead for that I use
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)

where ds is the datasetname and 0 the table index , for the last may also be
used the tablename between "".

Can you try that instead, because while testing the problem, I got results I
could not understand too. Maybe I do tomorrow however not today.

And that position answer is wrong, I thought it was a function to a dataset
update you was using.
The position has a zero index so count is always one behind the last index.

I hope this helps anyway?

Cor

[Greg]

It seems the problem is the field Q# in the QS table. Access won't
accept it back from the app unless it's surrounded by brackets. But
the app can't work with it that way internally because the field is
named Q#, not [Q#]. If I use the commandbuilder, I get an error
"Syntax error in Insert Into statement...".
If I build them manually, I get an error "No value given for one or
more required parameters" when I use brackets, or the same error as
above when I don't. Manual command is:
INSERT INTO QS(Q#, QText) VALUES (?, ?)
I did change it in a test db to QuNum, and that worked fine, but
changing the field name isn't really an option because it's used in
too many queries, forms, etc, in about 60 different db's.

[Greg]

Thans Cor, I'll try that. The dataset updates, inserts, deletes ok
now, it's updating the actual database that's a problem.