I'm trying to display the sum up certain columns from my SQL Server database into Winforms textboxes
but I'm facing difficulties and I don't know further.
我正在尝试将我的SQL Server数据库中的某些列的总和显示为Winforms文本框,但我遇到了困难,我不知道进一步。
This how my table in the database
looks like:
这是我在数据库中的表格如下:
My C# code:
我的C#代码:
if (combobox1.SelectedText != null)
{
string CS = (@"Data )
SqlConnection con = new SqlConnection(CS);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.CommandText = ("[dbo].[spSalesAnalyse]");
sqlCmd.CommandType = CommandType.StoredProcedure;
cbAnalyse.SelectedValue.ToString());
SqlDataReader myReader;
try
{
con.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
// Save the results in the DT. Call the adapter statement and fill it in the DT
DataTable dt = new DataTable();
adapter.Fill(dt);
//Fill textBoxes with data in DT
textBox1.Text = dt.Rows[0].Field<string>("North");
textBox2.Text = dt.Rows[1].Field<string>("East");
textBox3.Text = dt.Rows[2].Field<string>("West");
....
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
else
MessageBox.Show("Error");
My expectation:
The Problem: If I debugg till this line adapter.Fill(dt)
, I do see the records BUT they are not getting to my textBoxes. They are empty when debugg reach textBoxes level
问题:如果我调试直到这行适配器。填充(dt),我确实看到了记录,但他们没有到达我的textBoxes。当debugg达到textBoxes级别时,它们是空的
Hope I did express myself well
希望我表达得很好
2 个解决方案
#1
1
Looks like you are trying to get one textbox value at a time. You could use the GROUP BY
clause in SQL to fetch your data.
看起来您正在尝试一次获取一个文本框值。您可以使用SQL中的GROUP BY子句来获取数据。
SELECT
Country,
Region,
SUM([Sales1]) AS [First Sales],
SUM([Sales2]) AS [Sec Sales],
SUM([Sales3]) AS [Third Sales]
FROM
[dbo].[tblSales]
GROUP BY Country, Region
A single query will return all your results. You can take this results in a DataSet
and then set the values of the corresponding textboxes by fetching values from the DataSet.
单个查询将返回您的所有结果。您可以在DataSet中获取此结果,然后通过从DataSet中获取值来设置相应文本框的值。
//Use query string as show above
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Now that you have records inside your DataSet, you can fill your textboxes.
现在您的DataSet中有记录,您可以填充文本框。
DataRow dataRow = dataset.Tables[0].Select("Country = 'Italy' AND Region = 'North'").FirstOrDefault();
if (dataRow != null) {
textbox1.Text = dataRow["First Sales"].ToString();
}
#2
0
Sounds like a case for GROUP BY
听起来像GROUP BY的情况
Edited to answer the OPs remark
编辑回答了OP的评论
SELECT region, country
, SUM ( sales1 ) as [First Sales]
, SUM ( sales2 ) as [Second Sales]
, SUM ( sales3 ) as [Third Sales]
FROM tblSales
GROUP BY country, region
#1
1
Looks like you are trying to get one textbox value at a time. You could use the GROUP BY
clause in SQL to fetch your data.
看起来您正在尝试一次获取一个文本框值。您可以使用SQL中的GROUP BY子句来获取数据。
SELECT
Country,
Region,
SUM([Sales1]) AS [First Sales],
SUM([Sales2]) AS [Sec Sales],
SUM([Sales3]) AS [Third Sales]
FROM
[dbo].[tblSales]
GROUP BY Country, Region
A single query will return all your results. You can take this results in a DataSet
and then set the values of the corresponding textboxes by fetching values from the DataSet.
单个查询将返回您的所有结果。您可以在DataSet中获取此结果,然后通过从DataSet中获取值来设置相应文本框的值。
//Use query string as show above
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Now that you have records inside your DataSet, you can fill your textboxes.
现在您的DataSet中有记录,您可以填充文本框。
DataRow dataRow = dataset.Tables[0].Select("Country = 'Italy' AND Region = 'North'").FirstOrDefault();
if (dataRow != null) {
textbox1.Text = dataRow["First Sales"].ToString();
}
#2
0
Sounds like a case for GROUP BY
听起来像GROUP BY的情况
Edited to answer the OPs remark
编辑回答了OP的评论
SELECT region, country
, SUM ( sales1 ) as [First Sales]
, SUM ( sales2 ) as [Second Sales]
, SUM ( sales3 ) as [Third Sales]
FROM tblSales
GROUP BY country, region