Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

SQL Calculation Error

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
GMMC_man
Asked:
GMMC_man
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
seems like the table 'Dreams' doesnt contain the column 'DreamID'. it may be mispelled
0
 
RiteshShahCommented:
yes, DremaID is either mispelled or not exists in table "Dream", can you double check it once?
0
 
GMMC_manAuthor Commented:
I've checked and checked. dreamID is how the field is spelled. It is an auto increment id integer.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
GMMC_manAuthor Commented:
Now getting error - Invalid column name 'havingdreams'.
0
 
RiteshShahCommented:
can you show me your table structure? do you have "havingdreams' column?
0
 
GMMC_manAuthor Commented:
no havingdreams is created in sql query
0
 
RiteshShahCommented:
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
 
GMMC_manAuthor Commented:
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
 
RiteshShahCommented:
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
 
satheeshmCommented:
Hi,

Can you please post your table structure.

Thanks,
Satheesh M
0
 
dzex13Commented:
--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
 
GMMC_manAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now