• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

ADO Data Control?

My form includes: ADO Data Control:Adodc1
                          VsFlexgrid: FlexGrid
                   Command Button:cmdAdd,
                  Textbox: txtDay,txtHour(used to addnew and update)
My Database has:
                          One table: Table1
                          Two field:                   Day,Hour                    datatype:date/time
                          In field Day, the rows have same value is: 01/01/05
                  In field Hour, the rows have same value is: 22:00:00
Here is my code:

Private Sub cmdAdd_Click()
     Adodc1.Recordset.Fields("Day") = CDate(txtDay.Text)
     Adodc1.Recordset.Fields("Hour") = CDate(txtHour.Text)
End Sub

Private Sub cmdDel_Click()
End Sub

Private Sub cmdUpdate_Click()
     Adodc1.Recordset.Fields("Hour") = CDate(txtHour.Text)
End Sub

Private Sub Form_Load()
     Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\db1.mdb"
     Adodc1.RecordSource = "SELECT * FROM Table1"
     Adodc1.CursorType = adOpenDynamic
     Adodc1.LockType = adLockOptimistic
     Set FlexGrid.DataSource = Adodc1
End Sub

(Please note that field Day has the same value in every row.)
When I press cmdUpdate button, a error happens:

    Run-time error -2147467259 (8004005):
    Key column information is insufficient or incorrect. Too many rows were affected by update.

After I run my program again.
All of records in database have the same value
I only want to update one row that I chose. Anybody help me?

The same error happens when I press cmdDelete button
After I run my program again.
All of records in database have deleted
I only want to delete one row that I chose. Anybody help me?
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
add a primary key to your table
DoDinhNguyenAuthor Commented:
I don't want add primary key.do you have another solution?
Éric MoreauSenior .Net ConsultantCommented:
You need one! You could add a autonumber field with which you never interact.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Hi, DoDinhNguyen.

emoreau is right--one of the primary constraints of modern database development is the requirement that every record/row in a table be unique (whether based on a single field in that record or a combination of fields).  Setting a primary key is the only way to interact with your database.
Hi DoDinhNguyen:
> I don't want add primary key
Why not?

BarshamDevelopment Team LeadCommented:
You Have no other solution in this planet try use metaphysic solution maybe work
there is no problem to add a primary key with autonumber to your DB else intend on your words .

listen to professional kid and be better ;)
1. Get Primary key

2. Get rid of ADODC

When you click on a command button you know which record it is for example day= 11 and hour = 12:00pm.

I suggest you change your Adodc1.RecordSource = "SELECT * FROM Table1" to
Adodc1.RecordSource = "SELECT * FROM Table1 where day = 11 and hour  12:00pm" (on click)and you move to the last one, so it wil delete the last one if you have multiple records with the same data.
If you don't have multiple records with the same data you don't have to go to the last record in the record set.

kind regards


PS: so you don't have to use a primary key.

Standard "It can not be done, sorry" = correct answer

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now