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

GMMC_manAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RiteshShahConnect With a Mentor Commented:
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GMMC_manAuthor Commented:
I've checked and checked. dreamID is how the field is spelled. It is an auto increment id integer.
0
 
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
 
satheeshmCommented:
Hi,

Can you please post your table structure.

Thanks,
Satheesh M
0
 
dzex13Connect With a Mentor Commented:
--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
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.

All Courses

From novice to tech pro — start learning today.