What does this error message mean? 1

[frogggg]

When trying to update a recordset:
rsValidatePassword.updateRecord

I get this message:
Insufficient key column information for updating or refreshing.

What does it mean and how do I fix it?

Thanks in advance!

 

[Jorgandr]

Well it sounds like you are trying to update a multi table query and it doesn't know which table you want to update.

you might have to use another SQL statement to identify the key field values in the source table

 

[bperry]

In SQL Server, this can come up when the table you are updating has no primary key (which of course would uniquely identify each row.) So when the database engine goes to do an update, it can't figure out exactly which row to update, so it bails out and gives us that error message.

Is there a unique PK column on the table you are updating?

 

[frogggg]

Jorgandr, that was it exactly! How did you ever guess?
Well, now can you help me make the next sql statement.

The story is like this:
The recordset checks a username and password from an html form from the page before against the database, and brings back the entire record for approval and possible update. Several of the fields in the table are lookup fields, so in order to display the info properly, I had to include other tables, as you figured out. Here's the sql statement that I have so far. Do I need to make another recordset without the additional tables and update from that? Another question: the fields that were lookups, I hadn't displayed the dropdown box, because I didn't know how to do that and display the record from the database. Can you help with this too? Will this make the same error/problem as before?

Thanks for your help. Here's the query.
"SELECT CandidateContactInfo.*, State.StateName, CareerLevel.CareerName " _
& "FROM CandidateContactInfo, State, CareerLevel " _
& "WHERE CandidateContactInfo.State = State.StateID " _
& "AND CandidateContactInfo.CareerLevel = CareerLevel.CareerID " _
& "AND UserName='" & Request.QueryString("txtUserName&quot & "'OR UserName='" & session("UserName&quot _
& "' AND Password='" & Request.QueryString("txtPassword&quot & "'OR Password='" & session("Password&quot & "'"

 

[Jorgandr]

ok... Here is a quick example of what you want to do. You can use your SELECT statement from before but you do need to include 2 more recordsets if you want to use drop downs. in your form you want to do this..

RSState = conn.execute("SELECT StateName, StateID FROM STATE&quot

<SELECT Name=&quot;State&quot; Size=&quot;1&quot;>
<%DO UNTIL RSState.eof%>
<OPTION Value=&quot;<%Response.write RSState(&quot;StateID&quot%>&quot; <%IF RSState(&quot;StateID&quot = RSYourRecordSet(&quot;CandidateContactInfo.State&quot THEN%>SELECTED<%end if%>><%Response.write RSState(&quot;StateName&quot%></OPTION>
<%RSState.MoveNext%>
<%loop%>
</SELECT>

and of course now your UPDATE SQL statement can compare the Request.FOrm(&quot;State&quot with the CandidateContactInfo.State field... Hope this helps!

 

[frogggg]

Thanks for your help.
Actually, I made a mistake. I did have the dropdown boxes working fine - I'm using VI, so the code you posted for populating the dropdown boxes is not really necessary - I got that figured out.

But I still don't know how to get the update working. Do I need to take the state and careerlevel info out of the first sql and I should populate those dropdown boxes using the code you posted?

Please help.
Thanks.

 

[Jorgandr]

Well in order to use dropdowns correctly your StateID and CareerID need to be the Value part of your dropdown's while the CareerName and StateName are only there for the user's benefit. For Instance using my code above, when they select OH-Ohio the value or the Request.Form(&quot;State&quot value is going to be the StateID that is attached to that StateName. Now because your dropdown's have values that are in your Main Table, CandidateContactInfo, your Update statement will be on that Ex.

UPDATE CandidateContactInfo SET State=&quot; & Request.Form(&quot;State&quot & &quot;, CareerLevel=&quot; & Request.Form(&quot;CareerLevel&quot & &quot; WHERE CustomerID=&quot; & Request.FOrm(&quot;CustomerID&quot & &quot;

Now because your updating on a single table and not a multipe you should have no problems.