Solved

Get value from datatable

Posted on 2009-05-05
9
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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/…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

763 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