C# sql sum to a variable

[cchacholiades]

String sQueryShow = "SELECT SUM(pf5_showplaylists.runningtime) FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID + ";";

HOW CAN I GET THE RESULT OF THIS SQL STATEMENT INTO A VARIABLE SO COULD DISPLAY THE OUTPUT IN A MESSAGE BOX? (URGENT, REALLY URGENT..)

[Momerath]

Depends on what database you are using. For SQL Server this page is the command you want, and it even has an example.

[Mitja Bonca]

If you are using Sql class do as:

SqlConnection conn = new SqlConnection("connString");
String query = "SELECT SUM(pf5_showplaylists.runningtime) FROM pf5_showplaylists WHERE pf5_showplaylists.showID = @param1";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.Add("@param1", SqlDbType.Int).Value ShowID; //MUST BE AN INTEGER!! If not 
do:
//cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value ShowID;
int mySum = Convert.ToInt32(cmd.ExecuteScalar());
//dispose iDisposable objects:
conn.Dispose();
cmd.Dispose();
MessageBox.Show("The sum is: " + mySum.ToString());

[Mitja Bonca]

To add: but its almost the same for other providers. If you use OleDb you have to change the query, and not passing parameters into it, instead of you use questionmark "... WHERE pf5_showplaylists.showID = ?"; and define parameter in parametreized query, as I showed up in my previous post.
Hope it helps.
bye

[cchacholiades]

Thank you all guys.

[Mitja Bonca]

YOu salved the problem?

[cchacholiades]

not exactly.

