Runtime error

[prajana]

Hey can anyone tell me what is this error all about?

"Key column information is insufficient or incorrect. Too many rows were affected by update."

I am trying to update some fields in my database tables and its giving this error. When i say debug its highlighting the update statement. ie ADODC.Recordset.Update

My table which i am updating is a weak entity without a primary key. I inserted a primary key but it din't help :sad:

[Jx_Man]

post the codes friend.. :)

[prajana]

Private Sub cmdAdd_Click()
If txtLecturesattended.Text <> "" Then
    If Val(txtLecturesattended.Text) <= Val(txtTotallectures.Text) Then
        'Adodc4.RecordSource = "select  RollNo,Name, " + cmbMonth.Text + " from LecturesAttended where SubjectName='" + cmbSubject.Text + "'"
        'Adodc4.Refresh
        
        Adodc4.Recordset.Fields(cmbMonth.Text) = txtLecturesattended.Text
        Adodc4.Recordset.Update
        MsgBox "Attendance added.", vbInformation, "Add Attendance"
        txtLecturesattended.Text = ""
        txtLecturesattended.SetFocus
        Adodc4.Recordset.MoveNext
        If Adodc4.Recordset.EOF = True Then
        'Adodc4.Recordset.Update
            Unload Me
            TeachersAccount.Show
        End If
    Else
            MsgBox "Lectures Attended cannot be more than lectures taken. Please enter again."
            txtLecturesattended.Text = ""
            txtLecturesattended.SetFocus
        End If
    Else
    MsgBox "Enter the number of lectures attended."
End If
    

End Sub

First i am adding the lectures taken and then for 1-1 student i am adding the attendance.
But its also saying "Row cannot be located for updating. Some values may have been canged since it was last read."

[Jx_Man]

Adodc4.Recordset.Fields(cmbMonth.Text) = txtLecturesattended.Text you must input field name and add adodc4.refresh before it
it should be like this :

Adodc4.Refresh
Adodc4.Recordset.Fields("FieldName") = txtLecturesattended.Text
Adodc4.Recordset.Update

[AndreRet]

You forgot to add the Add new part first.:)

Adodc4.Recordset.AddNew
Adodc4.Recordset.Fields(cmbMonth.Text) = txtLecturesattended.Text
Adodc4.Recordset.Update

[Jx_Man]

AndreRet : you seems right too..hmmm..i feel confused..his procedure name is "Add" but he tried to "update" an information..what update refers to? add a new record or edit a record? :confused:

[AndreRet]

:) No his procedure starts with a command button called cmdAdd. If you look at the code, he wanted to add a record with a !data control! with no call to inisiate the adding, hence the code of mine "AddNew", then get the data, then update the new record.;)

[Jx_Man]

yes you right. i didn't noticed it. :)

[prajana]

Hey i am posting my files here. Can check my AddAttendance form. I am trying to update the records.

cmbMonth refers to my fieldname in table.ie the names of the month from June-March.

[AndreRet]

Prajana, please read the above posts. I have posted the answer there, and here it is again -

adodc4.Recordset.AddNew 'THIS IS NEW. YOU HAVE TO ADD THIS...
        Adodc4.Recordset.Fields(cmbMonth.Text) = txtLecturesattended.Text
        Adodc4.Recordset.Update

[prajana]

No ya i already have records in my table. I want to update the attendance in the month.

[AndreRet]

Ah, now it makes sense. You then have to uncomment

'Adodc4.RecordSource = "select RollNo,Name, " + cmbMonth.Text + " from LecturesAttended where SubjectName='" + cmbSubject.Text + "'"
'Adodc4.Refresh

this part -

Adodc4.RecordSource = "SELECT  RollNo, Name, " + cmbMonth.Text + " FROM LecturesAttended WHERE SubjectName= '" + cmbSubject.Text + "'"
'This should do the trick...

[prajana]

I did that but its not working. Is it the primary key that is creating this problem?

I've been trying hard on this for a long time now but cant figure out what is the problem.
:'(

[AndreRet]

On which line of your code is the error occurring?

The select statement is fine, there must be something else. Is there text in the combo boxes before the sql is called?

Also what does the error say?

[Jx_Man]

try to click row that u want to update on the datagrid then click add button

[Jx_Man]

first uncoment the recordsource part..
then add this procedure :

Public Sub AdoConn()
On Error Resume Next
Adodc4.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\ams\Attendancedb.mdb;Jet OLEDB:Database Password="
Adodc1.RecordSource = "SELECT * FROM LecturesAttended"
Adodc1.Refresh
End Sub

it seems like this :

Call AdoConn ' call procedure adoconn
Adodc4.RecordSource = "select  RollNo,Name, " + cmbMonth.Text + " from LecturesAttended where SubjectName='" + cmbSubject.Text + "'"
Adodc4.Refresh
Adodc4.Recordset.Fields(cmbMonth.Text) = txtLecturesattended.Text
Adodc4.Recordset.Update
MsgBox "Attendance added.", vbInformation, "Add Attendance"

[prajana]

I have duplicate data in my LecturesAttended table. Maybe that is the reason it cannot update the table. I have simply introduced a primary key as SrNo but getting the same error. The codes above din't help because there is repeated data.

Hope you understood my problem now..

Its like this..
My LecturesAttended table is supposed to be a weak entity.
LecturesAttended(SrNo, StudentID, RollNo, Name, SubjectName, June,..., March)

In this table 1 student is assigned many subjects. Thats where the data is repeated. RollNo as well as SubjectName is repeated.

The very first time when i have only 1 subject its adding the attendance..

Any suggestions over this?

[monarchmk]

Primary keys does not solve problem, you need Unique Column in database. Add column with autonumber feature and your problem is solved.

And remember that in almost every database you need Unique column. That a way that SQL distinct rows with same values

[prajana]

Ok got it! I now have a SrNo field as primary key in that table. Its an Autonumbered column. But how does it help now?
How do I update the records now?

[monarchmk]

now in select add SrNo like this

Adodc4.RecordSource = "select  SrNo,RollNo,Name, " + cmbMonth.Text + " from LecturesAttended where SubjectName='" + cmbSubject.Text + "'"

and rest of code should work...
But this Syntax will update only current record

If you have , for example , record like this where SubjectName is equal

SrNo, StudentID, RollNo, Name, SubjectName, June,..., March
1 XXX YYY ZZZ AAA BBB
2 XXX YYY ZZZ AAA BBB
3 XXX YYY ZZZ AAA BBB
4 XXX YYY ZZZ AAA BBB
5 XXX YYY ZZZ AAA BBB

After first update you will face this situation
SrNo, StudentID, RollNo, Name, SubjectName, June,..., March
1 XXX YYY ZZZ AAA NewValue
2 XXX YYY ZZZ AAA BBB
3 XXX YYY ZZZ AAA BBB
4 XXX YYY ZZZ AAA BBB
5 XXX YYY ZZZ AAA BBB

Only first record after select is updated... so you should go thru all record with Adodc4.Recordset.MoveNext and applying same changes or use
UPDATE SQL syntax for updating all records that match criteria.

Also one suggestion, is there any possibility to avoid using column for months? In 2 years you will end with 24 columns for months and SQL query's will be hell hard to write, while filter for rows is far more easy to write

[Jx_Man]

monarchmk, Good Point.