locked
problem using SUM query while populating data RRS feed

  • Question

  • User-1779368810 posted

    So here is my problem -- I need to select a customer from a dropdown list and stock ticker from a dropdownlist. 

    When the customer dropdown list is selected the account balance is displayed -- WORKS!

    When the stock ticker is displayed the name of the stock and the price is displayed -- WORKS!!

    What I need to do is SUM the amount of shared owned by each customer of each stock in the TextBox4.  I can't seem to make the connection any where.  Here is the code.

    THANK YOU IN ADVANCE FOR ANY HELP!

    public partial class record : System.Web.UI.Page
    {
        static string CSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
         AppDomain.CurrentDomain.BaseDirectory + "foxtrade1.mdb";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateDropDowns();
            }
        }

        private void populateDropDowns()
        {
            string query  = "SELECT * FROM Customer";
            string query1 = "SELECT Ticker FROM Stocks";
            System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query, new
                    System.Data.OleDb.OleDbConnection(CSTR));
            ocmd.Connection.Open();
           
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();

            DropDownList1.Items.Clear();
            while (dr.Read())
            {
                string customerID = dr.GetInt32(0).ToString();
                string lastName = dr.GetString(4);
                string firstName = dr.GetString(2);

                DropDownList1.Items.Add(new ListItem(customerID + ":" + lastName.ToString() + "," + firstName.ToString(), customerID.ToString()));
            }
            ocmd.Connection.Close();

            System.Data.OleDb.OleDbCommand ocmd1 =
                new System.Data.OleDb.OleDbCommand(query1, new
                System.Data.OleDb.OleDbConnection(CSTR));
            ocmd1.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr1 = ocmd1.ExecuteReader();
            
            while (dr1.Read())
            {
                string ticker = dr1.GetString(0);
                DropDownList2.Items.Add(new ListItem(ticker));
            }
            ocmd1.Connection.Close();

            DropDownList1.AutoPostBack = true;
            DropDownList2.AutoPostBack = true;
        }
        //protected int getShares()
        //{
        //    string sel = DropDownList2.SelectedValue;
        //    string query = "SELECT SUM (Shares) FROM Transactions WHERE Ticker = @SelValue";

        //    System.Data.OleDb.OleDbCommand ocmd =
        //        new System.Data.OleDb.OleDbCommand(query, new System.Data.OleDb.OleDbConnection(CSTR));

        //    ocmd.Parameters.AddWithValue("@SelValue", sel);

        //    ocmd.Connection.Open();
        //    System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
        //    dr.Read();

        //    //string ticker = dr.GetString(3);
        //    int shares = dr.GetInt32(4);
        //    ocmd.Connection.Close();
        //    return shares;
            
        //}
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList1.SelectedValue);
            string query = "SELECT * FROM Customer WHERE Customer_ID=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);
            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();

            string balance = dr.GetDouble(11).ToString("c");

            ocmd.Connection.Close();

            TextBox3.Text = balance;

            ;
        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList2.SelectedValue);
            string query = "SELECT * FROM Stocks WHERE Ticker=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);

            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();
            string ticker = dr.GetString(0);
            string full = dr.GetString(1);
            string price = dr.GetDouble(2).ToString("c");
            ocmd.Connection.Close();

            TextBox1.Text = full;
            TextBox2.Text = price;
            //int shareQuantity = this.getShares();
            //TextBox4.Text = shareQuantity.ToString();
        }
    }

    Tuesday, October 26, 2010 10:50 PM

