Solved

Get value from datatable

Posted on 2009-05-05
9
551 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

724 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