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?
LVL 9
TriggerHappyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fds_fatboyCommented:
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.
0
TriggerHappyAuthor Commented:
I know how to fix it.  The question is WHY it's doing this and WHY did it only start happening 2 days ago?
0
Arthur_WoodCommented:
why it is not working is simple, you should NOT be using the same recordset to BOTH retrieve the data, and then update the underlying table.  In this case, you are creating a calculated field in the original SQL, and then trying to update THAT recordset back to the underlying table, where that calcualted field does not exist.

You would be much better to read the data using the SQL as you currently have it, but then use an UPDATE query.

Create a Command Object, and then set the CommandText to:

strUpdate = "UPDATE pt_OMS_IS41 set BH = "Y" where GroupName = '" & rs!GroupName & "'"

and use the Command.Execute method to execute the SQL, to update the underlying table

AW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
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


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

AW
0
TriggerHappyAuthor Commented:
Nothing, Literally...Unless somebody did something with the SQL Server...That I don't have access to unfortunately.
0
Arthur_WoodCommented:
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
0
TriggerHappyAuthor Commented:
That column never has and never will exist and that's the problem.  
0
Arthur_WoodCommented:
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
0
TriggerHappyAuthor Commented:
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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.