How to display/ fire data (sum column values) from SQL Server database in C# WinForm textBoxes

Question

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.

This how my table in the database looks like:

enter image description here

My C# code:

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:

enter image description here

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

Hope I did express myself well

when you run the query manually what do the results look like.. also you do not need ( ) in this line sqlCmd.CommandText = ("[dbo].[spSalesAnalyse]"); it should just be ` sqlCmd.CommandText ="spSalesAnalyse";` for starters.. have you even stepped through the code.. — MethodMan
– MethodMan, Commented Apr 28, 2015 at 18:54

Praveen Paulose · Accepted Answer · 2015-04-28 19:09:18Z

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.

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.

//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.

DataRow dataRow = dataset.Tables[0].Select("Country = 'Italy' AND Region = 'North'").FirstOrDefault();
if (dataRow != null) {
    textbox1.Text = dataRow["First Sales"].ToString();
}

edited Apr 28, 2015 at 19:09

answered Apr 28, 2015 at 19:02

Praveen Paulose

5,7711 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mikybrain Over a year ago

Thnx dude but can you give me an example how? I mean with the dataset :)

mikybrain Over a year ago

@ Praveen Unfortunately no. I've edited my c# code. Can u please take a look and help?

Mario The Spoon · Accepted Answer · 2015-04-28 19:02:11Z

0

Sounds like a case for GROUP BY

Edited to answer the OPs remark

SELECT region, country
        , SUM (  sales1 ) as [First Sales]
        , SUM ( sales2 ) as [Second Sales]
        , SUM ( sales3 ) as [Third Sales]
 FROM tblSales 
 GROUP BY country, region

edited Apr 28, 2015 at 19:02

answered Apr 28, 2015 at 18:55

Mario The Spoon

4,8591 gold badge28 silver badges36 bronze badges

2 Comments

mikybrain Over a year ago

But I've three Sales columns and as such how do I set them to the textBoxes? Will be nice if you can edit my sp query so that I get a clue

Mario The Spoon Over a year ago

I changed it accordingly

Collectives™ on Stack Overflow

How to display/ fire data (sum column values) from SQL Server database in C# WinForm textBoxes

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related