how to work average with value from sql in c sharp

[stephen04]

dear I.T. lovers

I'm in need of assistance ,currently I'm working an Questionnaire solution project that consists inputting results of a questionnaire(saved in sql database) and then an average of certain questions is given.

I'm having trouble when i need to work the average of the questions

one of my question is there are seven text boxes that cover ages from 0 till 60 that the user has to input in the text boxes
the amount of people that are in a certain group of age.(sort of like below)

0-10 [text box]<--enter amount of people that the user has in the family of that group
11-20 [text box]
21-30 [text box]
31-40 [text box]
41-50 [text box]

the code that i have worked till now is the one below.i think the trouble is where i placed the arrows

//Connection string
SqlConnection MyConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|dbQuestionaireSolution.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
 
 
          string av1 = "SELECT[0-10]FROM Quest3 WHERE (Region = 'NorthernDistrict')";
 
          SqlCommand MysqlCommand =new SqlCommand(av1, MyConnection);
            MyConnection.Open();
            SqlDataReader reader = MysqlCommand.ExecuteReader();
 
            if (reader.Read())
            {
              int ND1 = Int32.Parse(av01); <-------
             // this.textBox1ND.Text = reader["0-10"].ToString();<------
              //int ND1 = int.Parse(textBox1ND.Text);<-----
 
              for (int i = ND1; i == null; i++)
              {
 
               int total = ((i + i)/int.MaxValue);
               total = Convert.ToInt32(textBox1ND.Text);
 
              }
 

pls in urgent need of help thanx a lot

[Rabbit]

Why are you parsing the SQL string? That doesn't make any sense.

If you're trying to get an average, why don't you just use SQL?

SELECT AVG([FieldName]) FROM Table1

[stephen04]

how do i display the value in a textbox

thanx

[Rabbit]

You would display it how you currently display it. You would just be grabbing the single value instead of trying to step through a recordset and calculating the average.

[stephen04]

can you please show me a syntax example on how it should be

[Rabbit]

You already have an example in your code.

this.textBox1ND.Text=reader["0-10"].ToString

The only thing you would have to change is the field name to the alias you give the count.

[stephen04]

when you said"the field name to the alias you give the count"

the field name is this part ------"0-10"
WHAT is did you ment exactly by"the alias you give the count" (WHAT IS THE COUNT)

[Rabbit]

Mistype, I meant average.

[stephen04]

This is the code how i made it that came without errors

 
SqlConnection MyConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Rebecca\Desktop\Project\dbQuestionaireSolution.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
                string av1 = "SELECT AVG([0-10])FROM Quest3 ";
                SqlCommand MysqlCommand =new SqlCommand(av1, MyConnection);
                    MyConnection.Open();
                    SqlDataReader reader = MysqlCommand.ExecuteReader();
                     if (reader.Read())
                    {
                        //this.textBox1ND.Text = reader["0 - 10"].ToString();
                     }
 

The comented part is the only part that is still giving me the error below
“0 – 10 IndexOutOfRangeExeption was unhendled”
What can i do now for the final push??

Thanx

[Rabbit]

You just need to give the average an alias and use the alias instead of 0-10.

[stephen04]

i m not geting the concept of the alias can you give me a syntax example? because i'm not geting the hang of it

[Rabbit]

Something like Avg(somefield) As AvgOfSomefield.

[stephen04]

do i have to create the AvgOfSomefield as a variable ? i think i'm not doing it correct

[Ajay Dudhat]

Try this, u might come to know use of Alias...Alias is custom name given to Column or SQL table...Have look, u will understand...

# /Connection string
# SqlConnection MyConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|dbQuestionaireSolution.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
#
#
# string av1 = "SELECT AVERAGE([0-10]) AS AverageAge FROM Quest3 WHERE (Region = 'NorthernDistrict')";
#
# SqlCommand MysqlCommand =new SqlCommand(av1, MyConnection);
# MyConnection.Open();
# SqlDataReader reader = MysqlCommand.ExecuteReader();
#
# if (reader.Read())
# {
this.textBox1ND.Text=reader["AverageAge"].ToString();
#
# }
#

Hope this will solve ur problems

[stephen04]

it
worked perfectly thanx for your help and patience