CODEWITHSUNDEEP
c#mysqlwindowsforms
Yash Patel
Yash Patel

Reputation: 105

calculating average of a column in mysql database for windows form application

I am trying to compute average of a column of my mysql database and save it in a variable so I can use it for further calculation such as finding variance for normal distribution. How ever, when I run my code, it's not showing me any error but it's not reading database either. I have added checkpoints in my code to see how far it is going. Program is showing me exception message "No database selected" before checkpoint 2. Any help would be appreciated. 

decimal proteinAvg;

        string myConnection = "datasource=localhost;port=3306;username=root;password=root"
        string Query = "SELECT AVG(Protein) AS proteinAvg FROM nutritioncalculator";
        MySqlConnection myConn = new MySqlConnection(myConnection);
        MySqlCommand cmdDatabase = new MySqlCommand(Query, myConn);
        MySqlDataReader myReader;


try
{
   myConn.Open();
   //checkpoint1
   MessageBox.Show("connected");
   myReader = cmdDatabase.ExecuteReader();
   //Checkpoint2

   MessageBox.Show("connected");
   while (myReader.Read())
   {
      //checkpoint3
      MessageBox.Show("connected");
      proteinAvg = (decimal) myReader["proteinAvg"];
      MessageBox.Show("Your protein intake should be around" + proteinAvg);
   }

Upvotes: 1

Views: 872

Answers (2)

Brian
Brian

Reputation: 5119

There are a few things wrong with your code here, and I will highlight them in my answer.

decimal proteinAvg;
// MySql uses 'database' and not 'Initial Catalog' like Sql does.
string myConnection = string myConnection = "datasource=localhost;Database=mydatabase;port=3306;username=root;password=root";  // MySql uses 'database' to define the DB name.
string Query = "SELECT AVG(Protein) AS proteinAvg FROM nutritioncalculator";

// Wrap your connection and command objects in 'using' blocks.  
// Both implement IDisposable and will be managed by GC once 
// they fall out-of-scope. 
using (MySqlConnection myConn = new MySqlConnection(myConnection))
{   
   using (MySqlCommand cmdDatabase = new MySqlCommand(Query, myConn))
   {
      MySqlDataReader myReader;
      try
      {
         myConn.Open();
         //checkpoint1
         MessageBox.Show("connected");
         myReader = cmdDatabase.ExecuteReader();
         //Checkpoint2

         MessageBox.Show("connected");
         while (myReader.Read())
         {
            //Checkpoint3
            MessageBox.Show("connected");
            proteinAvg = (decimal) myReader["proteinAvg"];
            MessageBox.Show("Your protein intake should be around" + proteinAvg);
         }
      }
   }
}

Upvotes: 0

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26209

You are not specifying a database name in your ConnectionString object.

Try this:

string myConnection = "datasource=localhost;Database=mydatabase;port=3306;username=root;password=root";

Refere this Link for MySQL Connection String

Upvotes: 1

Related Questions