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 10 1905
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
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
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.
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.
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
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
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
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Aaron Ackerman |
last post by:
I have a bound combobox the appears on a cell within the column of my bound
grid when the user clicks on a cell n(In my vb.net WinForm app).
I am trying to allow the adding of an item to that bound combo by allowing
the user to dynamically type in the new value directly in the combo box
whcih in turn updates the lookup table and then when they have finished
leaves that particular cell on the grid with the correct value.
Seems...
|
by: HJ |
last post by:
Hi all,
Consider an Access 2002 run-time application on Windows XP. The controls on
a form are locked until the user clicks an Edit button. The form is based on
a query and all controls are bound.
|
by: William Wisnieski |
last post by:
Hello Everyone,
Access 2000,
I have a main unbound form with a bound datasheet subform
. The subform is bound to a query
that returns records based on criteria in the main form. The user then
double clicks a row on the datasheet subform to open yet another form
bound to a table . These two forms are linked by
the field. So far so good.
|
by: Patrick |
last post by:
I'm working on a contact management application, and need a hand with one
aspect...
Here's what I want to create:
------------------------------------
A form split into two parts. There is a datagrid on the left side that lists
names and perhaps a couple of other key fields. The user can click on a
record in the datagrid, which should automatically pull up details on that
record in the various text boxes and other controls on the right...
|
by: Aaron Ackerman |
last post by:
I cannot a row to this bound DataGrid to SAVE MY LIFE! I have tried
everything and I am at a loss. The using goes into add mode with the add
button adds his data then updates with the update button, seems simple.
I am using ALL visual controls (supposedly to simplify things. If I was not
using the visual controls and calling an ExecuteNonQuery no prob.
Please look at my code and tell me what I am doing wrong. Also, what are the
advatages...
| |
by: Jim Heavey |
last post by:
Trying to figure out the technique which should be used
to add rows to a datagrid. I am thinking that I would
want an "Add" button on the footer, but I am not quite
sure how to do that. Is that the best method? Do you
have a sample of how to do this?
|
by: Greg |
last post by:
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...
|
by: foothills bhc |
last post by:
I have a problem with verifying content of controls on a form before closing the form or moving to the next form "record" (i.e., when moving to the next row of my form's record source).
HERE'S THE LONG EXPLANATION OF THE PROBLEM
First, A Description of the Form:
I have developed a form for entering survey questionnaire data. The data to be entred on the form could be considered as two kinds of information.
One kind could be called...
|
by: ApexData |
last post by:
I have a Single form with its RecordSource set to one table.
This form will have only one record that is used for the application
setup.
I have UnBound TextBoxes on the form. The textboxes will get filled,
and I want to have this data saved to the single record of the Table
using the OnClosed event.
The problem is that I get the error "You can't assign a value to this
object". The code below works fine when placed under a button. I
|
by: geolemon |
last post by:
I have a form/subform used for inventory management.
It is a bound form.
The error:
I don't understand what this means.
How the form is intended to work:
When you select a part number in the main form, the subform displays potentially several instances of that part number in inventory, and is designed to allow the inventory manager to adjust the quantities for each instance of the part in inventory.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |