Solved

Get value from datatable

Posted on 2009-05-05
9
543 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
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.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 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

12 Experts available now in Live!

Get 1:1 Help Now