|
 |
access thread: How do I insert rows with Autonumber primary key via ADO?
Message #1 by "Jonathan Strong" <jon.strong@b...> on Fri, 25 Oct 2002 08:38:10 +0100
|
|
Hi
Synopsis:
I am endeavouring to insert records into an Access (Office2000) table with
the following structure using ADOdc.recordset.addnew method.
TblCriteria
CriteriaId Autonum (PrimaryKey)
CategoryId Number (FK on another Category table)
Criteria Text
Weighting Number
ContraIndicator Boolean (Yes/No)
CriteriaSuspended Boolean
I have tackled the problem in several ways and eventually got close to
getting the insert working correctly. However I still get errors when the
query is executed in either of the two approaches I am trying to use.
Case 1:
Ignore primary key in query: Successfully inserts a new record but generates
a longwinded error message on the ADODC object indicating that the
recordset.update affects too many rows. Assumption: ignoring the primary key
means that part of the update functionality is attempting to modify other
records in the recordset
Query: adCmdText in ADODC object
SELECT CategoryId, Criteria, CriteriaWeighting, ContraIndicator,
CriteriaSuspended
FROM TblCriteria
WHERE CategoryId = 3
CategoryId, Criteria, CriteriaWeighting, ContraIndicator, CriteriaSuspended
are all bound to appropriate form objects
Case 2:
Add CriteriaId to the query and a new bound text box on the form. This
generates a duplicate record error. Assumption: this is because the AddNew
method sets the AutoNum field to Null (Checked through Watch window) in the
recordset, thus generating an attempt to insert a key of 0 which already
exists in the table.
Query: adCmdText in ADODC object
SELECT CriteriaId, CategoryId, Criteria, CriteriaWeighting, ContraIndicator,
CriteriaSuspended
FROM TblCriteria
WHERE CategoryId = 3
In this case I tried this query with and without a bound text object for the
primary key CriteriaId. I use the Arrays of Fields and Values approach as
this way seemed to get me closest to a solution. The AddNew on its own was a
whole other story!
How can I get the insert to work cleanly?
Code structure for the Add Criterian Button functionality.
Private Sub But_AddCriterion_Click()
Dim sAddCriterionCategory As String
Dim iAddCriterionCategory As Integer
Dim vArrCriterionField(5) As Variant
Dim vArrCriterionValue(5) As Variant
sAddCriterionCategory = CategoryId.Text
iAddCriterionCategory = Int(sAddCriterionCategory)
Criteria.SetFocus
'Set up Array of recordset field names
vArrCriterionField(0) = "CriteriaId"
vArrCriterionField(1) = "CategoryId"
vArrCriterionField(2) = "Criteria"
vArrCriterionField(3) = "CriteriaWeighting"
vArrCriterionField(4) = "ContraIndicator"
vArrCriterionField(5) = "CriteriaSuspended"
'Set up Array of recordset field names
'vArrCriterionValue(0) = HiddenCriteriaId.Text
'Line above commented when no bound object on form
vArrCriterionValue(1) = iAddCriterionCategory
vArrCriterionValue(2) = Criteria.Text
vArrCriterionValue(3) = Weighting.Text
vArrCriterionValue(4) = ContraIndicator.Value
vArrCriterionValue(5) = Suspended.Value
'Set up new ADODC1 record
Adodc1.Recordset.AddNew vArrCriterionField, vArrCriterionValue
vArrCriterionValue(0) = HiddenCriteriaId.Text
'Clear form fields
Criteria.Text = ""
Weighting.Text = ""
ContraIndicator.Value = False
Suspended.Value = False
'Set up Button states
But_AddCriterion.Visible = False
But_Cancel.Visible = True
But_UpdCriterion.Visible = True
Adodc1.Visible = False
End Sub
Private Sub But_Cancel_Click()
Adodc1.Recordset.CancelUpdate
Adodc1.Refresh
'Reset Button States
But_AddCriterion.Visible = True
But_Cancel.Visible = False
But_UpdCriterion.Visible = False
Adodc1.Visible = True
End Sub
Private Sub But_UpdCriterion_Click()
Adodc1.Recordset.Update
Adodc1.Refresh
'Reset Button States
But_AddCriterion.Visible = True
But_Cancel.Visible = False
But_UpdCriterion.Visible = False
Adodc1.Visible = True
End Sub
I understood that the AddNew method automatically created the new record;
Why does it not appear to generate 1 and only 1 new primary key value in
Case 1, and; Why in case 2 does it generate a null/0 autonum key value?
This is my first VB,ADO,Access application. I have written Multidimensional
SQL generators and ROLAP engines against a distributed ORACLE (7) database
way back in the dim and distant past. I am feeling a touch frustrated at the
quality of the examples in the books I have. This should be the most basic,
pardon the pun, of operations after a retrieval of data. I thought ADO was
supposed to make database access easy!
Many thanks in advance for your assistance if you can help.
Jon Strong
email: jon.strong@b...
Message #2 by "Loveday, Jamie" <Jamie.Loveday@n...> on Fri, 25 Oct 2002 11:26:56 +0100
|
|
Its an AutoNumber why are you trying to insert it, when you add all the
other data and update the record the AutoNumber is (automatically, strangely
enough) entered.
Jamie
-----Original Message-----
From: Jonathan Strong [mailto:jon.strong@b...]
Sent: 25 October 2002 08:38
To: Access
Subject: [access] How do I insert rows with Autonumber primary key via
ADO?
Hi
Synopsis:
I am endeavouring to insert records into an Access (Office2000) table with
the following structure using ADOdc.recordset.addnew method.
TblCriteria
CriteriaId Autonum (PrimaryKey)
CategoryId Number (FK on another Category table)
Criteria Text
Weighting Number
ContraIndicator Boolean (Yes/No)
CriteriaSuspended Boolean
I have tackled the problem in several ways and eventually got close to
getting the insert working correctly. However I still get errors when the
query is executed in either of the two approaches I am trying to use.
Case 1:
Ignore primary key in query: Successfully inserts a new record but generates
a longwinded error message on the ADODC object indicating that the
recordset.update affects too many rows. Assumption: ignoring the primary key
means that part of the update functionality is attempting to modify other
records in the recordset
Query: adCmdText in ADODC object
SELECT CategoryId, Criteria, CriteriaWeighting, ContraIndicator,
CriteriaSuspended
FROM TblCriteria
WHERE CategoryId = 3
CategoryId, Criteria, CriteriaWeighting, ContraIndicator, CriteriaSuspended
are all bound to appropriate form objects
Case 2:
Add CriteriaId to the query and a new bound text box on the form. This
generates a duplicate record error. Assumption: this is because the AddNew
method sets the AutoNum field to Null (Checked through Watch window) in the
recordset, thus generating an attempt to insert a key of 0 which already
exists in the table.
Query: adCmdText in ADODC object
SELECT CriteriaId, CategoryId, Criteria, CriteriaWeighting, ContraIndicator,
CriteriaSuspended
FROM TblCriteria
WHERE CategoryId = 3
In this case I tried this query with and without a bound text object for the
primary key CriteriaId. I use the Arrays of Fields and Values approach as
this way seemed to get me closest to a solution. The AddNew on its own was a
whole other story!
How can I get the insert to work cleanly?
Code structure for the Add Criterian Button functionality.
Private Sub But_AddCriterion_Click()
Dim sAddCriterionCategory As String
Dim iAddCriterionCategory As Integer
Dim vArrCriterionField(5) As Variant
Dim vArrCriterionValue(5) As Variant
sAddCriterionCategory = CategoryId.Text
iAddCriterionCategory = Int(sAddCriterionCategory)
Criteria.SetFocus
'Set up Array of recordset field names
vArrCriterionField(0) = "CriteriaId"
vArrCriterionField(1) = "CategoryId"
vArrCriterionField(2) = "Criteria"
vArrCriterionField(3) = "CriteriaWeighting"
vArrCriterionField(4) = "ContraIndicator"
vArrCriterionField(5) = "CriteriaSuspended"
'Set up Array of recordset field names
'vArrCriterionValue(0) = HiddenCriteriaId.Text
'Line above commented when no bound object on form
vArrCriterionValue(1) = iAddCriterionCategory
vArrCriterionValue(2) = Criteria.Text
vArrCriterionValue(3) = Weighting.Text
vArrCriterionValue(4) = ContraIndicator.Value
vArrCriterionValue(5) = Suspended.Value
'Set up new ADODC1 record
Adodc1.Recordset.AddNew vArrCriterionField, vArrCriterionValue
vArrCriterionValue(0) = HiddenCriteriaId.Text
'Clear form fields
Criteria.Text = ""
Weighting.Text = ""
ContraIndicator.Value = False
Suspended.Value = False
'Set up Button states
But_AddCriterion.Visible = False
But_Cancel.Visible = True
But_UpdCriterion.Visible = True
Adodc1.Visible = False
End Sub
Private Sub But_Cancel_Click()
Adodc1.Recordset.CancelUpdate
Adodc1.Refresh
'Reset Button States
But_AddCriterion.Visible = True
But_Cancel.Visible = False
But_UpdCriterion.Visible = False
Adodc1.Visible = True
End Sub
Private Sub But_UpdCriterion_Click()
Adodc1.Recordset.Update
Adodc1.Refresh
'Reset Button States
But_AddCriterion.Visible = True
But_Cancel.Visible = False
But_UpdCriterion.Visible = False
Adodc1.Visible = True
End Sub
I understood that the AddNew method automatically created the new record;
Why does it not appear to generate 1 and only 1 new primary key value in
Case 1, and; Why in case 2 does it generate a null/0 autonum key value?
This is my first VB,ADO,Access application. I have written Multidimensional
SQL generators and ROLAP engines against a distributed ORACLE (7) database
way back in the dim and distant past. I am feeling a touch frustrated at the
quality of the examples in the books I have. This should be the most basic,
pardon the pun, of operations after a retrieval of data. I thought ADO was
supposed to make database access easy!
Many thanks in advance for your assistance if you can help.
Jon Strong
email: jon.strong@b...
The information in this message including any attachments may be
confidential or privileged and is for the use of the named recipient only.
If you are not the named or intended recipient you may not copy, distribute,
or deliver this message to anyone or take any action in reliance on it. If
you receive this message in error please contact the sender immediately and
delete it from your system
Message #3 by "Jon Strong" <Jon.strong@b...> on Fri, 25 Oct 2002 12:31:15
|
|
Apologies if the Problem description, two cases and code in my message did
not make it clear that I am not using a SQL INSERT statement to create the
new row in the table.
For clarification I am using the addNew method to automatically create the
new record. The method either defaults to creating a 0 key for the primary
if the key column is retrieved in the query, or, appears to try to apply
the edit to all the rows retrieved by the query if the key column is not
included in the select statement. I admit that if the key is not retrieved
then the new row is inserted correctly. What concerns me in this case is
the error message that is generated implying it is also attempting to
apply the contents of the form to all the other rows retrieved by the
query.
Kind regards
Jon
> Its an AutoNumber why are you trying to insert it, when you add all the
other data and update the record the AutoNumber is (automatically,
strangely
enough) entered.
Jamie
Message #4 by "Loveday, Jamie" <Jamie.Loveday@n...> on Fri, 25 Oct 2002 13:42:59 +0100
|
|
The reason you are getting the error message sounds more like something to
do with referential integrity in the database than your code.
Jamie
-----Original Message-----
From: Jon Strong [mailto:Jon.strong@b...]
Sent: 25 October 2002 13:31
To: Access
Subject: [access] RE: How do I insert rows with Autonumber primary key
via ADO?
Apologies if the Problem description, two cases and code in my message did
not make it clear that I am not using a SQL INSERT statement to create the
new row in the table.
For clarification I am using the addNew method to automatically create the
new record. The method either defaults to creating a 0 key for the primary
if the key column is retrieved in the query, or, appears to try to apply
the edit to all the rows retrieved by the query if the key column is not
included in the select statement. I admit that if the key is not retrieved
then the new row is inserted correctly. What concerns me in this case is
the error message that is generated implying it is also attempting to
apply the contents of the form to all the other rows retrieved by the
query.
Kind regards
Jon
> Its an AutoNumber why are you trying to insert it, when you add all the
other data and update the record the AutoNumber is (automatically,
strangely
enough) entered.
Jamie
The information in this message including any attachments may be
confidential or privileged and is for the use of the named recipient only.
If you are not the named or intended recipient you may not copy, distribute,
or deliver this message to anyone or take any action in reliance on it. If
you receive this message in error please contact the sender immediately and
delete it from your system
Message #5 by "Enzo Zaragoza" <enzaux@g...> on Sat, 26 Oct 2002 09:48:20 +0800
|
|
You dont need to insert Criteria ID for it is an autonumber.
Enzo
-----Original Message-----
From: Jonathan Strong [mailto:jon.strong@b...]
Sent: Friday, October 25, 2002 3:38 PM
To: Access
Subject: [access] How do I insert rows with Autonumber primary key via
ADO?
Hi
Synopsis:
I am endeavouring to insert records into an Access (Office2000) table with
the following structure using ADOdc.recordset.addnew method.
TblCriteria
CriteriaId Autonum (PrimaryKey)
CategoryId Number (FK on another Category table)
Criteria Text
Weighting Number
ContraIndicator Boolean (Yes/No)
CriteriaSuspended Boolean
I have tackled the problem in several ways and eventually got close to
getting the insert working correctly. However I still get errors when the
query is executed in either of the two approaches I am trying to use.
Case 1:
Ignore primary key in query: Successfully inserts a new record but generates
a longwinded error message on the ADODC object indicating that the
recordset.update affects too many rows. Assumption: ignoring the primary key
means that part of the update functionality is attempting to modify other
records in the recordset
Query: adCmdText in ADODC object
SELECT CategoryId, Criteria, CriteriaWeighting, ContraIndicator,
CriteriaSuspended
FROM TblCriteria
WHERE CategoryId = 3
CategoryId, Criteria, CriteriaWeighting, ContraIndicator, CriteriaSuspended
are all bound to appropriate form objects
Case 2:
Add CriteriaId to the query and a new bound text box on the form. This
generates a duplicate record error. Assumption: this is because the AddNew
method sets the AutoNum field to Null (Checked through Watch window) in the
recordset, thus generating an attempt to insert a key of 0 which already
exists in the table.
Query: adCmdText in ADODC object
SELECT CriteriaId, CategoryId, Criteria, CriteriaWeighting, ContraIndicator,
CriteriaSuspended
FROM TblCriteria
WHERE CategoryId = 3
In this case I tried this query with and without a bound text object for the
primary key CriteriaId. I use the Arrays of Fields and Values approach as
this way seemed to get me closest to a solution. The AddNew on its own was a
whole other story!
How can I get the insert to work cleanly?
Code structure for the Add Criterian Button functionality.
Private Sub But_AddCriterion_Click()
Dim sAddCriterionCategory As String
Dim iAddCriterionCategory As Integer
Dim vArrCriterionField(5) As Variant
Dim vArrCriterionValue(5) As Variant
sAddCriterionCategory = CategoryId.Text
iAddCriterionCategory = Int(sAddCriterionCategory)
Criteria.SetFocus
'Set up Array of recordset field names
vArrCriterionField(0) = "CriteriaId"
vArrCriterionField(1) = "CategoryId"
vArrCriterionField(2) = "Criteria"
vArrCriterionField(3) = "CriteriaWeighting"
vArrCriterionField(4) = "ContraIndicator"
vArrCriterionField(5) = "CriteriaSuspended"
'Set up Array of recordset field names
'vArrCriterionValue(0) = HiddenCriteriaId.Text
'Line above commented when no bound object on form
vArrCriterionValue(1) = iAddCriterionCategory
vArrCriterionValue(2) = Criteria.Text
vArrCriterionValue(3) = Weighting.Text
vArrCriterionValue(4) = ContraIndicator.Value
vArrCriterionValue(5) = Suspended.Value
'Set up new ADODC1 record
Adodc1.Recordset.AddNew vArrCriterionField, vArrCriterionValue
vArrCriterionValue(0) = HiddenCriteriaId.Text
'Clear form fields
Criteria.Text = ""
Weighting.Text = ""
ContraIndicator.Value = False
Suspended.Value = False
'Set up Button states
But_AddCriterion.Visible = False
But_Cancel.Visible = True
But_UpdCriterion.Visible = True
Adodc1.Visible = False
End Sub
Private Sub But_Cancel_Click()
Adodc1.Recordset.CancelUpdate
Adodc1.Refresh
'Reset Button States
But_AddCriterion.Visible = True
But_Cancel.Visible = False
But_UpdCriterion.Visible = False
Adodc1.Visible = True
End Sub
Private Sub But_UpdCriterion_Click()
Adodc1.Recordset.Update
Adodc1.Refresh
'Reset Button States
But_AddCriterion.Visible = True
But_Cancel.Visible = False
But_UpdCriterion.Visible = False
Adodc1.Visible = True
End Sub
I understood that the AddNew method automatically created the new record;
Why does it not appear to generate 1 and only 1 new primary key value in
Case 1, and; Why in case 2 does it generate a null/0 autonum key value?
This is my first VB,ADO,Access application. I have written Multidimensional
SQL generators and ROLAP engines against a distributed ORACLE (7) database
way back in the dim and distant past. I am feeling a touch frustrated at the
quality of the examples in the books I have. This should be the most basic,
pardon the pun, of operations after a retrieval of data. I thought ADO was
supposed to make database access easy!
Many thanks in advance for your assistance if you can help.
Jon Strong
email: jon.strong@b...
Message #6 by "Bob Bedell" <bobbedell15@m...> on Sat, 26 Oct 2002 05:22:14 +0000
|
|
WARNING: THIS IS VB STUFF (though we are connecting to Access)
Jon,
You're working at cross-purposes. You need to EITHER drop an Adodc
data control on your form OR you need to use ADO to create your own
connection and recordset to your database. You're trying to do both.
Adodc1 in your code is (I assume) the name of your Adodc ActiveX
control. In the properties window, set its connection string to the
OLE DB Jet 4.0 provider and the name of your database, the command type
to adCmdTable, and the record source to the name of your table. Your
Adodc control is now configured.
Place text boxes on your form for each of the fields in your database.
You do not need a text box control for your Autonumber/Primary Key
field. Executing the Update method of your Adodc controls recordset
will insert the key for you. Bind the fields directly to your Adodc
control. You don't need to do this with arrays in your code.
Thats the point of using the ActiveX control; it handles all the
under-the-hood stuff for you. Set each text boxes Data Source property
to Adodc1, and its Data Field property to a field in your database.
Your Adodc control is now connected to your database; your text box
controls are now bound to your Adodc control.
Place two control buttons on your form. Create a control array by
naming both controls 'Control' and setting their index properties to
0 and 1, respectively. The controls names are now Control(0) and
Control (1). Control(0) is your Add button and Control(1) is your
Update button. Now paste the folloowing code behind your form:
'~~~~~~~~~~Start~~~~~~~~~~
Private Sub Command_Click(Index As Integer)
With Adodc1
Select Case Index
Case 0
.Recordset.AddNew
Case 1
.Recordset.Update
End Select
End With
End Sub
'~~~~~~~~~~Stop~~~~~~~~~~
That is literally all you need to add a record to your database.
Adodc1.Recordset.Add creates a new record for your updateable recordset
object and clears your text boxes so you can add new values for the
new record. Adodc1.Recordset.Update commits the values you typed in the
text boxes.
The Adodc control does all the connecting and binding and committing
work for you. You don't need the arrays.
Best,
Bob
>From: "Jon Strong" <Jon.strong@b...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: How do I insert rows with Autonumber primary key via
> ADO?
>Date: Fri, 25 Oct 2002 12:31:15
>
>Apologies if the Problem description, two cases and code in my message did
>not make it clear that I am not using a SQL INSERT statement to create the
>new row in the table.
>
>For clarification I am using the addNew method to automatically create the
>new record. The method either defaults to creating a 0 key for the primary
>if the key column is retrieved in the query, or, appears to try to apply
>the edit to all the rows retrieved by the query if the key column is not
>included in the select statement. I admit that if the key is not retrieved
>then the new row is inserted correctly. What concerns me in this case is
>the error message that is generated implying it is also attempting to
>apply the contents of the form to all the other rows retrieved by the
>query.
>
>Kind regards
>
>Jon
>
> > Its an AutoNumber why are you trying to insert it, when you add all the
>other data and update the record the AutoNumber is (automatically,
>strangely
>enough) entered.
>
>Jamie
_________________________________________________________________
Broadband? Dial-up? Get reliable MSN Internet Access.
http://resourcecenter.msn.com/access/plans/default.asp
Message #7 by "Jon Strong" <jon.strong@b...> on Sat, 26 Oct 2002 10:17:27
|
|
> WARNING: THIS IS VB STUFF (though we are connecting to Access)
Jon,
You're working at cross-purposes. You need to EITHER drop an Adodc
data control on your form OR you need to use ADO to create your own
connection and recordset to your database. You're trying to do both.
Adodc1 in your code is (I assume) the name of your Adodc ActiveX
control. In the properties window, set its connection string to the
OLE DB Jet 4.0 provider and the name of your database, the command type
to adCmdTable, and the record source to the name of your table. Your
Adodc control is now configured.
Place text boxes on your form for each of the fields in your database.
You do not need a text box control for your Autonumber/Primary Key
field. Executing the Update method of your Adodc controls recordset
will insert the key for you. Bind the fields directly to your Adodc
control. You don't need to do this with arrays in your code.
Thats the point of using the ActiveX control; it handles all the
under-the-hood stuff for you. Set each text boxes Data Source property
to Adodc1, and its Data Field property to a field in your database.
Your Adodc control is now connected to your database; your text box
controls are now bound to your Adodc control.
Place two control buttons on your form. Create a control array by
naming both controls 'Control' and setting their index properties to
0 and 1, respectively. The controls names are now Control(0) and
Control (1). Control(0) is your Add button and Control(1) is your
Update button. Now paste the folloowing code behind your form:
'~~~~~~~~~~Start~~~~~~~~~~
Private Sub Command_Click(Index As Integer)
With Adodc1
Select Case Index
Case 0
.Recordset.AddNew
Case 1
.Recordset.Update
End Select
End With
End Sub
'~~~~~~~~~~Stop~~~~~~~~~~
That is literally all you need to add a record to your database.
Adodc1.Recordset.Add creates a new record for your updateable recordset
object and clears your text boxes so you can add new values for the
new record. Adodc1.Recordset.Update commits the values you typed in the
text boxes.
The Adodc control does all the connecting and binding and committing
work for you. You don't need the arrays.
Best,
Bob
>From: "Jon Strong" <Jon.strong@b...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: How do I insert rows with Autonumber primary key
via
> ADO?
>Date: Fri, 25 Oct 2002 12:31:15
>
>Apologies if the Problem description, two cases and code in my message did
>not make it clear that I am not using a SQL INSERT statement to create the
>new row in the table.
>
>For clarification I am using the addNew method to automatically create the
>new record. The method either defaults to creating a 0 key for the primary
>if the key column is retrieved in the query, or, appears to try to apply
>the edit to all the rows retrieved by the query if the key column is not
>included in the select statement. I admit that if the key is not retrieved
>then the new row is inserted correctly. What concerns me in this case is
>the error message that is generated implying it is also attempting to
>apply the contents of the form to all the other rows retrieved by the
>query.
>
>Kind regards
>
>Jon
>
> > Its an AutoNumber why are you trying to insert it, when you add all the
>other data and update the record the AutoNumber is (automatically,
>strangely
>enough) entered.
>
>Jamie
_________________________________________________________________
Broadband? Dial-up? Get reliable MSN Internet Access.
http://resourcecenter.msn.com/access/plans/default.asp
Message #8 by "Jon Strong" <jon.strong@b...> on Sat, 26 Oct 2002 10:40:07
|
|
Bob
Thanks for taking the time to read the problem as outlined and reply. I
agree I may be working at cross purposes.
I started out with something similar to your approach. However I
recognised the need to constrain the query for the user, hence the
CategoryId constraint and the use of the adCmdText setting with a SQL
Query that I can edit.
I will give your suggestions a go. I was wondering if the constraint on
the query was causing the problem in that the recordset is not the full
table and therefore the end of the recordset retrieved is potentially in
the middle of the table when ADO tries the insert.
The criteria table is potentially too big to be easily updateable by the
user group I have in mind. They must be able to see only those records
related to the category selected on a previous form. My first attempt was
to create 2 linked ADODC objects one bound to a combo box selecting
category and the second ADODC control working off of the first. This
approach was outlined in the MS Manuals. This didn't work in practice so I
have been breaking the problem down into smaller and smaller chunks to get
it to work.
Best
Jon
> WARNING: THIS IS VB STUFF (though we are connecting to Access)
Jon,
You're working at cross-purposes. You need to EITHER drop an Adodc
data control on your form OR you need to use ADO to create your own
connection and recordset to your database. You're trying to do both.
Adodc1 in your code is (I assume) the name of your Adodc ActiveX
control. In the properties window, set its connection string to the
OLE DB Jet 4.0 provider and the name of your database, the command type
to adCmdTable, and the record source to the name of your table. Your
Adodc control is now configured.
Place text boxes on your form for each of the fields in your database.
You do not need a text box control for your Autonumber/Primary Key
field. Executing the Update method of your Adodc controls recordset
will insert the key for you. Bind the fields directly to your Adodc
control. You don't need to do this with arrays in your code.
Thats the point of using the ActiveX control; it handles all the
under-the-hood stuff for you. Set each text boxes Data Source property
to Adodc1, and its Data Field property to a field in your database.
Your Adodc control is now connected to your database; your text box
controls are now bound to your Adodc control.
Place two control buttons on your form. Create a control array by
naming both controls 'Control' and setting their index properties to
0 and 1, respectively. The controls names are now Control(0) and
Control (1). Control(0) is your Add button and Control(1) is your
Update button. Now paste the folloowing code behind your form:
'~~~~~~~~~~Start~~~~~~~~~~
Private Sub Command_Click(Index As Integer)
With Adodc1
Select Case Index
Case 0
.Recordset.AddNew
Case 1
.Recordset.Update
End Select
End With
End Sub
'~~~~~~~~~~Stop~~~~~~~~~~
That is literally all you need to add a record to your database.
Adodc1.Recordset.Add creates a new record for your updateable recordset
object and clears your text boxes so you can add new values for the
new record. Adodc1.Recordset.Update commits the values you typed in the
text boxes.
The Adodc control does all the connecting and binding and committing
work for you. You don't need the arrays.
Best,
Bob
>From: "Jon Strong" <Jon.strong@b...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: How do I insert rows with Autonumber primary key
via
> ADO?
>Date: Fri, 25 Oct 2002 12:31:15
>
>Apologies if the Problem description, two cases and code in my message did
>not make it clear that I am not using a SQL INSERT statement to create the
>new row in the table.
>
>For clarification I am using the addNew method to automatically create the
>new record. The method either defaults to creating a 0 key for the primary
>if the key column is retrieved in the query, or, appears to try to apply
>the edit to all the rows retrieved by the query if the key column is not
>included in the select statement. I admit that if the key is not retrieved
>then the new row is inserted correctly. What concerns me in this case is
>the error message that is generated implying it is also attempting to
>apply the contents of the form to all the other rows retrieved by the
>query.
>
>Kind regards
>
>Jon
>
> > Its an AutoNumber why are you trying to insert it, when you add all the
>other data and update the record the AutoNumber is (automatically,
>strangely
>enough) entered.
>
>Jamie
_________________________________________________________________
Broadband? Dial-up? Get reliable MSN Internet Access.
http://resourcecenter.msn.com/access/plans/default.asp
Message #9 by "Bob Bedell" <bobbedell15@m...> on Sat, 26 Oct 2002 15:59:56 +0000
|
|
Hi Jon,
A couple of final thoughts, and then we should probably move this
discussion off the Access list. It's really VB user interface stuff.
E-mail me if you like.
You can use a SELECT query with a WHERE clause to return a single
record to an Adodc control. But then your user only gets to view one
record in your database. You can then click the add button and add a
new record, but your user will never get to see it, because the Adodc
control serves as the forms record source, and its only returning one
specific record.
To get a feel for how VB handles Select, Add, and Update operations using
the Adodc control, kick out a few data entry forms using the VB 6 Data Form
Wizard which gives you the option of adding an Adodc. Go to
Add-ins > Add-In Manager. Select VB 6 Data Form Wizard and check
Loaded/Unloaded in the load behavior frame. Click OK. Click on the
Add-Ins menu again and you will see the loaded Data Form Wizard. Play
with the Master/Detail type (similar to Access's MainForm/SubForm) to
place constraints on the records returned to the user. The code behind
the Wizard generated forms is exposed so you can play with it.
Best,
Bob
>From: "Jon Strong" <jon.strong@b...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: How do I insert rows with Autonumber primary key via
>ADO?
>Date: Sat, 26 Oct 2002 10:40:07
>
>Bob
>
>Thanks for taking the time to read the problem as outlined and reply. I
>agree I may be working at cross purposes.
>
>I started out with something similar to your approach. However I
>recognised the need to constrain the query for the user, hence the
>CategoryId constraint and the use of the adCmdText setting with a SQL
>Query that I can edit.
>
>I will give your suggestions a go. I was wondering if the constraint on
>the query was causing the problem in that the recordset is not the full
>table and therefore the end of the recordset retrieved is potentially in
>the middle of the table when ADO tries the insert.
>
>The criteria table is potentially too big to be easily updateable by the
>user group I have in mind. They must be able to see only those records
>related to the category selected on a previous form. My first attempt was
>to create 2 linked ADODC objects one bound to a combo box selecting
>category and the second ADODC control working off of the first. This
>approach was outlined in the MS Manuals. This didn't work in practice so I
>have been breaking the problem down into smaller and smaller chunks to get
>it to work.
>
>Best
>
>Jon
>
> > WARNING: THIS IS VB STUFF (though we are connecting to Access)
>
>Jon,
>
>You're working at cross-purposes. You need to EITHER drop an Adodc
>data control on your form OR you need to use ADO to create your own
>connection and recordset to your database. You're trying to do both.
>
>Adodc1 in your code is (I assume) the name of your Adodc ActiveX
>control. In the properties window, set its connection string to the
>OLE DB Jet 4.0 provider and the name of your database, the command type
>to adCmdTable, and the record source to the name of your table. Your
>Adodc control is now configured.
>
>Place text boxes on your form for each of the fields in your database.
>You do not need a text box control for your Autonumber/Primary Key
>field. Executing the Update method of your Adodc controls recordset
>will insert the key for you. Bind the fields directly to your Adodc
>control. You don't need to do this with arrays in your code.
>Thats the point of using the ActiveX control; it handles all the
>under-the-hood stuff for you. Set each text boxes Data Source property
>to Adodc1, and its Data Field property to a field in your database.
>
>Your Adodc control is now connected to your database; your text box
>controls are now bound to your Adodc control.
>
>Place two control buttons on your form. Create a control array by
>naming both controls 'Control' and setting their index properties to
>0 and 1, respectively. The controls names are now Control(0) and
>Control (1). Control(0) is your Add button and Control(1) is your
>Update button. Now paste the folloowing code behind your form:
>
>'~~~~~~~~~~Start~~~~~~~~~~
>
>Private Sub Command_Click(Index As Integer)
> With Adodc1
> Select Case Index
> Case 0
> .Recordset.AddNew
> Case 1
> .Recordset.Update
> End Select
> End With
>End Sub
>
>'~~~~~~~~~~Stop~~~~~~~~~~
>
>That is literally all you need to add a record to your database.
>
>Adodc1.Recordset.Add creates a new record for your updateable recordset
>object and clears your text boxes so you can add new values for the
>new record. Adodc1.Recordset.Update commits the values you typed in the
>text boxes.
>
>The Adodc control does all the connecting and binding and committing
>work for you. You don't need the arrays.
>
>Best,
>
>Bob
>
> >From: "Jon Strong" <Jon.strong@b...>
> >Reply-To: "Access" <access@p...>
> >To: "Access" <access@p...>
> >Subject: [access] RE: How do I insert rows with Autonumber primary key
>via
> > ADO?
> >Date: Fri, 25 Oct 2002 12:31:15
> >
> >Apologies if the Problem description, two cases and code in my message
>did
> >not make it clear that I am not using a SQL INSERT statement to create
>the
> >new row in the table.
> >
> >For clarification I am using the addNew method to automatically create
>the
> >new record. The method either defaults to creating a 0 key for the
>primary
> >if the key column is retrieved in the query, or, appears to try to apply
> >the edit to all the rows retrieved by the query if the key column is not
> >included in the select statement. I admit that if the key is not
>retrieved
> >then the new row is inserted correctly. What concerns me in this case is
> >the error message that is generated implying it is also attempting to
> >apply the contents of the form to all the other rows retrieved by the
> >query.
> >
> >Kind regards
> >
> >Jon
> >
> > > Its an AutoNumber why are you trying to insert it, when you add all
>the
> >other data and update the record the AutoNumber is (automatically,
> >strangely
> >enough) entered.
> >
> >Jamie
>
>
>_________________________________________________________________
>Broadband? Dial-up? Get reliable MSN Internet Access.
>http://resourcecenter.msn.com/access/plans/default.asp
>
_________________________________________________________________
Get a speedy connection with MSN Broadband. Join now!
http://resourcecenter.msn.com/access/plans/freeactivation.asp
Message #10 by "Jon Strong" <jon.strong@b...> on Sat, 26 Oct 2002 20:06:23
|
|
Hi Bob
I'm happy to move this discussion off the Access list. I recently joined
the P2P forum and I guess I just picked the wrong list to subscribe to. I
saw it as a Database issue rather than a VB issue, I'm happy to stand
correction.
Thanks for your offer of direct discussion. If you would be good enough to
email me then we can establish contact. At the moment I can't see your
full email address.
Best,
Jon
|
|
|
View Cart
