Solved

SQL Calculation Error

Posted on 2009-05-09
14
324 Views
Last Modified: 2012-05-06
Hi Experts,

My sql calculation keeps giving me an error.

Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'dreamID'.

/gmmcPub/dreamsReview.asp, line 62

I'm trying to figure out why I'm getting this error.
<%

Dim rsDreamsCalculate__MMColParam

rsDreamsCalculate__MMColParam = "1"

If (Session("MM_userID") <> "") Then 

  rsDreamsCalculate__MMColParam = Session("MM_userID")

End If

%>

<%

Dim rsDreamsCalculate

Dim rsDreamsCalculate_cmd

Dim rsDreamsCalculate_numRows
 

Set rsDreamsCalculate_cmd = Server.CreateObject ("ADODB.Command")

rsDreamsCalculate_cmd.ActiveConnection = MM_connGMMCdata_STRING

rsDreamsCalculate_cmd.CommandText = "SELECT dreamID, memberID, dreamDate, InHowManyMonths, HavingDream1, HavingDream2, HavingDream3,  HavingDream4, HavingDream5, BeingDream1, BeingDream1ToDo, BeingDream2, BeingDream2ToDo, BeingDream3, BeingDream3ToDo, BeingDream4, BeingDream4ToDo, BeingDream5, BeingDream5ToDo, DoingDream1, DoingDream2, DoingDream3, DoingDream4, DoingDream5, StepsNow, Tomorrow, NextDay, TMI, TDI, HavingDreamTotalCost, BeingDreamTotalCost, DoingDreamTotalCost, TotalDreamCost, COALESCE(HavingDream1Cost, 0) + COALESCE(HavingDream2Cost, 0) + COALESCE(HavingDream3Cost, 0) + COALESCE(HavingDream4Cost, 0) + COALESCE(HavingDream5Cost, 0)  as HavingDreams , COALESCE(BeingDream1Cost, 0) + COALESCE(BeingDream2Cost, 0) + COALESCE(BeingDream3Cost, 0) + COALESCE(BeingDream4Cost, 0) + COALESCE(BeingDream5Cost, 0) as BeingDreams , COALESCE(DoingDream1Cost, 0) + COALESCE(DoingDream2Cost, 0) +  COALESCE(DoingDream3Cost, 0) + COALESCE(DoingDream4Cost, 0) + COALESCE(DoingDream5Cost, 0) as DoingDreams --grandtotal=havingdreams+beingdreams+doingdreams FROM dbo.dreams WHERE memberID = ?" 

rsDreamsCalculate_cmd.Prepared = true

rsDreamsCalculate_cmd.Parameters.Append rsDreamsCalculate_cmd.CreateParameter("param1", 3, 1, -1, rsDreamsCalculate__MMColParam) ' adInteger
 

Set rsDreamsCalculate = rsDreamsCalculate_cmd.Execute

rsDreamsCalculate_numRows = 0

%>

Open in new window

0
Comment
Question by:GMMC_man
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
seems like the table 'Dreams' doesnt contain the column 'DreamID'. it may be mispelled
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
yes, DremaID is either mispelled or not exists in table "Dream", can you double check it once?
0
 

Author Comment

