Solved

Get value from datatable

Posted on 2009-05-05
9
544 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:RealSnaD
  • 5
  • 4
9 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 24306265
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24306423
Yep, that worked. Thank you. Do you happen to know how to answer my first question?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24306458
Do you mean: Show Records Added Last Month
http://www.experts-exchange.com/Q_24379474.html?
0
 
LVL 7

Author Comment

by:RealSnaD
ID: 24306464
No :) In this one, how can I add up all SUM(P.Price) values and store the result in subtotal variable?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 38

Expert Comment

by:Jim P.
ID: 24306788
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24307772
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
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 24308186
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
 
LVL 7

Author Comment

by:RealSnaD
ID: 24328407
That worked, thanks!
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24328892
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

20 Experts available now in Live!

Get 1:1 Help Now