Friends,
I need your help, please.
I want to Count 'public schools' in each State(which is a column name) across all 50 States in USA(currently having 50 to 100 rows in sql server table named "tbl_PublicSchoolUSA") and display in 50 TextBox for each State using C# asp Dot Net Sql Server 2008
SELECT States, COUNT(States) AS Total_Schools
FROM tbl_PublicSchoolUSA
GROUP BY States
HAVING (COUNT(States) >= 1)
After all these days of "hard work(?)" had led nowhere.
Please help.
Thanks,
Ravi
What I have tried:
I tried the above SQL statement in Sql server 2008 - It works well - but, how can I display each value in each TextBox in ASP.Net.
This is my C# code
private void DisplayInTextBox()
{
string constring = (@"Data Source=.\....bla...bla..bla");
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT States, COUNT(States) AS Total_Schools FROM tbl_PublicSchoolUSA GROUP BY States HAVING (COUNT(States) >= 1)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
TextBox1.Text = sdr["Total_Schools"].ToString();////Total schools in California
TextBox2.Text = sdr["Some Alias Name"].ToString(); ///Total schools in New York
TextBox3.Text = sdr["Some Alias Name"].ToString(); ///Total schools in Ohio
TextBox4.Text = sdr["Some Alias Name"].ToString(); ///Total schools in Illinois
...............
...........
.........
}
con.Close();
}
}
}