Error 6153 - "Insufficient Key Column Information for Updating or Refreshing
John Rutherford
Have a Datagrid bound to Data Control
When I try to update record using DataGrid get the error
message "Insufficient Key Column Information for Updating
or Refreshing".
I have searched and found 2 articles in the MSDN Knowledge
Base describing this but they don't provide a solution for
my problem.
Any suggestions appreciated.
Cheers,
John
Val Mazur
Do you have primary key in your table? Are you using any joins in your
SELECT statement?
--
Val Mazur
Microsoft MVP
"John Rutherford" <felic...@optusnet.com.au> wrote in message
news:017f01c2c840$671ab1a0$8df82ecf@TK2MSFTNGXA02...
John Rutherford
Yes, I have 3 tables each with a primary key. This key
appears in each table as a field. I have created
relationships and the SQL includes JOIN statements as
follows in code;
********************************************************
Private Sub DataCombo1_Click(Area As Integer)
Dim strsql As String
Dim Errorcondition As Integer
On Error GoTo c1c_Error
'---By Event (All Swimmers)
If HId <> -1 Then
Select Case HId
Case 1, 2, 3 '---Free
DataGrid1.BackColor = &H80FFFF
Case 4, 5, 6 '---Breast
DataGrid1.BackColor = &HC0C0FF
Case 7, 8, 9 '---Back
DataGrid1.BackColor = &HC0FFC0
Case 10, 11, 12 '---Butt
DataGrid1.BackColor = &H80C0FF
Case 13, 14 '---Medley
DataGrid1.BackColor = &HFFFFC0
End Select
strsql = "SELECT Swimmers.FirstName,
Swimmers.LastName, Times.Min, Times.Sec"
strsql = strsql & " FROM Swimmers INNER JOIN
(Events INNER JOIN Times ON Events.EventID =
Times.EventID) ON Swimmers.SwimmerID = Times.SwimmerID"
strsql = strsql & " Where (((Events.EventID) = "
& HId & ") And ((Times.Date) = #" & SwimMeetDate & "#))"
strsql = strsql & " ORDER BY Times.HeatSortOrder
DESC;"
With Adodc1
.ConnectionString
= "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
Info=False;Data Source=" & dBaseName
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.CommandType = adCmdText
.RecordSource = strsql
.Refresh
End With
Set DataGrid1.DataSource = Adodc1
DataGrid1.ClearFields
DataGrid1.ReBind
DataGrid1.Refresh
RepaintGrids
End If
c1c_resume:
On Error GoTo 0
Exit Sub
c1c_Error:
Errorcondition = Err
strsql = "Error " & Error(Errorcondition)
strsql = strsql & vbCrLf & "in TimesCombo1"
MsgBox strsql, vbExclamation, "TimesCombo1"
Resume c1c_resume
End Sub
**************************************************
the variable HId is selected from the dbCombo box and
passes to this sub.
Cheers,
John
>.
>
Val Mazur
Looks like your query is to complex for provider to build appropriate
INSERT/UPDATE/DELETE statements. Try to specify Unique Table dynamic
property for your main table (I think it is Swimmers in your case), it could
help. Otherwise probably you would need to execute your own action queries
to make changes in database. To specify Unique Table dynamic property you
could use code, something like
MyRecordset.Open 'you need to open recordset first
.....
MyRecordset.Properties("Unique Table").Value="Swimmers"
--
Val Mazur
Microsoft MVP
"John Rutherford" <felic...@optusnet.com.au> wrote in message
news:043201c2c8d2$5de0dbe0$d6f82ecf@TK2MSFTNGXA13...
John Rutherford
Afraid you've lost me. How would you incorporate your
suggestion into a datagrid bound to a ADODC data control?
Cheers
JR
>-----Original Message-----
>John,
>
>Looks like your query is to complex for provider to build
appropriate
>INSERT/UPDATE/DELETE statements. Try to specify Unique
Table dynamic
>property for your main table (I think it is Swimmers in
your case), it could
>help. Otherwise probably you would need to execute your
own action queries
>to make changes in database. To specify Unique Table
dynamic property you
>could use code, something like
>
>MyRecordset.Open 'you need to open recordset first
>......
>.
>
John Rutherford
I managed to overcome this error by including the Primary
Key filed in my SQL statement for the table that the
DataGrid ultimatley affets. Hop e this helps anyone with
similar problem.
Cheers,
JR
>.
>