Insufficient key column information for updating and refreshing. 2

[Trudye]

Hi VB'ers

I just discovered something I thought I would pass along. Of course I'm probably the last one in the world to realize this but here goes.

I was getting the msg:
insufficient key column information for updating and refreshing.

I was loading combo boxes which I learned how to do on Tek Tips thank you very much. Well I decided to get fancy and try and load two combos at once. I am building an Application form and a few of the combo options that apply to the applicant also apply to the spouse (ie. sex). So I figured what the HEY since they are coming from the same table I'll load the Applicants combo and the Spouse's all at the same time. WRONG!!

As proof of my dum-dum move here is the code:

Public Sub LoadCombos()
Dim adoCombo As ADODB.Connection
Dim adoRec As ADODB.Recordset
Dim adoRS4 As ADODB.Recordset
Dim adoRS5 As ADODB.Recordset

Dim conString As String
Dim sCode As String
Dim sElig As String
Dim sElig2 As String
Dim sNeed As String
Dim sNeed2 As String

Set adoCombo = New ADODB.Connection
Set adoRec = New ADODB.Recordset
Set adoRS4 = New ADODB.Recordset
Set adoRS5 = New ADODB.Recordset

conString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data source= C:\VB Practice\zGDS_Data_V6_1_2k.mdb"

'LOAD PROGRAM CODES
adoCombpen conString
adoRec.Open "[Program Codes]", adoCombo
sCode = cboPgmCode

Do Until adoRec.EOF
cboPgmCode.AddItem (adoRec!pgm_code & " " & adoRec!pgm_code_names)
adoRec.MoveNext
Loop
cboPgmCode = sCode

'LOAD Eligibility CODES
adoRS4.Open "[Eligibility]", adoCombo
sElig = cboEligibility
sElig2 = cboElig2 '

Do Until adoRS4.EOF
cboEligibility.AddItem (adoRS4!Eligibility)
' cboElig2.AddItem (adoRS4!Eligibility)
adoRS4.MoveNext
Loop
cboEligibility = sElig
cboElig2 = sElig2

'LOAD Need CODES
adoRS5.Open "[Needs]", adoCombo
sNeed = cboNeed
sNeed2 = cboNeed2

Do Until adoRS5.EOF
cboNeed.AddItem (adoRS5!need)
' cboNeed2.AddItem (adoRS5!need)
adoRS5.MoveNext
Loop
cboNeed = sNeed
cboNeed2 = sNeed2

adoRec.Close
adoRS4.Close
adoRS5.Close
Set adoRec = Nothing
Set adoRS4 = Nothing
Set adoRS5 = Nothing
End Sub

Just thought I would pass that a long, I hope this helps someone.

Trudye

 

[SemperFiDownUnda]

I'm unsure what your problem is but if you pop 2 combo boxes on a form and 1 command button and put this in the click event of the command button it will populate both combos just fine

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MySQLServer"
    conn.Open
    Set rs = New ADODB.Recordset
    rs.Open "Customers", conn
    Do Until rs.EOF
        Combo1.AddItem rs.Fields("CompanyName").Value
        Combo2.AddItem rs.Fields("CompanyName").Value
        rs.MoveNext
    Loop
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

all you have to do is change the connection string to your Northwind database.




What exactly are you trying to do in these lines?

sCode = cboPgmCode
cboPgmCode = sCode
sElig = cboEligibility
sElig2 = cboElig2 '
cboEligibility = sElig
cboElig2 = sElig2
sNeed = cboNeed
sNeed2 = cboNeed2
cboNeed = sNeed
cboNeed2 = sNeed2

 

[Trudye]

Hi Semper:

Thanks for responding.

Maybe my code failed to work because I used a different provider (ie. Provider=Microsoft.Jet.OLEDB.4.0). For some reason Jet somtimes suffers from key confusion when loading combo's, or so Microsoft says. I'll try it your way and see if it works.

I am using the fields you asked about (ie. cboNeed = sNeed
cboNeed2 = sNeed2) to put back the value that was orginally in the first record. I noticed after I loaded the combo boxes it would blank out the existing combo value in the first rec. After I get everything working I am going to change the sequence so that the combo's get loaded first and then the data.

Thanks
Trudye

 

[Trudye]

I tried everything I can think of to get over, around and through this error but it won't go away.

When I remove the 2nd loading of "Needs"/"Elig" tables (see 1st entry in this thread) I don't get an error. I have moved the loading of the 2nd combo to another procedure, changed the sequence of the process and nothing seems to help.

When I investigated Run Time error 2147467259 in the MS Knowledge base it was no longer there (I was able to find it last week). So I tried Tek Tips Advanced Search. Nada.

However I do remember reading that the problem stemmed from ADO not knowing where to put the data because the key was not unique. So I figured because ADO was trying to deal with one key and two combos at the same time it was getting confused. When I separated the loading of the combo boxes it worked at first. Now it no longer works and I am back to square one. Interestingly enough it is not blowing up on the load of "Needs"/"Elig" tables. It is blowing up on the subsequent loading a lookup combo box. Which works find if I remove the "Needs"/"Elig" load.

