Get value from datatable

Hi,

I have the following code for retrieving records from my shopping cart table. How can I add up all SUM(P.Price) values and store the result in subtotal variable? Also, right now if a user adds the same CD more than once my statement shows price for unit and total unit price * how many times user added the same CD. Is there a way to show one more column Quantity in that GridView1 that would show the quantity? I am using Access database.

Thank you.
Dim strSQL1 As String = "SELECT P.Title, P.Artist, P.Price, SUM(P.Price) AS SubTotal FROM Products P, Cart C WHERE C.SessionID='" & _
                                    Session.SessionID & "' AND P.ProductID=C.ProductFK1 GROUP BY P.Title, P.Artist, P.Price"
        Dim subtotal, tax, total As Double
        Dim objCommand1 As New System.Data.OleDb.OleDbCommand(strSQL1, objConnection)
 
            Try
                objConnection.Open()
                Dim rdr As System.Data.OleDb.OleDbDataReader = objCommand1.ExecuteReader
                Dim dt As System.Data.DataTable = New System.Data.DataTable
                dt.Load(rdr)
 
                objConnection.Close()
 
                GridView1.DataSource = dt
                GridView1.DataBind()
 
                dt.Dispose()
            Catch ex As Exception
                MsgBox("Connection failed to open. Error: " & ex.ToString())
            End Try

Open in new window

LVL 7
Yury MerezhkovDevelopment Team LeadAsked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
Try this:
Dim strSQL1 As String = "SELECT SUM(SubTotal) As OrderSubtotal, Sum(Qty) As OrderQTY FROM " _
             "(SELECT  CDbl(Count(P.ProductID)) * Sum(P.Price) AS SubTotal, Count(P.ProductID) As Qty FROM Products P, Cart C WHERE C.SessionID='" & _
                                    Session.SessionID & "' AND P.ProductID=C.ProductFK1 GROUP BY ProductID)"

Open in new window

0
 
Jim P.Commented:
Should just be a count of ProductID. Try this:
Dim strSQL1 As String = "SELECT P.Title, P.Artist, P.Price, SUM(P.Price) AS SubTotal, Count(P.ProductID) As Qty FROM Products P, Cart C WHERE C.SessionID='" & _
                                    Session.SessionID & "' AND P.ProductID=C.ProductFK1 GROUP BY P.Title, P.Artist, P.Price"
        Dim subtotal, tax, total As Double
        Dim objCommand1 As New System.Data.OleDb.OleDbCommand(strSQL1, objConnection)
 
            Try
                objConnection.Open()
                Dim rdr As System.Data.OleDb.OleDbDataReader = objCommand1.ExecuteReader
                Dim dt As System.Data.DataTable = New System.Data.DataTable
                dt.Load(rdr)
 
                objConnection.Close()
 
                GridView1.DataSource = dt
                GridView1.DataBind()
 
                dt.Dispose()
            Catch ex As Exception
                MsgBox("Connection failed to open. Error: " & ex.ToString())
            End Try

Open in new window

0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
Yep, that worked. Thank you. Do you happen to know how to answer my first question?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim P.Commented:
Do you mean: Show Records Added Last Month
http://www.experts-exchange.com/Q_24379474.html?
0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
No :) In this one, how can I add up all SUM(P.Price) values and store the result in subtotal variable?
0
 
Jim P.Commented:
I'm not really an ASP guy but the way I would think would work without running through the whole recordset and adding -- a query on a query for the whole cart, and to get the subtotal right the first
Dim strSQL1 As String = "SELECT P.Title, P.Artist, P.Price, CDbl(Count(P.ProductID)) * (P.Price) AS SubTotal, Count(P.ProductID) As Qty FROM Products P, Cart C WHERE C.SessionID='" & _
                                    Session.SessionID & "' AND P.ProductID=C.ProductFK1 GROUP BY P.Title, P.Artist, P.Price"
 
 
Dim strSQL1 As String = "SELECT SUM(SubTotal) As OrderSubtotal, Sum(Qty) As OrderQTY FROM " _
             "(SELECT  CDbl(Count(P.ProductID)) * (P.Price) AS SubTotal, Count(P.ProductID) As Qty FROM Products P, Cart C WHERE C.SessionID='" & _
                                    Session.SessionID & "' AND P.ProductID=C.ProductFK1 GROUP BY ProductID)"

Open in new window

0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
I tried your second statement and it gives me an error: You tried to execute a query that does not include the specified expression 'CDbl(Count(P.ProductID)) * (P.Price)' as a part of an aggregate function. Could you please see if you can fix it because I really like sql approach more than recordsets.
0
 
Yury MerezhkovDevelopment Team LeadAuthor Commented:
That worked, thanks!
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
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.