Jump to content

sum dynamic table column in ms access with vb.net


rony_78
 Share

Recommended Posts

Can somene help me with this I'm having difficulty figuring out how to sum column values in dynamic tables. I am using create table statement passing text box input to the query with a column name 'total'. Using another insert query i am saving tables name in another table called table_list. I am looking for a way to get the sum of column 'total' of all created tables .

Link to comment
Share on other sites

can you show me any sample code or query . I have tried to query the system tables such as
MSysAccessObjects
MSysAccessXML
MSysACEs
MSysObjects
MSysQueries
public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }               private void button2_Click(object sender, EventArgs e)        {                }         private static int getTotal(string connectionString)        {            int total = 0;            using (var connection = new OleDbConnection(""))            {                connection.Open();                var command = new OleDbCommand("SELECT TABLE_NAME FROM MSysAccessObjects.COLUMNS WHERE COLUMN_NAME = 'Total';"                    , connection);                foreach (var table in from IDataRecord row in command.ExecuteReader()                                      select new { name = row.GetValue(row.GetOrdinal("TABLE_NAME")) }                    )                {                    command.CommandText = String.Format("SELECT SUM(Total) AS Total FROM {0}", table.name);                    var tableTotal = command.ExecuteScalar();                    total = total + (tableTotal == DBNull.Value ? 0 : (int)command.ExecuteScalar());                }                connection.Close();            }            return total;        }

I have found a function similer to my problem but I dont know if it will work.
stackoverflow.com/.../how-sum-values-in-dynamic-table-columns-
function sumQty(tableID) {    var total = 0;    var table = document.getElementById(tableID);    var rowCount = table.rows.length;    for (var i = 0; i < rowCount; i++) {        var row = table.rows[i];        var colCount = row.cells.length;        for (var j = 0; j < cellCount; j++) {            var node = row.cells[j].childNodes[0];            if (node.name == "qty[]") {                total += parseInt(node.value);            }        }    }    return total;}
I also tried the the query as a single statement
 CmdStr1 = "SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS Tot FROM ' + TABLE_NAME FROM MSysObjects .COLUMNS WHERE COLUMN_NAME = 'Total'"

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...