Solved

Get value from datatable

Posted on 2009-05-05
9
548 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

829 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