by:GMMC_man
Comment Utility
I've checked and checked. dreamID is how the field is spelled. It is an auto increment id integer.
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
hhmmmm, I gotcha....... try below in your code.
"SELECT dreamID, memberID, dreamDate, InHowManyMonths, HavingDream1, HavingDream2, HavingDream3,  HavingDream4, HavingDream5, BeingDream1, BeingDream1ToDo, BeingDream2, BeingDream2ToDo, BeingDream3, BeingDream3ToDo, BeingDream4, BeingDream4ToDo, BeingDream5, BeingDream5ToDo, DoingDream1, DoingDream2, DoingDream3, DoingDream4, DoingDream5, StepsNow, Tomorrow, NextDay, TMI, TDI, HavingDreamTotalCost, BeingDreamTotalCost, DoingDreamTotalCost, TotalDreamCost, COALESCE(HavingDream1Cost, 0) + COALESCE(HavingDream2Cost, 0) + COALESCE(HavingDream3Cost, 0) + COALESCE(HavingDream4Cost, 0) + COALESCE(HavingDream5Cost, 0)  as HavingDreams , COALESCE(BeingDream1Cost, 0) + COALESCE(BeingDream2Cost, 0) + COALESCE(BeingDream3Cost, 0) + COALESCE(BeingDream4Cost, 0) + COALESCE(BeingDream5Cost, 0) as BeingDreams , COALESCE(DoingDream1Cost, 0) + COALESCE(DoingDream2Cost, 0) +  COALESCE(DoingDream3Cost, 0) + COALESCE(DoingDream4Cost, 0) + COALESCE(DoingDream5Cost, 0) as DoingDreams --grandtotal=havingdreams+beingdreams+doingdreams FROM dbo.dreams WHERE memberID = ?" 

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
sorry, use this one.  typo in above.
"SELECT dreamID, memberID, dreamDate, InHowManyMonths, HavingDream1, HavingDream2, HavingDream3,  HavingDream4, HavingDream5, BeingDream1, BeingDream1ToDo, BeingDream2, BeingDream2ToDo, BeingDream3, BeingDream3ToDo, BeingDream4, BeingDream4ToDo, BeingDream5, BeingDream5ToDo, DoingDream1, DoingDream2, DoingDream3, DoingDream4, DoingDream5, StepsNow, Tomorrow, NextDay, TMI, TDI, HavingDreamTotalCost, BeingDreamTotalCost, DoingDreamTotalCost, TotalDreamCost, COALESCE(HavingDream1Cost, 0) + COALESCE(HavingDream2Cost, 0) + COALESCE(HavingDream3Cost, 0) + COALESCE(HavingDream4Cost, 0) + COALESCE(HavingDream5Cost, 0)  as HavingDreams , COALESCE(BeingDream1Cost, 0) + COALESCE(BeingDream2Cost, 0) + COALESCE(BeingDream3Cost, 0) + COALESCE(BeingDream4Cost, 0) + COALESCE(BeingDream5Cost, 0) as BeingDreams , COALESCE(DoingDream1Cost, 0) + COALESCE(DoingDream2Cost, 0) +  COALESCE(DoingDream3Cost, 0) + COALESCE(DoingDream4Cost, 0) + COALESCE(DoingDream5Cost, 0) as DoingDreams, grandtotal=havingdreams+beingdreams+doingdreams FROM dbo.dreams WHERE memberID = ?" 

Open in new window

0
 

Author Comment

by:GMMC_man
Comment Utility
Now getting error - Invalid column name 'havingdreams'.
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
can you show me your table structure? do you have "havingdreams' column?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:GMMC_man
Comment Utility
no havingdreams is created in sql query
0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
try this.
SELECT 

dreamID, 

memberID, 

dreamDate, 

InHowManyMonths, 

HavingDream1, 

HavingDream2, 

HavingDream3,  

HavingDream4, 

HavingDream5, 

BeingDream1, 

BeingDream1ToDo, 

BeingDream2, 

BeingDream2ToDo, 

BeingDream3, 

BeingDream3ToDo, 

BeingDream4, 

BeingDream4ToDo, 

BeingDream5, 

BeingDream5ToDo, 

DoingDream1, 

DoingDream2, 

DoingDream3, 

DoingDream4, 

DoingDream5, 

StepsNow, 

Tomorrow, 

NextDay, 

TMI, 

TDI, 

COALESCE(HavingDream1 , 0) + COALESCE(HavingDream2 , 0) + COALESCE(HavingDream3 , 0) + COALESCE(HavingDream4 , 0) + COALESCE(HavingDream5 , 0)  as HavingDreamsTotal , 
 

COALESCE(BeingDream1 , 0) + COALESCE(BeingDream2 , 0) + COALESCE(BeingDream3 , 0) + COALESCE(BeingDream4 , 0) + COALESCE(BeingDream5 , 0) as BeingDreamsTotal , 
 

COALESCE(DoingDream1 , 0) + COALESCE(DoingDream2 , 0) +  COALESCE(DoingDream3 , 0) + COALESCE(DoingDream4 , 0) + COALESCE(DoingDream5 , 0) as DoingDreamsTotal,
 

grandtotal=HavingDreamsTotal+BeingDreamsTotal+DoingDreamsTotal FROM dbo.dreams WHERE memberID = ?

Open in new window

0
 

Author Comment

by:GMMC_man
Comment Utility
Still getting error
Invalid column name 'HavingDreamsTotal'.

I removed grandtotal line and got this error
Microsoft OLE DB Provider for SQL Server error '80040e07'

Conversion failed when converting the varchar value 'A fully functional church' to data type int.

