get Integer value with sqldatareader?

Question

4.00/5 (1 vote)

See more:

Hi, i am trying to read an integer value from the database, here is my code:

string query = "SELECT SUM(amount) FROM rec_stats WHERE material=0";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                reader.Read();
            }
            catch (Exception e)
            {
                MessageBox.Show("! " + e.ToString());
            }
            txtbx.Text = reader[0].ToString();
            try
            {
                Q = reader.GetInt32(0);
            }
            catch (Exception x)
            {
                MessageBox.Show("### " + x.ToString());
            }
            reader.Close();

connection string is defined before, and Q is an integer, it keeps throwing exception at the Q = reader.GetInt32(0);

where did i do wrong?

Posted 20-Apr-11 21:37pm

BlackJack99

Updated 14-Jan-19 2:05am

Add a Solution

Comments

Ankit Rajput 21-Apr-11 3:39am

Can you please provide the details of Exception?

BlackJack99 21-Apr-11 3:44am

System.InvalidCastException: Specified cast is not valid.
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at RecyclingStats.MainWindow.ShowAll()

5 solutions

Solution 1

Try this :

C#

string query = "SELECT SUM(amount) FROM rec_stats WHERE material=0";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader reader = null;
            int myInt = 0;
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                   myInt = reader.GetInt32(0);
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show("! " + e.ToString());
            }
            txtbx.Text = myInt.ToString();

You can only read the values of a reader while cycling through it, in a while loop.

Hope this helps

Posted 20-Apr-11 21:44pm

Wayne Gaylard

Comments

Ankit Rajput 21-Apr-11 3:53am

Hi Wayne,

Are you sure, we can not use reader without cycling?

Regards
Ankit

Wayne Gaylard 21-Apr-11 3:58am

How else?

BlackJack99 21-Apr-11 4:00am

i think we can, since i the query should only return 1 row, :)

thanks for the help anyway,

Solution 5

Use it like this :

C#

try
            {
                reader = cmd.ExecuteReader();
                while(reader.Read())   // This is how record is read. Loop through the each record
                {
                    Q = int.Parse(reader["YourColumnName"]);
                    txtbx.Text = reader["YourColumnName"].ToString(); 
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("! " + e.ToString());
            }
            finally
            {
               reader.Close();

}

Posted 20-Apr-11 21:52pm

Tarun.K.S

Updated 20-Apr-11 21:58pm

v3

Comments

Tarun.K.S 21-Apr-11 3:53am

Oops seems like others have answered it.

BlackJack99 21-Apr-11 3:59am

thanks for the intention anyway, :)

Tarun.K.S 21-Apr-11 4:06am

Ohkay no problem! Glad you were able to solve it.

Solution 4

Hi,

In this case, Please check the data type of field amount.
It might be type of float.

It is possible that sum(amount) is returning some Float value.

Please check it.

Regards
Ankit

Posted 20-Apr-11 21:50pm

Ankit Rajput

Comments

BlackJack99 21-Apr-11 4:01am

i made sure it is an integer, it works using the parse function below, thanks anyway, :)

Solution 8

String sql = "Select Id From TblCategories Where CategoryName Like '%" + P.Title + "%'";
            DataTable table = new DataTable();
            SqlConnection con = new SqlConnection(mycon);
            SqlCommand cmd = new SqlCommand(sql,con);
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            table.Load(reader);
            int catid = Convert.ToInt32(table.Rows[0]["Id"]);
            return catid;

Posted 14-Jan-19 2:06am

Member 14117526

Comments

CHill60 14-Jan-19 8:41am

Reasons for my downvote:
1. The OP was trying to read an integer value from a sql query. There is absolutely no need load the datareader into a datatable in order to achieve that.
2. You have introduced the risk of SQL injection attacks. Never create SQL strings by concatenating strings. See SQL Injection - OWASP[^]
3. You didn't even bother to use the same SQL query that the OP posted
4. You use Convert.ToInt32 whereas the OP confirmed 8 years ago that the much more robust int.Parse actually worked.

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)

Mahendra.p25 · Accepted Answer · 2011-04-20T21:48:00

Solution 3

try to find that in this way

C#

objReader = objCommand.ExecuteReader();
            while (objReader.Read())
            {
                objProduct = new ProductMasterClass();
                objProduct.pid = int.Parse(objReader["product_id"].ToString());
                objProduct.icatid = int.Parse(objReader["cat_id"].ToString());
             }

Posted 20-Apr-11 21:48pm

Mahendra.p25

Comments

BlackJack99 21-Apr-11 3:51am

thanks, it works