Answers

  • User892429821 posted

    To canculate the pls the specify the relationship between these tables, hope you must be maintaining sum unique id between customer and stock table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 27, 2010 2:31 AM
  • User-1779368810 posted

    Kumar,


    Thanks for the response.  You are correct -- when I select the customer from dropdown1 it displays the information correctly -- the same with stock.  I have created a method called GetShares() that I am calling in the dropdown2.selectedindex change method.  Now I am getting a "no values given for 1 or more paramters error when I pick a stock from dropdown2.  I couldn't use SELECT * because I need to SUM the shares for each customer of each stock.

    Here is the latest code. 

    public partial class record : System.Web.UI.Page
    {
        static string CSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
         AppDomain.CurrentDomain.BaseDirectory + "foxtrade1.mdb";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateDropDowns();
            }
        }

        private void populateDropDowns()
        {
            string query  = "SELECT * FROM Customer";
            string query1 = "SELECT Ticker FROM Stocks";
            System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query, new
                    System.Data.OleDb.OleDbConnection(CSTR));
            ocmd.Connection.Open();
           
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();

            DropDownList1.Items.Clear();
            while (dr.Read())
            {
                string customerID = dr.GetInt32(0).ToString();
                string lastName = dr.GetString(4);
                string firstName = dr.GetString(2);

                DropDownList1.Items.Add(new ListItem(customerID + ":" + lastName.ToString() + "," + firstName.ToString(), customerID.ToString()));
            }
            ocmd.Connection.Close();

            System.Data.OleDb.OleDbCommand ocmd1 =
                new System.Data.OleDb.OleDbCommand(query1, new
                System.Data.OleDb.OleDbConnection(CSTR));
            ocmd1.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr1 = ocmd1.ExecuteReader();
            
            while (dr1.Read())
            {
                string ticker = dr1.GetString(0);
                DropDownList2.Items.Add(new ListItem(ticker));
            }
            ocmd1.Connection.Close();

            DropDownList1.AutoPostBack = true;
            DropDownList2.AutoPostBack = true;
        }
        protected int getShares()
        {
            
            string sel = Convert.ToString(DropDownList1.SelectedValue);
            string sel2 = Convert.ToString(DropDownList2.SelectedValue);
            string query = "SELECT SUM (Shares) FROM Transactions WHERE Customer_ID = @SelValue AND Ticker = @Sel2Value";//where
                      
            System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query, new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);
            ocmd.Parameters.AddWithValue("@Sel2Value", sel2);
            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();

            string custID = dr.GetInt32(2).ToString();
            string ticker = dr.GetString(3);
            int shares = dr.GetInt32(4);
            ocmd.Connection.Close();
            return shares;
            
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList1.SelectedValue);
            string query = "SELECT * FROM Customer WHERE Customer_ID=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);
            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();

            string balance = dr.GetDouble(11).ToString("c");

            ocmd.Connection.Close();

            TextBox3.Text = balance;

            
        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList2.SelectedValue);
            string query = "SELECT * FROM Stocks WHERE Ticker=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);

            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();
            string ticker = dr.GetString(0);
            string full = dr.GetString(1);
            string price = dr.GetDouble(2).ToString("c");
            

            TextBox1.Text = full;
            TextBox2.Text = price;
            ocmd.Connection.Close();
            //int shareQuantity = this.getShares();
            //TextBox4.Text = shareQuantity.ToString();
            
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 29, 2010 9:42 AM
  • User1867929564 posted

     It appear that error is here,getShares()

    SELECT SUM (Shares) FROM Transactions WHERE Customer_ID = @SelValue AND Ticker = @Sel2Value";//


    this will return only one column.but in this function you expect it to return 3 column

    string custID = dr.GetInt32(2).ToString();
            string ticker = dr.GetString(3);
            int shares = dr.GetInt32(4);


    correct this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 30, 2010 5:29 AM

