Insert Data into 2 Columns

I have a database with 1 table. The table contains 2 columns.

I have run the DataAssist 2.07 wizard, and had a couple of issues.

The search works, but when I click the Insert button, there is only 1 field to fill in, even though there are 2 labels.

For the insert the one that you only see a label for is probably the auto incrementing primary key for the table. This is as it should be, if the id column in the table is an auto incrementing primary key then you will not want to insert a value for the id but let the database take care of that for you. If you would like to remove the label you can do so from the design view. You may also have this listed on your update page, if that is the case then you should make sure that you are not setting a value for it there either.

None of the columns have a primary key, therefore how can I get the field to appear.

If you would like to set this value yourself you can add in a text field next to the label for it, then you need to edit the DataAssist Insert or update server behavior, on the screen that has the db column select the id then using the lightning bolt icon for the value select the newly added text field. Then whatever value you have in this field should be inserted for that record. If you have any further trouble with this post back with the pages in question.

The insert is working now but if I change a Dr Code, it changes all the Dr Codes. I had a Clinic Code of IDFC and 5 different Dr Codes. When I changed the Dr Code to IDFC1A it changed all the Dr Codes to the same that were connected to Clinic Code IDFC.

I have attached the pages.

It looks like you have the index field for the update set to the ClinicCode column. For the value you are using Request.Form("WADAUpdateRecordID"). This form element is set to WADAdboHICOMClinicsAndDoctors.Fields.Item("ClinicCode").Value which should be the ClinicCode that comes from the recordset. You are filtering the recordset on this value being either -1 or Request.QueryString("ClinicCode") if it is present.

So long as you get to this page and filter the recordset on this value, and this value is unique in the db it should only update this individual record. The only way that I can see it updating more than one record would be if more than one record had the same ClinicCode before you got to this update page.

The best way to avoid this type of thing with the update it to reference the unique primary key for the table, this would ensure that the value was unique and that only a single record would be updated. I would suggest that you set this field to be the primary key if it is to be unique for each record, or add an index that is a primary key that will be unique for each record. If you add an index you will want to update your recordset on this page to get the id and then update the update server behavior to set this id column as the index field for the update.

If you can post back with an export of the db with just this table in it in a MS Access file I can take a look and do some further testing for you.

I have attached a .mdb file as requested.

Your data is like I suspected. Since you are updating this table based on the ClinicCode you will update all of the records in this table where the clinic code matches. The ClinicCode is not unique to an individual record so any updates based on this will update all the where there is a matching clinic code.

To get past this you will need to have a unique identifier for the table you are trying to update. The best way to do this is to add an id column and make an an auto incrementing primary key for the table. Then you would use this id reference as the unique identifier when you are updating a record. Only one record should match for this id.

If I do this will I still be able to have duplicates in each column.

Having an id column will help you to keep all the records unique, even if the values in the other two columns are the same. You can have data that is the same in the other columns and the id will make them all unique records. If you have not done so already you should have a look at our free trainings and tutorials that might help you gain a better understanding:

tutorials-and-training/