/gmmcPub/dreamsReview.asp, line 64
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 400 total points
Comment Utility
in your original logic, you were trying to total something but you might not have numeric value in the fields you are making total rather it has value in string like 'A gully functional church' so it can't be use with addition or subtraction etc. have a look at this.
SELECT 

dreamID, 

memberID, 

dreamDate, 

InHowManyMonths, 

HavingDream1, 

HavingDream2, 

HavingDream3,  

HavingDream4, 

HavingDream5, 

BeingDream1, 

BeingDream1ToDo, 

BeingDream2, 

BeingDream2ToDo, 

BeingDream3, 

BeingDream3ToDo, 

BeingDream4, 

BeingDream4ToDo, 

BeingDream5, 

BeingDream5ToDo, 

DoingDream1, 

DoingDream2, 

DoingDream3, 

DoingDream4, 

DoingDream5, 

StepsNow, 

Tomorrow, 

NextDay, 

TMI, 

TDI 

FROM dbo.dreams WHERE memberID = ?

Open in new window

0
 
LVL 5

Expert Comment

by:satheeshm
Comment Utility
Hi,

Can you please post your table structure.

Thanks,
Satheesh M
0
 
LVL 2

Assisted Solution

by:dzex13
dzex13 earned 100 total points
Comment Utility
--grandtotal=havingdreams+beingdreams+doingdreams

Replace This string by:

COALESCE(HavingDream1 , 0) + COALESCE(HavingDream2 , 0) + COALESCE(HavingDream3 , 0) + COALESCE(HavingDream4 , 0) + COALESCE(HavingDream5 , 0)  +
COALESCE(BeingDream1 , 0) + COALESCE(BeingDream2 , 0) + COALESCE(BeingDream3 , 0) + COALESCE(BeingDream4 , 0) + COALESCE(BeingDream5 , 0) +
COALESCE(DoingDream1 , 0) + COALESCE(DoingDream2 , 0) +  COALESCE(DoingDream3 , 0) + COALESCE(DoingDream4 , 0) + COALESCE(DoingDream5 , 0) as GrandTotal

0
 

Author Comment

by:GMMC_man
Comment Utility
That worked - Thanks.

Here's the final code. I was calculating text fields which was throwing an error. After adding Cost to each dream it worked fine. Thanks.

SELECT dreamID, memberID, dreamDate, InHowManyMonths, HavingDream1, HavingDream2, HavingDream3,  HavingDream4, HavingDream5, BeingDream1, BeingDream1ToDo, BeingDream2, BeingDream2ToDo, BeingDream3, BeingDream3ToDo, BeingDream4, BeingDream4ToDo, BeingDream5, BeingDream5ToDo, DoingDream1, DoingDream2, DoingDream3, DoingDream4, DoingDream5, StepsNow, Tomorrow, NextDay, TMI, TDI, COALESCE(HavingDream1Cost , 0) + COALESCE(HavingDream2Cost , 0) + COALESCE(HavingDream3Cost , 0) + COALESCE(HavingDream4Cost , 0) + COALESCE(HavingDream5Cost , 0)  as HavingDreamsTotal , COALESCE(BeingDream1Cost , 0) + COALESCE(BeingDream2Cost , 0) + COALESCE(BeingDream3Cost , 0) + COALESCE(BeingDream4Cost , 0) + COALESCE(BeingDream5Cost , 0) as BeingDreamsTotal , COALESCE(DoingDream1Cost , 0) + COALESCE(DoingDream2Cost , 0) +  COALESCE(DoingDream3Cost , 0) + COALESCE(DoingDream4Cost , 0) + COALESCE(DoingDream5Cost , 0) as DoingDreamsTotal, COALESCE(HavingDream1Cost , 0) + COALESCE(HavingDream2Cost , 0) + COALESCE(HavingDream3Cost , 0) + COALESCE(HavingDream4Cost , 0) + COALESCE(HavingDream5Cost , 0)  + COALESCE(BeingDream1Cost , 0) + COALESCE(BeingDream2Cost , 0) + COALESCE(BeingDream3Cost , 0) + COALESCE(BeingDream4Cost , 0) + COALESCE(BeingDream5Cost , 0) + COALESCE(DoingDream1Cost , 0) + COALESCE(DoingDream2Cost , 0) +  COALESCE(DoingDream3Cost , 0) + COALESCE(DoingDream4Cost , 0) + COALESCE(DoingDream5Cost , 0) as GrandTotal FROM dbo.dreams WHERE memberID = ?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now