How to fetch records(sum column values) from SQL Server database in WinForm textBoxes

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I'm trying to display the sum up certain columns from my SQL Server database into Winforms textboxes.
My Database results: [^]

And this is how my winForms 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");

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

My Expectation:[^]

Posted 29-Apr-15 23:02pm

mikybrain1

Add a Solution

Comments

Karthik_Mahalingam 30-Apr-15 5:21am

make sure that
textBox2.Text = dt.Rows[1].Field<string>("East");
textBox3.Text = dt.Rows[2].Field<string>("West");
these lines are pointing to Row 1

Maciej Los 30-Apr-15 5:37am

Miky, this piece of code and images are useless. Please, be more specific and provide more details about your issue.
What have you tried? What's wrong with your code? Have you debug it?

mikybrain1 30-Apr-15 6:16am

@ Maciej Los. Yes I've debug it and everything seems to be okay at this level adapter.Fill(dt);[The records are being fired into dt] BUT not getting into my textBoxes.

New screenshot: http://x53i.imgup.net/167327.PNG

Maciej Los 30-Apr-15 6:22am

OK, try this:
dt.Rows[0]["North"] instead of dt.Rows[0].Field<string>("North")

mikybrain1 30-Apr-15 6:44am

unfortunately no. It says identifier expected. So i put string infront but no

mikybrain1 30-Apr-15 9:00am

@Maciej Los. I'm now getting the values in the first textBox if I debug but not the first value. I' m getting the last value. Is there a way to code like: Get the values from first row and first cell?

That's my edited code:

SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
foreach(DataRow row in ds.Tables[0].Rows)
{
textBox1.Text = row["North"].ToString();
//textBox1.Text = ds.Rows[0].ItemArray[0].ToString();

}

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11268276 · Answer 1 · 2015-04-29T23:40:00

Please try below code and let me know for any issues:

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

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.

C#

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

C#

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

Maciej Los · Answer 2 · 2015-04-30T03:34:00

Solution 2

Miky, you mixed everything!

Why to force doors wide open? Use proper tools, such as ReportViewer[^]. It enables grouping, sorting, etc.[^].

Posted 30-Apr-15 3:34am

Maciej Los

Comments

mikybrain1 30-Apr-15 9:41am

Thnx anyway for the tips. I think I'm getting it now :). The foreach loop is working pretty good. I just need to advance & improve it. That's self help effort hahahaha.