reading data from database to display in label

Question

5.00/5 (1 vote)

See more:

how would i display the balance from my database. I have tried the following code but i am having no luck getting it to work.

C#

SqlDataReader readdata;
          
                sqlCommandCheckBalance.Connection.Open();
                sqlCommandCheckBalance.CommandText = "SELECT * FROM Accounts WHERE accountID = '" + "accountID" + "'";  
                readdata = sqlCommandCheckBalance.ExecuteReader();
                string balanceDB = null;
                while (readdata.Read())
                {
                    balanceDB = readdata["balance"].ToString();
                }
                balanceShow.Text += " " + balanceDB.ToString();

Posted 24-Feb-12 5:51am

stefanere2k9

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2012-02-24T05:57:00

Solution 1

Take off the spare double quotes:

C#

sqlCommandCheckBalance.CommandText = "SELECT * FROM Accounts WHERE accountID = '" + "accountID" + "'";

Becomes

C#

sqlCommandCheckBalance.CommandText = "SELECT * FROM Accounts WHERE accountID = '" + accountID + "'";

Or (preferably) use a parametrized query instead!

like this? sqlCommandCheckBalance.Parameters["accountID"].Value = balanceShow.Text;

And

on this line of code - readdata = sqlCommandCheckBalance.ExecuteReader();
cannot convert parameter from string to int32... how would i convert it try a few things but nothing working

That is SQL complaining about the value of a parameter you have passed over - presumably the account ID.
Check the type you gave it when you declared it, or try this:

C#

sqlCommandCheckBalance.CommandText = "SELECT * FROM Accounts WHERE accountID = @ID";
sqlCommandCheckBalance.Parameters.AddWithValue("@ID", accountID);

I don't guarantee that will compile, because I don't know type sqlCommandCheckBalance is - if it is an SqlCommand object it should be fine.
BTW: You do realise you can specify both the command and connection in the SqlCommand constructor?

C#

SqlCommand sqlCommandCheckBalance = new SqlCommand("SELECT * FROM Accounts WHERE accountID = @ID", con);
sqlCommandCheckBalance.Parameters.AddWithValue("@ID", accountID);

A lot of people think it is a lot tidier way to do it!

Posted 24-Feb-12 5:57am

OriginalGriff

Updated 24-Feb-12 8:07am

v2

Comments

stefanere2k9 24-Feb-12 12:41pm

like this? sqlCommandCheckBalance.Parameters["accountID"].Value = balanceShow.Text;

stefanere2k9 24-Feb-12 12:48pm

on this line of code - readdata = sqlCommandCheckBalance.ExecuteReader();
cannot convert parameter from string to int32... how would i convert it try a few things but nothing working

stefanere2k9 7-Mar-12 13:53pm

managed to get most of the code working now
SqlDataReader readdata;

{
sqlCommandbalance.Connection.Open();
sqlCommandbalance.Parameters["@accountID"].Value = _cardNumber;
readdata = sqlCommandbalance.ExecuteReader();
string balanceDB = null;
while (readdata.Read())
{
balanceDB = readdata["balance"].ToString();
}
sqlCommandbalance.Connection.Close();
sqlCommandbalance.Dispose();
balanceShow.Text += " " + balanceDB.ToString();
}
on the last line balanceShow.Text += " " + balanceDB.ToString(); i got a error saying Object reference not set to an instance of an object.

OriginalGriff 7-Mar-12 15:03pm

Check balanceShow is set to a value and not null. If it is, then your reader is returning no records as balanceDB is null.
At a guess, your _cardNumber is not a valid account ID.

stefanere2k9 7-Mar-12 15:08pm

_cardNumber is the value from the previous form

OriginalGriff 7-Mar-12 15:38pm

That doesn't mean it matches any AccountID values...

stefanere2k9 7-Mar-12 15:59pm

sorry for wasting your time i was passing the wrong data over. Now it working fine. what i need to do aswell i want to display available balance where balance + overdraftLimit how would i do that

OriginalGriff 8-Mar-12 2:55am

Depends on where the info is, and how you want to do it. Either way, you need to add (or subtract...I'm not sure how you store these things) the one and the other. Either do in the C# in the obvious way, or you can return it as a field from SQL via part of your SELECT statement:
SELECT balance, odlimit, balance + odlimit AS availableMoney FROM myTable WHERE ...