• 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,
                                              cmdUpdate,
                                              cmdDel
                  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.AddNew
     Adodc1.Recordset.Fields("Day") = CDate(txtDay.Text)
     Adodc1.Recordset.Fields("Hour") = CDate(txtHour.Text)
     Adodc1.Recordset.Update
End Sub

Private Sub cmdDel_Click()
     Adodc1.Recordset.Delete
End Sub

Private Sub cmdUpdate_Click()
     Adodc1.Recordset.Fields("Hour") = CDate(txtHour.Text)
     Adodc1.Recordset.Update
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
     Adodc1.Refresh
     Set FlexGrid.DataSource = Adodc1
     FlexGrid.Refresh
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?
0
DoDinhNguyen
Asked:
DoDinhNguyen
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
add a primary key to your table
0
 
DoDinhNguyenAuthor Commented:
I don't want add primary key.do you have another solution?
0
 
Éric MoreauSenior .Net ConsultantCommented:
You need one! You could add a autonumber field with which you never interact.
0
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.

 
edwardiiiCommented:
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.
0
 
DabasCommented:
Hi DoDinhNguyen:
> I don't want add primary key
Why not?

Dabas
0
 
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 ;)
0
 
leonstrykerCommented:
1. Get Primary key

2. Get rid of ADODC
0
 
orhanmmCommented:
Hi,

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

orhanmm

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

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

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