sqldatareader output to a a label in winforms

Question

hello everyone, I am new to ADO.net and i am stuck in a situation.

I have a query like this :

select Sum(amount) as Total

from account

where AccountHolderName='Mark'

So what this query does is, it creates a new column Total which is the sum of the whole column "amount".

So now i need to get the value of the "Total" column and assign it to a label in my form application.

here is what i am doing,

SqlDataReader Reader = null;

MyConnection = new SqlConnection("SERVER=username;Uid=sa;

pwd=password;Database=Customer;");

string selectString = "select Sum(amount) as Total from account where AccountHolderName='" + tbUsername + "'"; SqlCommand oCommand = new SqlCommand(selectString, MyConnection); MyConnection.Open();

Reader = oCommand.ExecuteReader();

if (Reader.HasRows != false)

{

while (Reader.Read())

{

lbAmountLeft.Text= Reader["Total"].ToString();

}

}

But the problem is it doesn't consider "Total" as a column. Tried using even this,

while (Reader.Read())

{

lbAmountLeft.Text= Reader[0].ToString();

}

Doesn't really work. Can anyone help me out with this?

Answer 1

Hi,

Welcome to the MSDN forum.

For me, the code is running well on my machine.

I would suggest you to set a breakPoint at this line to see if it executed: lbAmountLeft.Text= Reader["Total"].ToString();

And then tell us what's going on please.

Edit: I would help you to move this case to C# General forum for better suppot too.

Thanks for your understanding and support.

---

Best Regards,
Rocky Yue[MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi, 

As i see you are going to expect only result from query 

"Select Sum(amount) from account where AccountHolderName='" + tbUsername + "'" right?

If this is the case best suited for this scenario is using "ExecuteScalar".

using(var connection = new SqlConnection("SERVER=username;Uid=sa;pwd=password;Database=Customer;"))
           {
               connection.Open();
               using(var command = new SqlCommand("select Sum(amount) as Total from account where AccountHolderName='" + tbUsername + "'",connection))
               {
                   var result = command.ExecuteScalar();
                   lbAmountLeft.Text = result == null ? string.Empty : result.ToString();
               }
		connection.Close()
           }

From your approach,

"But the problem is it doesn't consider "Total" as a column." If the reader does not find the column name provided it throws IndexOutOfRangeException. 

Are you seeing any exception?

If not there is no problem picking up result, but could be result is empty.

Hope this helps you...

 

---

If this post answers your question, please click "Mark As Answer". If this post is helpful please click "Mark as Helpful".

Answer 3

Have you set the breadpoint and got the runtime sql statement and put it run in Sql Server Management?

If it does not return any values, this may the sql statement issue.

So, you will need to check both the sql statement is correct or not and whether the sql statement will return a real value or not.

Best Regards

---

Neddy Ren[MSFT]
MSDN Community Support | Feedback to us