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 bindingmanagerb ase 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.Dat aBindings.Add(" Text", dsQuestion.Tabl es _
("QS"), "QText")
txtQuNum.DataBi ndings.Add("Tex t", dsQuestion.Tabl es _
("QS"), "Q#")
cboQuNum.DataSo urce = dsQuestion.Tabl es("QS")
cboQuNum.Displa yMember = "Q#"
Catch ex As Exception
MessageBox.Show (ex.Message, "Binding Controls")
End Try
End Sub

Private Sub btnAdd_Click(By Val sender As System.Object, _
ByVal e As System.EventArg s) Handles btnAdd.Click
Try
bmbQuestion.Add New()
SetButtons(Fals e)
btnSave.Enabled = False
blnNewRow = True
txtQuestion.Cle ar()
txtQuNum.Clear( )
txtQuNum.Focus( )
Catch ex As Exception
MessageBox.Show (ex.Message, "Error Adding Record")
End Try
End Sub

Private Sub cboQuNum_Select edIndexChanged( ByVal sender As
System.Object, _
ByVal e As System.EventArg s) Handles
cboQuNum.Select edIndexChanged
If Not blnLoading Then
bmbQuestion.Pos ition = cboQuNum.Select edIndex
SetButtons(True )
btnSave.Enabled = dsQuestion.HasC hanges
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(By Val sender As System.Object, _
ByVal e As System.EventArg s) Handles btnAdd.Click
Try
bmbQuestion.Add New()

Adding this here
\\\
BindingContext( DataSetName, "TableName").Po sition =
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(By Val sender As System.Object, _
ByVal e As System.EventArg s) Handles btnAdd.Click
Try
bmbQuestion.Add New()

Adding this here
\\\
BindingContext( DataSetName, "TableName").Po sition =
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.ExecuteNonQ uery 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.ExecuteNonQ uery 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.Dat aBindings.Add(N ew Binding("Text",
dsQuestion, "QS.QText") )
txtQuNum.DataBi ndings.Add(New Binding("Text", dsQuestion,
"QS.Q#"))
cboQuNum.DataSo urce = dsQuestion.Tabl es("QS")
cboQuNum.Displa yMember = "Q#"
dgModule.DataSo urce = dsModule
dgModule.DataMe mber = "[Mod List]"
Catch ex As Exception
MessageBox.Show (ex.Message, "Binding Controls")
End Try
End Sub

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

Private Sub CreateADOObject s()
cn.ConnectionSt ring = mstrFullPath
Try
cmdQuestion.Con nection = cn
Dim strQuestionSele ct As String
strQuestionSele ct = "SELECT * FROM QS"
cmdQuestion.Com mandText = strQuestionSele ct
daQuestion.Sele ctCommand = cmdQuestion

cmdModule.Conne ction = cn
Dim strModuleSelect As String
strModuleSelect = "SELECT * FROM [Mod List]"
cmdModule.Comma ndText = strModuleSelect
daModule.Select Command = cmdModule

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

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

Private Sub btnSave_Click(B yVal sender As System.Object, ByVal e
As System.EventArg s) 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.EventArg s) Handles btnUpdate.Click
If ValidateData() Then
bmbQuestion.End CurrentEdit()
If blnNewRow Then
cboQuNum.Select edIndex = bmbQuestion.Cou nt - 1
blnNewRow = False
End If
SetButtons(True )
btnSave.Enabled = True
cboQuNum.Focus( )
End If
End Sub

Private Sub btnAdd_Click(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnAdd.Click
Try
bmbQuestion.Add New()
BindingContext( dsQuestion, "QS").Posit ion =
dsQuestion.Tabl es("QS").Rows.C ount

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

Private Sub cboQuNum_Select edIndexChanged( ByVal sender As
System.Object, ByVal e As System.EventArg s) Handles
cboQuNum.Select edIndexChanged
If Not blnLoading Then
bmbQuestion.Pos ition = cboQuNum.Select edIndex
SetButtons(True )
btnSave.Enabled = dsQuestion.HasC hanges
txtQuNum.Focus( )
End If
End Sub

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

ans = MessageBox.Show ("Delete question #: " & cboQuNum.Text &
"?", "Confirm Delete", MessageBoxButto ns.YesNo,
MessageBoxIcon. Question)
If ans = DialogResult.Ye s Then
dblOldNum = CDbl(txtQuNum.T ext)
bmbQuestion.Rem oveAt(bmbQuesti on.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.Dat aBindings.Add(N ew Binding("Text",
dsQuestion, "QS.QText") )
txtQuNum.DataBi ndings.Add(New Binding("Text", dsQuestion,
"QS.Q#"))
cboQuNum.DataSo urce = dsQuestion.Tabl es("QS")
cboQuNum.Displa yMember = "Q#"
dgModule.DataSo urce = dsModule
dgModule.DataMe mber = "[Mod List]"
Catch ex As Exception
MessageBox.Show (ex.Message, "Binding Controls")
End Try
End Sub

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

Private Sub CreateADOObject s()
cn.ConnectionSt ring = mstrFullPath
Try
cmdQuestion.Con nection = cn
Dim strQuestionSele ct As String
strQuestionSele ct = "SELECT * FROM QS"
cmdQuestion.Com mandText = strQuestionSele ct
daQuestion.Sele ctCommand = cmdQuestion

cmdModule.Conne ction = cn
Dim strModuleSelect As String
strModuleSelect = "SELECT * FROM [Mod List]"
cmdModule.Comma ndText = strModuleSelect
daModule.Select Command = cmdModule

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

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

Private Sub btnSave_Click(B yVal sender As System.Object, ByVal e
As System.EventArg s) 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.EventArg s) Handles btnUpdate.Click
If ValidateData() Then
bmbQuestion.End CurrentEdit()
If blnNewRow Then
cboQuNum.Select edIndex = bmbQuestion.Cou nt - 1
blnNewRow = False
End If
SetButtons(True )
btnSave.Enabled = True
cboQuNum.Focus( )
End If
End Sub

Private Sub btnAdd_Click(By Val sender As System.Object, ByVal e As
System.EventArg s) Handles btnAdd.Click
Try
bmbQuestion.Add New()
BindingContext( dsQuestion, "QS").Posit ion =
dsQuestion.Tabl es("QS").Rows.C ount

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

Private Sub cboQuNum_Select edIndexChanged( ByVal sender As
System.Object, ByVal e As System.EventArg s) Handles
cboQuNum.Select edIndexChanged
If Not blnLoading Then
bmbQuestion.Pos ition = cboQuNum.Select edIndex
SetButtons(True )
btnSave.Enabled = dsQuestion.HasC hanges
txtQuNum.Focus( )
End If
End Sub

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

ans = MessageBox.Show ("Delete question #: " & cboQuNum.Text &
"?", "Confirm Delete", MessageBoxButto ns.YesNo,
MessageBoxIcon. Question)
If ans = DialogResult.Ye s Then
dblOldNum = CDbl(txtQuNum.T ext)
bmbQuestion.Rem oveAt(bmbQuesti on.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).Ro ws.Add(ds.Table s(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).Ro ws.Add(ds.Table s(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.