The text to the msg is: Insufficient key column information for updating or refreshing

What the heck is going on? Does anyone have any ideas?

I hope you were able to follow that

Thank much
Trudye

 

[SemperFiDownUnda]

I'm really clueless on what is happing to you.

My first suggestion.
DON'T use default properties
My second suggestion
DON'T use the BANG notation

Do Until adoRec.EOF
cboPgmCode.AddItem (adoRec!pgm_code & " " & adoRec!pgm_code_names)
adoRec.MoveNext
Loop


should look like this

Do Until adoRec.EOF
cboPgmCode.AddItem adoRec.Fields("pgm_code&quot.value & " " & adoRec.fields("pgm_code_names&quot.Value
adoRec.MoveNext
Loop

where you are saying you are having a key problem I'm unsure.
ADO doesn't care about anything to do with a key here.
It doesn't care where you put the data.
you are asking for a copy of the data, what you do with it has NOTHING to do with ADO
.AddItem method of the combo box takes up to 2 parameters.
1st being a string to display in the combo box
2nd being optional is the index where to insert this in the list

As far as ADO goes you have a recordset.....you are pointing to a current record. It does not care where you shove that data.

Are you leaving out some databound issues some where?

 

[CCLINT]

>DON'T use default properties

Hey SemperFiDownUnda, I know this is a little off subject, but what do you feel about using the shorter Collect property instead of the longer, slower Fields(0).Value property when returning just the field values (when accessing more properties, a using field object variable is also more efficient)?
I find it more efficient, and you are not dealing with default properties or the a bang either.

 

[SemperFiDownUnda]

CCLINT do you mean using
rs.Fields("LastName&quot.value
instead of
rs.Fields.Item("LastName&quot.value
?

I have to admit I break down on that and use the default.

A test I did with 10 million rows run on multiple systems has shown that using the Bang notation is just as slow as
rs.Fields.Item("LastName&quot.value. Microsoft do teach in the MOC that the BANG is faster but it is a lye.

I have a problem in most cases with default properties because it can be more confusing when you jump into an exsisting project trying to figure out if they are wanting to use a default property when doing a set or the actual object itself. Also if you start using full notation you can start using shortcuts like

Dim fld as ADODB.Field
set fld = rs.Fields("LastName&quot

and use fld to access it in a loop which is MUCH faster.
I'm one to drop use of syntax that is there for "Backward compatibility"

Just my opinion.

 

[CCLINT]

No, that's not what I meant.
(I guess I wasn't feeling too good yesterday and didn't explain thouroughly or correctly.

I agree with the default properties and use of a banger.

And yes, using an ADODB.Field obeject variable is also one point I mentioned which is faster when accessing multiple properties and methods of a field (or whatever).

No, what I meant is using the (hidden) Collect method:

SomeValue = rs.Collect(0)
SomeValue = rs.Collect("SomeField&quot

 

[SemperFiDownUnda]

Star for you, you've taught me something new...now for me to figure out what you've taught me. 8)

 

[SemperFiDownUnda]

I've taken a look at the .Collect in the ADODB.Recordset.

What it is is a direct reference to the .Value properties.

Thus
rs.Collect("LastName&quot
points to
rs.Fields("LastName&quot.Value

But since you remove 1 level of resolving you get a speed increase.

I have no problem using it because it isn't useing a default property
rs.Collect("LastName&quot
can only refer to 1 thing
where using
rs.Fields("LastName&quot
could refer to the ADODB.Field object or the ADODB.Field.Value

Thanks agian CCLINT for point me at it.

 

[SonOfEmidec1100]

Although I cant think that its relevant you can get a similar error in SQL - "key column information is insufficient or incorrect. Too many rows were affected by the update."

This occurs if you have a table with no unique index constraints and have duplicate rows. Then try to change or delete one of these rows directly using Enterprise Manager Open table view.

Just thought I'd mention it.

 

[SemperFiDownUnda]

Agreed SonOfEmidec1100, the original description of the error looked like to me that they where using databinding of some type.

 

[Trudye]

This thread had taken on a life of its own. I am a novice and you guys left me in the dust a looooong time ago.

I have not even tried a collection outside of the Beginners book example.

SonOfEmidec1100’s observation was right-on, that is what I read in MS Knowledgebase. I checked both tables and neither one had duplicate entries. Two thoughts came to my little mind, maybe the loading of two combo boxes was causing the appearance of duplication. Also if duplication existed in any of the tables when I load them individually the error would still occur wouldn't it?

I have not given up. I have just been away from this thread because I lost my hdd and thus had to start my VB project over form scratch. It was the only thing I was not backing up. I also had one day’s work to make up but that was not so bad.

Thanks guys for the feedback. When I get better at this I know that the light bulb will come on and I will see the wisdom of your words.

Be well,
Trudye

 

[CCLINT]

Try using a Server-side cursor.