Retrieving a value from an sql table and storing it in a variable.

Question

0.00/5 (No votes)

See more:

Hi, I'm having trouble getting a value from an sql table and storing it in a variable.

SqlConnection connection = new SqlConnection(ConnectionString.Conn);<br />
            if (connection.State != ConnectionState.Open)<br />
            {<br />
                connection.Open();<br />
            }<br />
            <br />
            SqlCommand command = connection.CreateCommand();<br />
            command.CommandText = "SELECT * FROM LoginTable WHERE Username = ( '" + username + "')";<br />
            SqlDataReader dataread = command.ExecuteReader();<br />
            dataread.GetSqlInt32(cash);

LoginTable contains 2 coloums, Username and Cash.
Cash holds the amount of cash the user holds. I want to get that value to be able to use it in my application and then update it back using the UPDATE as an sql command.
However, my application crashes at

dataread.GetSqlInt32(cash);

Error states:
Invalid attempt to read when no data is present.
While in the row called there is an integer value of 1000.
Any help is greatly appreciated.

Posted 25-May-11 12:21pm

LighthouseCall

Add a Solution

3 solutions

Solution 2

if (dataread.HasRows)
{
    while (dataread.Read())
    {
        cashstring = (dataread.GetSqlValue(0).ToString());
    }
}
dataread.Close();
cash = int.Parse(cashstring);

Did some research as fcronin said. This worked perfectly for me. Thank you =]

Posted 25-May-11 13:00pm

LighthouseCall

Solution 3

If your intension is just to read a single value from SQL. Go for execute scalar instead of execute reader.

object objResult = dbCommand.ExecuteScalar();
int result;
if(objResult != null)
{
if(Int.TryParse(objResult,result out))
Console.Write("Result is: "+ result);
else
Console.Write("No data Found");
}

Posted 26-May-11 22:58pm

suren.info

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)

fcronin · Accepted Answer · 2011-05-25T12:28:00

Do some more research on the DataReader and how to iterate through your resulting rows.

The call to ExecuteReader() is geared toward returning a set of rows resulting from your query. So, what you'll want to do is check to see if your DataReader "HasRows", and if it does, you will loop through those rows to extract field (or 'column') data from them.

OleDbConnection dbConnection = new OleDbConnection();
OleDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = "select * from tbl where user='example'";
OleDbDataReader dbReader = dbCommand.ExecuteReader();
int cashValue = 0;
if (dbReader.HasRows)
{
    while (dbReader.Read())
    {
        cashValue = Convert.ToInt32(dbReader["cash"]);
    }
}