private void button1_Click(object sender, EventArgs e)
        {
            //Check that a show has been selected
            if (dgvShow.CurrentRow != null)
            {

            //Obtain selected show id
            int selectedShow = dgvShow.CurrentRow.Index;
            String ShowID = dgvShow.Rows[selectedShow].Cells["showID"].Value.ToString();
            
            //Select show data matching search criteria and put in data grid (SQL Statement)
            String sTotal = "SELECT SUM(pf5_showplaylists.runningtime) FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID + ";";
            
             
            dAdapterShowPlaylist = new MySqlDataAdapter(sTotal, myConn);

            dAdapterShowPlaylist.Fill(dTableShowPlaylist);

           
            myConn.Close();


            MessageBox.Show("The total runtime of the playlist is " + sTotal +".", "Total Runtime", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

    }

[cchacholiades]

the output now is in the data grid view in another column and the message box shows me the sql statement. But the number displayed in the data grid view is correct it does the sum of the "runningorder" inputs (ex. (row1)345 + (row2)345 = (result)690).

[Mitja Bonca]

What do you do with the "dTableShowPlaylist"?
there is no code that would show of any use.

[cchacholiades]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.Odbc;
using MySql.Data.MySqlClient;

namespace newproject
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        //declare variable for database connection (public static so it is available
        //to other forms generated from this form
        public static MySqlConnection myConn;

        //data table and adapter (form variables as used in multiple functions) 
        DataTable dTableShow = new DataTable();
        MySqlDataAdapter dAdapterShow;

        DataTable dTableShowPlaylist = new DataTable();
        MySqlDataAdapter dAdapterShowPlaylist;

        private void MainForm_Load(object sender, EventArgs e)
        {
            //Create and use database form to get database connection details 
            DatabaseForm databaseFrm = new DatabaseForm();
            databaseFrm.ShowDialog();

            //Create connection string for particular database (from database form)
            String connStr = DatabaseForm.sConnStr;

            //Create connection 
            myConn = new MySqlConnection(connStr);

            //Load data
            updateDataGridShow();
            updateDataGridShowPlaylist();

            //Hide showID column as only required for identifying selected show.
            dgvShow.Columns["showID"].Visible = false;

            //Set Column heading names
            //Column heading names for show
            dgvShow.Columns["ShowName"].HeaderText = "Show Name";
            dgvShow.Columns["PresenterName"].HeaderText = "Presenter Name";
            dgvShow.Columns["description"].HeaderText = "Description";

            //Column heading names for show playlists
            dgvShowPlaylist.Columns["runningorder"].HeaderText = "Running Order";
            dgvShowPlaylist.Columns["runningtime"].HeaderText = "Running Time";
            dgvShowPlaylist.Columns["songTitle"].HeaderText = "Song Title";
            dgvShowPlaylist.Columns["artist"].HeaderText = "Artist";

        }

        private void updateDataGridShow()
        {
            //Clear Current values
            dTableShow.Clear();

            //Select show data and put in data grid (SQL Statement)
            String sQueryShow = "SELECT pf5_show.showID, pf5_show.ShowName, pf5_showcategory.description, pf5_show.PresenterName FROM pf5_Show JOIN pf5_showcategory ON (pf5_show.ShowcategoryID = pf5_showcategory.ShowcategoryID) ORDER BY ShowName";

            dAdapterShow = new MySqlDataAdapter(sQueryShow, myConn);

            dAdapterShow.Fill(dTableShow);

            myConn.Close();

            dgvShow.DataSource = dTableShow;
        }

        private void updateDataGridShowPlaylist()
        {
            //Clear current values
            dTableShowPlaylist.Clear();

            //Check that a show has been selected
            if (dgvShow.CurrentRow != null)
            {
                //Obtain selected show id
                int selectedShow = dgvShow.CurrentRow.Index;
                String ShowID = dgvShow.Rows[selectedShow].Cells["showID"].Value.ToString();

                //Select show data for this playlist and put in data grid
                String sQueryShowPlaylist = "SELECT pf5_showplaylists.artist, pf5_showplaylists.songTitle, pf5_showplaylists.runningorder, pf5_showplaylists.runningTime FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID + ";";

                dAdapterShowPlaylist = new MySqlDataAdapter(sQueryShowPlaylist, myConn);

                dAdapterShowPlaylist.Fill(dTableShowPlaylist);

                myConn.Close();

                dgvShowPlaylist.DataSource = dTableShowPlaylist;
            }
        }


        private void btnSearch_Click(object sender, EventArgs e)
        {
            //Clear current values
            dTableShow.Clear();

            //Select show data matching search criteria and put in data grid (SQL Statement)
            String sQueryShow = "SELECT pf5_show.showID, pf5_show.ShowName, pf5_show.PresenterName, pf5_showcategory.description FROM pf5_show JOIN pf5_showcategory ON (pf5_show.showcategoryID = pf5_showcategory.showcategoryID) WHERE pf5_showcategory.description LIKE '%" + txtSearch.Text + "%' ORDER BY ShowName";

            dAdapterShow = new MySqlDataAdapter(sQueryShow, myConn);

            dAdapterShow.Fill(dTableShow);

            myConn.Close();

            updateDataGridShowPlaylist();
        }

        private void dgvShow_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            updateDataGridShowPlaylist();
        }

        private void dgvShow_Sorted(object sender, EventArgs e)
        {
            updateDataGridShowPlaylist();
        }

        private void btnAddShow_Click(object sender, EventArgs e)
        {
            //Create form to add new competition
            AddShowForm frm = new AddShowForm();

            //Display from 
            frm.ShowDialog();

            //Reload data into data grid 
            updateDataGridShow();
        }

        private void btnAddPlaylist_Click(object sender, EventArgs e)
        {
            //Check that a Show has been selected
            if (dgvShow.CurrentRow != null)
            {
                //Obtain selected ShowcategoryID
                int selectedShow = dgvShow.CurrentRow.Index;
                string ShowID = dgvShow.Rows[selectedShow].Cells["showID"].Value.ToString();

                //Create form to add Show category
                AddShowPlaylistForm frm = new AddShowPlaylistForm();

                //Pass required Show id to new form 
                frm.showID = ShowID;
                //Display form 
                frm.ShowDialog();

                //Reload data into data grid
                updateDataGridShowPlaylist();
            }
            else
            {
                MessageBox.Show("Can't Add description unless a show has been selected");
            }
        }

        private void aboutPiltonPopFMToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Create form to add new competition
            AboutForm frm = new AboutForm();

            //Display from 
            frm.ShowDialog();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Check that a show has been selected
            if (dgvShow.CurrentRow != null)
            {

            //Obtain selected show id
            int selectedShow = dgvShow.CurrentRow.Index;
            String ShowID = dgvShow.Rows[selectedShow].Cells["showID"].Value.ToString();
            
            //Select show data matching search criteria and put in data grid (SQL Statement)
            String sTotal = "SELECT SUM(pf5_showplaylists.runningtime) FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID + ";";
            
            dAdapterShowPlaylist = new MySqlDataAdapter(sTotal, myConn);

            dAdapterShowPlaylist.Fill(dTableShowPlaylist);

           
            myConn.Close();


            MessageBox.Show("The total runtime of the playlist is " + sTotal +".", "Total Runtime", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

    }
}
    }

[cchacholiades]

this is the whole form

[dwarvenassassin]

Why not just change

SELECT SUM(pf5_showplaylists.runningtime) FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID

to read

SELECT SUM(pf5_showplaylists.runningtime) As mySumResult FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID

and then use a DataReader ?
The result of the query may be found in the first column of the first row of the readers returned data.

// Query the DB for the number of tracks we currently have records for.
            string sql = "SELECT COUNT(*) As cnt FROM Tracks";

            OleDbDataReader reader = null;
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            // Attempt the query
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    dbRecordCount = long.Parse(reader.GetValue(0).ToString());
                }
            }

[Momerath]

and then use a DataReader ?

Using a DataReader to retrieve a single value is bad design. That's what ExecuteScaler is for.

[vimit]

Write code as:

String sQueryShow = "SELECT SUM(pf5_showplaylists.runningtime) as tot FROM pf5_showplaylists WHERE pf5_showplaylists.showID = " + ShowID + ";";
SqlCommand cmd=new SqlCommand(sQueryShow,con);
string str="";
con.Open();
if(cmd.ExecuteScalar()!=null)
str=cmd.ExecuteScalar();
con.Close();