up vote 0 down vote favorite

I've the below sql statement as follows: 

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

Fields in ViewSectorInvestments:

AccountNumber
SectorName
AmountInvested

I'm trying to compute the AmountInvested in each sector against the total investments. So the formula will be: AmountInvested/TotalInvestments * 100

my code is as follows:

    string DMConnectionString = ConfigurationManager.ConnectionStrings["DMConnectionString"].ConnectionString;
    SqlConnection DMConnection = new SqlConnection(DMConnectionString);
    DMConnection.ConnectionString = DMConnectionString;

    string DMCommandText = "SELECT Name,RiskProfile,AccountNumber,TotalInvestments FROM ViewClientDetails WHERE AccountNumber = @AccountNumber; SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber ;SELECT * FROM ViewStockTypeInvestments WHERE AccountNumber = @AccountNumber  ";
    SqlCommand DMCommand = new SqlCommand(DMCommandText, DMConnection);
    DMCommand.Parameters.AddWithValue("@AccountNumber", lb_AcctNum.Text);
    DMConnection.Open();

    SqlDataReader DMReader = DMCommand.ExecuteReader();

    ArrayList SectorArray = new ArrayList();
    ArrayList StockTypeArray = new ArrayList();

    while (DMReader.Read())
    {
        CustName.Text = DMReader["Name"].ToString();
        lb_Risk.Text = DMReader["RiskProfile"].ToString();
        T_Investment.Text = DMReader.GetDecimal(DMReader.GetOrdinal("TotalInvestments")).ToString("N2");
        Client_RiskProfile.Text = DMReader["RiskProfile"].ToString();

        //encounter error when i add the datas into arraylist. 
        //System.IndexOutOfRangeException: SectorName

        SectorArray.Add(DMReader.GetOrdinal("SectorName").ToString());
        StockTypeArray.Add(DMReader.GetOrdinal("BlueChipName").ToString());


        foreach( Object objReader in SectorArray){
        //compute the percentage of amount invested in each sector
        //check if the percentage is more than 25%
        //if it is more than 25% lbMsg (an label) shows the name of the sector.

        }
    }

    DMReader.Close();
    DMConnection.Close();
}

When i test out the sql statement :

SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber

The result i got is :

AccountNumber  SectorName              AmountInvested
1001         Commerce            97230.00000
1001         Construction            389350.00000
1001         Finance             222830.00000
1001         Hotel                     14910.00000
1001         Loans                     105070.00000
1001         Manufacturing           1232210.00000
1001         Mining/Quarrying        32700.00000

I encountered System.IndexOutOfRangeException: SectorName. What's wrong with my code? Please advice me. Thanks in advance.

share|improve this question
Is there a question here? It's hard to locate anyway. I would suggest NOT putting the crux of your question in a comment in your code block. – pseudocoder Feb 2 '12 at 18:33
Also, you don't have ANY code attempting computation, or even extracting the computational variables from the query results, and you appear to not understand how SqlDataReader works at all. Are you working on someone else's code with no experience whatsoever? – pseudocoder Feb 2 '12 at 18:45
I havent added the codes for the computation. I cant even read the values at the moment. – user1125911 Feb 2 '12 at 18:46
double TInvestments = Convert.ToDouble(DMReader["TotalInvestments"].ToString()); double SAmt = Convert.ToDouble(DMReader["SAmountInvested"].ToString()); double SPercent = (SAmt / TInvestments) * 100; if (SPercent > 25.0) { lb_Sector2.Text = DMReader["SectorName"].ToString(); } – user1125911 Feb 2 '12 at 18:47
the above code is what i wanted to add into the foreach loop. i'm not sure what went wrong. – user1125911 Feb 2 '12 at 18:48

1 Answer

up vote 0 down vote

string DMCommandText = "SELECT Name,RiskProfile,AccountNumber,TotalInvestments FROM ViewClientDetails WHERE AccountNumber = @AccountNumber; SELECT * FROM ViewSectorInvestments WHERE AccountNumber = @AccountNumber ;SELECT * FROM ViewStockTypeInvestments WHERE AccountNumber = @AccountNumber ";

This CommandText contains multiple queries. Only the results from the last SELECT statement will be returned to the SqlDataReader.

SectorArray.Add(DMReader.GetOrdinal("SectorName").ToString());

You are trying to access the column ordinal of a field called "SectorName" in your SqlDataReader. The problem causing your exception is probably that the column doesn't exist, but it's hard to say since you are using SELECT * in your CommandText.

share|improve this answer
so does that mean that i cant have multiple select statements? – user1125911 Feb 2 '12 at 19:02

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.