All replies

  • User892429821 posted

    To canculate the pls the specify the relationship between these tables, hope you must be maintaining sum unique id between customer and stock table.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 27, 2010 2:31 AM
  • User-1779368810 posted

    In the database file there are no specific relationships between the tables.  Here I have pasted the names of the fields so you can see them.  My problem is that I can't figure out where to put the query that calculates the sum of the shares -- AND...I can't figure out how to link the dropdown selections.  IE -- I need to link each stock ticker to each customer to determine how many shares each customer has of each stock -- right now you will see in the code a method getShares() that I have commented out. - -any help?!!


     public partial class record : System.Web.UI.Page
    {
        static string CSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
         AppDomain.CurrentDomain.BaseDirectory + "foxtrade1.mdb";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateDropDowns();
            }
        }

        private void populateDropDowns()
        {
            string query  = "SELECT * FROM Customer";
            string query1 = "SELECT Ticker FROM Stocks";
            System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query, new
                    System.Data.OleDb.OleDbConnection(CSTR));
            ocmd.Connection.Open();
           
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();

            DropDownList1.Items.Clear();
            while (dr.Read())
            {
                string customerID = dr.GetInt32(0).ToString();
                string lastName = dr.GetString(4);
                string firstName = dr.GetString(2);

                DropDownList1.Items.Add(new ListItem(customerID + ":" + lastName.ToString() + "," + firstName.ToString(), customerID.ToString()));
            }
            ocmd.Connection.Close();

            System.Data.OleDb.OleDbCommand ocmd1 =
                new System.Data.OleDb.OleDbCommand(query1, new
                System.Data.OleDb.OleDbConnection(CSTR));
            ocmd1.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr1 = ocmd1.ExecuteReader();
            
            while (dr1.Read())
            {
                string ticker = dr1.GetString(0);
                DropDownList2.Items.Add(new ListItem(ticker));
            }
            ocmd1.Connection.Close();

            DropDownList1.AutoPostBack = true;
            DropDownList2.AutoPostBack = true;
        }
        //protected int getShares()
        //{
        //    string sel = DropDownList2.SelectedValue;
        //    string query = "SELECT SUM (Shares) FROM Transactions WHERE Ticker = @SelValue";

        //    System.Data.OleDb.OleDbCommand ocmd =
        //        new System.Data.OleDb.OleDbCommand(query, new System.Data.OleDb.OleDbConnection(CSTR));

        //    ocmd.Parameters.AddWithValue("@SelValue", sel);

        //    ocmd.Connection.Open();
        //    System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
        //    dr.Read();

        //    //string ticker = dr.GetString(3);
        //    int shares = dr.GetInt32(4);
        //    ocmd.Connection.Close();
        //    return shares;
            
        //}
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList1.SelectedValue);
            string query = "SELECT * FROM Customer WHERE Customer_ID=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);
            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();

            string balance = dr.GetDouble(11).ToString("c");

            ocmd.Connection.Close();

            TextBox3.Text = balance;

            ;
        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList2.SelectedValue);
            string query = "SELECT * FROM Stocks WHERE Ticker=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);

            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();
            string ticker = dr.GetString(0);
            string full = dr.GetString(1);
            string price = dr.GetDouble(2).ToString("c");
            ocmd.Connection.Close();

            TextBox1.Text = full;
            TextBox2.Text = price;
            //int shareQuantity = this.getShares();
            //TextBox4.Text = shareQuantity.ToString();
        }
    }


    Stocks Table Metadata

    Field

    Type

    Description

    Ticker

    String

    The ticker symbol for the stock

    FullName

    String

    The full name of the company

    Price

    Double

    The current price of a share

    Exchange

    String

    The exchange on which the stock is traded

     


    Customer Table Metadata

    Field

    Type

    Description

    Customer_ID

    Long Integer

    The customer’s identification number

    C_SS

    Long Integer

    The customer’s social security number

    C_FName

    String

    The customer’s first name

    C_MName

    String

    The customer’s middle name

    C_LName

    String

    The customer’s last name

    C_Address1

    String

    First line of the customer’s address

    C_Address2

    String

    Second line of the customer’s address

    C_City

    String

    The city where the customer resides

    C_State

    String

    The state where the customer resides

    C_Zip

    Long Integer

    The zip code where the customer resides

    C_Phone

    String

    The customer’s primary telephone number

    C_AccountBalance

    Double

    The customer’s account balance (in dollars)

     

    Transactions Table Metadata

    Field

    Type

    Description

    TransactionID

    Long Integer

    The unique identifier for a transaction

    TransactionDate

    Date/Time

    The date of the transaction

    CustomerID

    Long Integer

    The customer that bought the stock

    Ticker

    String

    The stock that was purchased

    Shares

    Long Integer

    The number of shares purchased

    SalePrice

    Double

    The price of a share at the time of sale

    Wednesday, October 27, 2010 2:01 PM
  • User1867929564 posted

     Is your problem solve ?
    what is the latest problem ?

    Thursday, October 28, 2010 7:09 AM
  • User-1779368810 posted

    No solve! STUCK!

    Thursday, October 28, 2010 8:20 AM
  • User1867929564 posted

     i)you hv already populated Customer name in dropdown1
    Such that whenver I select any name I will get its customerid by dropdown1.Selectvalue

    Is this part working in this manner ?
    ii)Same with Stock.I should get ticker by dropdown2.Selected.Value

    Right ?

    iii)Any problem in i) and ii),ask.

    iv)Make another function Called StockTransaction or anything.
    Call this function in dropdwon2 selectedindexchange
    In this function write query on transaction table

    Select *  from Transactions where CustomerID=dropdwn1.Selectedvalue and ticker=dropdown2.selectedvalue

    now you do whatever you want and display.
    Any problem ask.

    Friday, October 29, 2010 4:24 AM
  • User-1779368810 posted

    Kumar,


    Thanks for the response.  You are correct -- when I select the customer from dropdown1 it displays the information correctly -- the same with stock.  I have created a method called GetShares() that I am calling in the dropdown2.selectedindex change method.  Now I am getting a "no values given for 1 or more paramters error when I pick a stock from dropdown2.  I couldn't use SELECT * because I need to SUM the shares for each customer of each stock.

    Here is the latest code. 

    public partial class record : System.Web.UI.Page
    {
        static string CSTR = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
         AppDomain.CurrentDomain.BaseDirectory + "foxtrade1.mdb";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                populateDropDowns();
            }
        }

        private void populateDropDowns()
        {
            string query  = "SELECT * FROM Customer";
            string query1 = "SELECT Ticker FROM Stocks";
            System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query, new
                    System.Data.OleDb.OleDbConnection(CSTR));
            ocmd.Connection.Open();
           
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();

            DropDownList1.Items.Clear();
            while (dr.Read())
            {
                string customerID = dr.GetInt32(0).ToString();
                string lastName = dr.GetString(4);
                string firstName = dr.GetString(2);

                DropDownList1.Items.Add(new ListItem(customerID + ":" + lastName.ToString() + "," + firstName.ToString(), customerID.ToString()));
            }
            ocmd.Connection.Close();

            System.Data.OleDb.OleDbCommand ocmd1 =
                new System.Data.OleDb.OleDbCommand(query1, new
                System.Data.OleDb.OleDbConnection(CSTR));
            ocmd1.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr1 = ocmd1.ExecuteReader();
            
            while (dr1.Read())
            {
                string ticker = dr1.GetString(0);
                DropDownList2.Items.Add(new ListItem(ticker));
            }
            ocmd1.Connection.Close();

            DropDownList1.AutoPostBack = true;
            DropDownList2.AutoPostBack = true;
        }
        protected int getShares()
        {
            
            string sel = Convert.ToString(DropDownList1.SelectedValue);
            string sel2 = Convert.ToString(DropDownList2.SelectedValue);
            string query = "SELECT SUM (Shares) FROM Transactions WHERE Customer_ID = @SelValue AND Ticker = @Sel2Value";//where
                      
            System.Data.OleDb.OleDbCommand ocmd =
                new System.Data.OleDb.OleDbCommand(query, new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);
            ocmd.Parameters.AddWithValue("@Sel2Value", sel2);
            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();

            string custID = dr.GetInt32(2).ToString();
            string ticker = dr.GetString(3);
            int shares = dr.GetInt32(4);
            ocmd.Connection.Close();
            return shares;
            
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList1.SelectedValue);
            string query = "SELECT * FROM Customer WHERE Customer_ID=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);
            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();

            string balance = dr.GetDouble(11).ToString("c");

            ocmd.Connection.Close();

            TextBox3.Text = balance;

            
        }
        protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
        {
            string sel = Convert.ToString(DropDownList2.SelectedValue);
            string query = "SELECT * FROM Stocks WHERE Ticker=@SelValue";
            System.Data.OleDb.OleDbCommand ocmd =
              new System.Data.OleDb.OleDbCommand(query,
               new System.Data.OleDb.OleDbConnection(CSTR));

            ocmd.Parameters.AddWithValue("@SelValue", sel);

            ocmd.Connection.Open();
            System.Data.OleDb.OleDbDataReader dr = ocmd.ExecuteReader();
            dr.Read();
            string ticker = dr.GetString(0);
            string full = dr.GetString(1);
            string price = dr.GetDouble(2).ToString("c");
            

            TextBox1.Text = full;
            TextBox2.Text = price;
            ocmd.Connection.Close();
            //int shareQuantity = this.getShares();
            //TextBox4.Text = shareQuantity.ToString();
            
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 29, 2010 9:42 AM
  • User1867929564 posted

     It appear that error is here,getShares()

    SELECT SUM (Shares) FROM Transactions WHERE Customer_ID = @SelValue AND Ticker = @Sel2Value";//


    this will return only one column.but in this function you expect it to return 3 column

    string custID = dr.GetInt32(2).ToString();
            string ticker = dr.GetString(3);
            int shares = dr.GetInt32(4);


    correct this.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 30, 2010 5:29 AM
  • User892429821 posted

    Hello Friend,

                  Sorry for being out for longer period from this forum. Whereas I could make out your problem is like this.

                    You have a customer table which contains customer personal details.

                   Any Transaction from customer is recorded into transaction table with transations table with customerID.

                  Now you want to select particular customer and stock from two dropdown list and want to know the sum of share price hold by

                  selected customer for select stock.

                  * First thing is sure to populate this you must have valid customer and stock selected in both the dropdownlists, As you are calling the polulate function on page load both the dropdown list will not have any value selected. (hence you will not have any parameter for selection)

                  * Run in break mode and make sure you are passing valid customer name and stock name to the populate function and check out what is your query you are passing to the database.

                  * If everything is fine, then I dont think you will have any problem in getting the sum(amount).

     

    Thanks

    SantoshSmile

    Friday, April 1, 2011 3:47 AM