update record error

[frogggg]

When I try to update a recordset
rsValidatePassword.updateRecord
I get this error:
Insufficient key column information for updating or refreshing.

The page has a sql statement which checks a username and password from the page before against the db, and brings up all the user's info, and allows changes if necessary.

The sql statement has more than one table in it which I understand is what is causing the problem.

This is because two of the values from the user info table are lookups, so in order not to display to the user unidentifiable numbers, I needed the following sql statement:
rsValidatePassword.setSQLText("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 & "'&quot

What can I do to populate the lookup(listbox) fields with the correct info while still allowing an update? Do I need another recordset?

Thanks in advance for your help!

 

[MerlinB]

Yes! It is not the greatest to try to update a recordset from more than one table - though you can often get away with it. The server or ODBC has to decide which table(s) you wanted to update - and often decides that they all do - in which case all tables must include key columns in the recordset. This clearly is not the intention here - but how is the system to know that? (ADO.Net improves this by letting you specify the UPDATE, INSERT and DELETE sql for each SELECT clause).

In Your case, if you have drop-down lists on the page, then you do not need to de-code them in SQL anyway - the drop-down does this for you via the VALUE attribute. So you should have 3 recordsets - Candidate,Career & State.

You really aught to use query parameters too
"AND UserName=? AND Password=?"

then you just do:
rsValidatePassword.setParameter 0, txtUserName.value
rsValidatePassword.setParameter 1, txtPassword.value
instead of changing the SQL string.

WHY - because you never worry about quotes characters in the parameter, which normally break SQL as you have shown. (ie if the users name is o'brian)

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

Okay, I got the update to work and the listboxes to display properly - almost.
Most of the elements on the form are brought by the first recordset which I modified to have only one table:
rsValidatePassword.setSQLText("SELECT CandidateContactInfo.* " _
& "FROM CandidateContactInfo " _
& "WHERE UserName='" & Request.QueryString("txtUserName&quot & "'OR UserName='" & session("UserName&quot _
& "' AND Password='" & Request.QueryString("txtPassword&quot & "'OR Password='" & session("Password&quot & "'&quot

Then I populated the fields which come from lookups using another recordset like you said with the sql as follows:
rsState.setSQLText("SELECT State.* " _
& "FROM State, CandidateContactInfo " _
& "WHERE CandidateContactInfo.State = State.StateID " _
& "AND UserName='" & Request.QueryString("txtUserName&quot & "'OR UserName='" & session("UserName&quot _
& "' AND Password='" & Request.QueryString("txtPassword&quot & "'OR Password='" & session("Password&quot & "'&quot

This allows the update, but the lookup fields only display the value that belongs to UserName=request.querystring, instead of displaying all the options.
What am I doing wrong?

By the way, I would love to use UserName = ? and setParameters, but for some reason, I do not have this option. The parameters section on the recordset is greyed out, and when I try to manually set the params as you mentioned, I get an error something like setParameter info not called. I don't remember the exact message, it's been a few months. But someone told me to try this and it always works, so...

Thanks again.

 

[MerlinB]

Are you using DTCs for the list box? If so, then you plug in the rsValidatePassword field for state id on the first tab of the dropdown's properties.
The state recordset SHOULD NOT be filtered - remove the username and password bit. Then plug the recordset name, descriptive column name and id column name into the Second Properties tab of the dropdown. Now it lists ALL states - but auto selects the one from the users record.

If you are using a database other than SQLServer, then you may need to specify parameters in the SQL command differently. Some databases require %name% or similar.
It is the database server that 'informs' VI about parameters that it finds in the SQL command. Once you get them working, then you will wonder how you ever worked without them!

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

I know what you said should work, but for some reason it doesn't. If I remove all WHERE clauses from the lookup recordsets, it just shows the whole list of states and does not select any. Now it just started a new nonsense, and prints each option 3 times! Even when I put the filter back in, it auto selected the user's option, and instead of the dropdown being empty, it had 3 more entries of the same option. What's going on here?!

BTW, yes, I am using dtc's.

And I would love to get the parameters working. What is the database server and what is wrong with it? I'm using Access Database, dsn connection.

Thanks again!

 

[frogggg]

Okay, I figured out why there are 3 of every entry - that's how many candidates there are in my test database!
What am I doing wrong?!

These are my sql statements:
rsValidatePassword.setSQLText("SELECT CandidateContactInfo.* " _
& "FROM CandidateContactInfo " _
& "WHERE UserName='" & Request.QueryString("txtUserName&quot & "'OR UserName='" & session("UserName&quot _
& "' AND Password='" & Request.QueryString("txtPassword&quot & "'OR Password='" & session("Password&quot & "'&quot
rsValidatePassword.open
rsState.setSQLText("SELECT State.* " _
& "FROM State, CandidateContactInfo&quot

rsState.open

rsCareerLevel.setSQLText("SELECT CareerLevel.* " _
& "FROM CareerLevel, CandidateContactInfo&quot
rsCareerLevel.open

and in the listbox dtc's, the data tab recordset is rsValidatePassword and the field is State, which is a number field which looks up a table, and the lookup field the recordset is rsState, the bound column is StateID and the list field is StateName, and I am getting the whole list with every option listed 3 times, and the correct one is not selected.

Thanks for your help.

 

[MerlinB]

A listbox takes two items of data:
1 - a list of id's and values to display. The display values can be the same as the non-visible id values. In your case this is the list of states
SELECT ID, CAREER_DESCRIPTION FROM CareerLevel

2 - a single ID value, that holds the 'selected' value. This comes from the 'master' table in a master-child relationship. The child table being the list of values.

Your SQL clause
SELECT CareerLevel.*
FROM CareerLevel, CandidateContactInfo
is known as a 'Cartesian-Join' - two tables without any WHERE clause relationship. The result is every row from one table multiplied by every row from the other. So two 10 row tables has a 100 row result set.
You DO NOT NEED the CandidateContactInfo table here - just remove it (and from the State query).

Furthermore, the list populates the moment that you open the recordset. This means that you can add additional rows (ie the '<pick a state>' entry to the top) using the

lstMyList.addItem 'display', 'id', iPosition

method AFTER the recordset open. To re-populate a list, you should clear the list first, then open the recordset.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

Thank you. Now it isn't listing each option 3 times.
But, it's still not showing the canididate's choice selected.
What do I have to do to make it selected?
I'm not trying to populate the list, I don't want to add any values. I just want that if the candidate lives in Michigan, Michigan should be selected, and not Alabama, which is first in the list of states.

 

[MerlinB]

Drop-Down properties...
First Tab
DataSource: rsValidatePassword
BoundColumn: CareerLevel

Second Tab
ListSource: rsCareerLevel
IDColumn: CareerID
DisplayColumn: CareerName

That should be all that you need. It may help to open the List Source (rsCareerLevel) before the Data Source (rsValidatePassword) - but I do not think that it matters.
The drop-down makes the link between CareerID and CareerLevel in order to 'select' the appropriate entry.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[frogggg]

I wasn't successful in getting the listboxes to work properly, so I just ended up using regular asp to populate them instead of the dtcs. It works fine, but the code I'm using is full of <% instead of response.write which I believe are more efficient.
How can I rewrite this code to look neater?
<SELECT Name=&quot;CareerLevel&quot; Size=&quot;1&quot;>
<%DO UNTIL rsCareerLevel.eof%>
<OPTION Value=&quot;<%Response.write rsCareerLevel.fields.getValue(&quot;CareerID&quot%>&quot;
<%IF rsCareerLevel.fields.getValue(&quot;CareerID&quot = rsValidatePassword.fields.getValue(&quot;CareerLevel&quot THEN%>
SELECTED
<%end if%>
><%Response.write rsCareerLevel.fields.getValue(&quot;CareerName&quot%></OPTION>
<%rsCareerLevel.MoveNext%>
<%loop%>
</SELECT>

Thanks again.

 

[MerlinB]

Here is an answer found in the MSDN library (25+ tips to improve performance by Nancy Cluts, Len Cardinal, George V. Reilly):

Tip 15: Batch Inline Script and Response.Write Statements

The VBScript syntax <% = expression %> writes the value of &quot;expression&quot; to the ASP output stream. If response buffering is not turned on, then each of these statements results in writing data to the browser over the network in many small packets. This is slow. Also, interspersing small amounts of script and HTML causes switching between the script engine and HTML, reducing performance. Thus, use the following tip: Replace closely-bunched inline expressions with one call to Response.Write. For example, in the following sample, there is one write to the response stream per field per row, and many switches between VBScript and HTML per row:

<table>
<% For Each fld in rs.Fields %>
<th><% = fld.Name %></th>
<%
Next
While Not rs.EOF
%>
<tr>
<% For Each fld in rs.Fields %>
<td><% = fld.Value %></td>
<% Next
</tr>
<% rs.MoveNext
Wend %>
</table>

The more efficient code, below, has one write to the response stream per row. All of the code is contained within one VBScript block:

<table>
<%
For each fld in rs.Fields
Response.Write (&quot;<th>&quot; & fld.Name & &quot;</th>&quot; & vbCrLf)
Next
While Not rs.EOF
Response.Write (&quot;<tr>&quot
For Each fld in rs.Fields %>
Response.Write(&quot;<td>&quot; & fld.Value & &quot;</td>&quot; & vbCrLf)
Next
Response.Write &quot;</tr>&quot;
Wend
%>
</table>

This tip has a much bigger effect when response buffering is disabled. It's best to enable response buffering, and then see if batching Response.Write helps performance.

(In this particular example, the nested loop that builds the body of the table (While Not rs.EOF...) can be replaced by a carefully constructed call to GetString.)

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)