asked on

SQL Server complains of an unfound column when using rs.update

Here's my problem...

I have a table with several fields

GroupName|OMTimestamp|intValue1|intValue2|intValue3|BusyHour

I want to mark the busiest hour for each combination of GroupName, Date.
So i open a recordset that groups by GroupName, Date and order it by the sum of (intValue1 + intValue2 + intValue3) as TotalCount descending.

Then i iterate through the recordset and every time i see a new groupname or date i set that busyHour to true (first one in a descending query is always the highest)
However, when i try to do a rs.update it tells me that the column TotalCount does not exist. Of course it doesn't exist its a calculated value!

The weirdest part however, is that up until 2 days ago...this code worked FINE...

Here's the code just in case

rs.Open "SELECT GroupName, BH, REGNIVIC + LOCREQIC + CSSIIVIC + RGCNIVOG + RTEREQOG + TRANIVIC AS IS41Count FROM pt_OMS_IS41 " & _
"WHERE OMTimestamp BETWEEN '" & Format(dteDate, "mm/dd/yyyy") & "' AND '" & Format(dteDate, "mm/dd/yyyy") & " 11:59:59 PM' " & _
"ORDER BY GroupName, REGNIVIC + LOCREQIC + CSSIIVIC + RGCNIVOG + RTEREQOG + TRANIVIC DESC"
While Not rs.EOF
If rs!GroupName <> i Then
rs!BH = "Y"
rs.Update

i = rs!GroupName
End If

rs.MoveNext
Wend

What's causing this?

fds_fatboy

Why not just use an update statement and keep it all in your back end?

Anyway, there is no need to select

REGNIVIC + LOCREQIC + CSSIIVIC + RGCNIVOG + RTEREQOG + TRANIVIC AS IS41Count

as you don't appear to use it.

TriggerHappy

ASKER

I know how to fix it. The question is WHY it's doing this and WHY did it only start happening 2 days ago?

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arthur_Wood

oops, that will update ALL of the records for GroupName, so try this:

strUpdate = "UPDATE pt_OMS_IS41 set BH = "Y" where GroupName = '" & rs!GroupName & "' AND " & _
"REGNIVIC + LOCREQIC + CSSIIVIC + RGCNIVOG + RTEREQOG + TRANIVIC = " & rs!IS41Count

or you could extract the individual filed values (REGNIVIC, LOCREQIC...) in the original query, as you are not GROUPING the data, and use each field in the Where clause, to only update the spcific record that has those specifi values.

AW

Arthur_Wood

what did you change between 2 days ago and today...you MUST have changed something.

AW

TriggerHappy

ASKER

Nothing, Literally...Unless somebody did something with the SQL Server...That I don't have access to unfortunately.

Arthur_Wood

well, it would appear that if this EXACTLY SQL worked for update in the past, then the table schema was changed to remove the field IS41Count from the field definitions. That is the only thing that would lead to the error message that you are now getting.

It is a VERY bad design, by the way, to have a field in a table that is the result of a calculation on other fields in the same record of that table. This violates one of the basic design tenets of a Relational Database.

AW

TriggerHappy

ASKER

That column never has and never will exist and that's the problem.

Arthur_Wood

if that is the case, then your statement that this INSERT that is now failing, succeeded in the past CANNOT POSSIBLY BE TRUE. Sorry to be blunt, but it can't be correct.

AW

TriggerHappy

ASKER

Well...Without any changes yet again, it works again...I'm at a loss for words...

I will award the points to Arthur based on good information about not doing this sort of thing in the future.

(Just to cover my hide here, this isnt my code, i was just called in to fix it when it broke down)