locked
Retrieve value from table and store in variable C# RRS feed

  • Question


  • Hi

    I want to store value from a row into a variable.

    I have used the following coding but it is not working properly.

    It is generating the following error:

    Index was outside the bounds of the array.System.Data

    Please note that the table contains only one row.

     objSqlConnection.ConnectionString = connectionStringSetting;
    try
    {
    objSqlConnection.Open();
    SqlCommand objSqlCommand = new SqlCommand("select FineCharges from FINECHARGES where FineID = 1", objSqlConnection);
    SqlDataReader objDataReader = objSqlCommand.ExecuteReader();
    int ChargeFine;
    while (objDataReader.Read())
    {
    ChargeFine = Convert.ToInt32(objDataReader[1].ToString());
    MessageBox.Show("Fine" + Convert.ToString(ChargeFine));
    }
    objSqlConnection.Close();
    }
    catch (SqlException e1)
    {
    MessageBox.Show(e1.Message + e1.Source);
    objSqlConnection.Close();
    }
    catch (Exception e2)
    {
    MessageBox.Show(e2.Message + e2.Source);
    objSqlConnection.Close();
    }
    Can anybody tell me what is the error?

    Please help me out!

    Thanks in advance!
    Thursday, July 9, 2009 1:30 PM

Answers

  • Is the error on this line:

    ChargeFine = Convert.ToInt32(objDataReader[1].ToString());
    If so, then I expect there is only a single field returned from the query, and you probably want the first, e.g.:

    ChargeFine = Convert.ToInt32(objDataReader[0].ToString());
    Also if you know the value returned is Integer, you could consider the following:

    ChargeFine = objDataReader.GetInt32(0);
    As the query only returns a single value you could also consider using the ExecuteScalar() method of the SqlCOmmand object:

                objSqlConnection.ConnectionString = connectionStringSetting;
                try
                {
                    objSqlConnection.Open();
                    SqlCommand objSqlCommand = new SqlCommand("select FineCharges from FINECHARGES where FineID = 1", objSqlConnection);
    
                    int ChargeFine;
                    ChargeFine = (int)objSqlCommand.ExecuteScalar();
                    MessageBox.Show("Fine" + Convert.ToString(ChargeFine));
    
                    objSqlConnection.Close();
                }
                catch (SqlException e1)
                {
                    MessageBox.Show(e1.Message + e1.Source);
                    objSqlConnection.Close();
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.Message + e2.Source);
                    objSqlConnection.Close();
                }


    Hope this helps

    Tom

    • Marked as answer by SNK08 Thursday, July 9, 2009 2:48 PM
    Thursday, July 9, 2009 1:48 PM
  • And easiest if you just need 1 value

    object myValue = objSqlCommand.ExecuteScalar();

    instead of:

    SqlDataReader objDataReader = objSqlCommand.ExecuteReader();

                    int ChargeFine;

                    while (objDataReader.Read())

                    {

                        ChargeFine = Convert.ToInt32(objDataReader[1].ToString());

                        MessageBox.Show("Fine" + Convert.ToString(ChargeFine));

                    }

    • Marked as answer by SNK08 Thursday, July 9, 2009 2:49 PM
    Thursday, July 9, 2009 1:50 PM

All replies

  • Is the error on this line:

    ChargeFine = Convert.ToInt32(objDataReader[1].ToString());
    If so, then I expect there is only a single field returned from the query, and you probably want the first, e.g.:

    ChargeFine = Convert.ToInt32(objDataReader[0].ToString());
    Also if you know the value returned is Integer, you could consider the following:

    ChargeFine = objDataReader.GetInt32(0);
    As the query only returns a single value you could also consider using the ExecuteScalar() method of the SqlCOmmand object:

                objSqlConnection.ConnectionString = connectionStringSetting;
                try
                {
                    objSqlConnection.Open();
                    SqlCommand objSqlCommand = new SqlCommand("select FineCharges from FINECHARGES where FineID = 1", objSqlConnection);
    
                    int ChargeFine;
                    ChargeFine = (int)objSqlCommand.ExecuteScalar();
                    MessageBox.Show("Fine" + Convert.ToString(ChargeFine));
    
                    objSqlConnection.Close();
                }
                catch (SqlException e1)
                {
                    MessageBox.Show(e1.Message + e1.Source);
                    objSqlConnection.Close();
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.Message + e2.Source);
                    objSqlConnection.Close();
                }


    Hope this helps

    Tom

    • Marked as answer by SNK08 Thursday, July 9, 2009 2:48 PM
    Thursday, July 9, 2009 1:48 PM
  • And easiest if you just need 1 value

    object myValue = objSqlCommand.ExecuteScalar();

    instead of:

    SqlDataReader objDataReader = objSqlCommand.ExecuteReader();

                    int ChargeFine;

                    while (objDataReader.Read())

                    {

                        ChargeFine = Convert.ToInt32(objDataReader[1].ToString());

                        MessageBox.Show("Fine" + Convert.ToString(ChargeFine));

                    }

    • Marked as answer by SNK08 Thursday, July 9, 2009 2:49 PM
    Thursday, July 9, 2009 1:50 PM