ExecuteReader, Adding Values to List, index out of range

Question

Hello I have following code:

  SqlCeCommand commandArbeitstage= new SqlCeCommand("select count(IDStundensatz) as Gesamt from tblstunden Where IDPersonal = @IDPersonal Group by datepart(month, Datum) Order By datepart(month, Datum)", verbindung);

            commandArbeitstage.Parameters.Add("IDPersonal", SqlDbType.Int);
            commandArbeitstage.Parameters["@IDPersonal"].Value = IDPersonal;


            SqlCeDataReader readerArbeitstage = commandArbeitstage.ExecuteReader();
            List<Int32> Arbeitstage = new List<Int32>();

                while (readerArbeitstage.Read())
                {
                    Arbeitstage.Add(readerArbeitstage.GetInt32(0));
                }
                Arbeitstage.GetRange
                textBox53.Text = Arbeitstage[0].ToString();
                textBox60.Text = Arbeitstage[1].ToString();
                textBox68.Text = Arbeitstage[2].ToString();

The query is counting working days in a table and order them by datepart. So I have one column [Gesamt] and 12 rows. I would like to assign the number of worked days to 12 textboxes. I have done that above for January to March.

If I add another line of code for april,

     textBox74.Text = Arbeitstage[3].ToString();

I get an out of range exception. I think the problem occurs because there is no record for April and hence the index[4] in List Arbeitstage doesn't exist. So therefor I would like to assign the textBox74.Text a zero.

Anybody an idea?

Many thanks in advance!

Szymon · Accepted Answer · 2014-03-05T23:23:12.267

1

If you need the number of days for each month, you can use an array that is inialised with zeroes to store the numbers for each month. You can then reference the values for each month (by index 0 to 11). You will also need to change the query to return the month number.

SqlCeCommand commandArbeitstage= new SqlCeCommand("select datepart(month, Datum) as Month, count(IDStundensatz) as Gesamt from tblstunden Where IDPersonal = @IDPersonal Group by datepart(month, Datum) Order By datepart(month, Datum)", verbindung);

commandArbeitstage.Parameters.Add("IDPersonal", SqlDbType.Int);
commandArbeitstage.Parameters["@IDPersonal"].Value = IDPersonal;


SqlCeDataReader readerArbeitstage = commandArbeitstage.ExecuteReader();
Int32[] Arbeitstage = new Int32[12];

while (readerArbeitstage.Read())
{
    Arbeitstage[readerArbeitstage.GetInt32(0) - 1]  = readerArbeitstage.GetInt32(1));
}
textBox53.Text = Arbeitstage[0].ToString();  // January
// ... and so on up to 11

edited Mar 05 '14 at 23:23

answered Mar 05 '14 at 22:58

Szymon

42,577
16
96
114

1

Or instead of pretty redundant "if-else" sequence, he could allocate a zeroed array of length 12, then update this array by enumerating values which were returned from db and finally assign values stored in the array to respective textboxes without checking for any conditions. – nullop Mar 05 '14 at 23:17
I tried that with my first and second query, but the array was filled by the number of rows: 1 2 3 And not by the values in the column days: i.e. 3 1 2. – user3204119 Mar 06 '14 at 17:24
So are you using a query as in my example above? – Szymon Mar 06 '14 at 18:47

Edper · Answer 2 · 2014-03-05T23:35:56.093

You could attack this one in at least two ways. First without using a List<> or you could use one and then loop through the list.

Without using a list could be like this:

int ctr=1;
while (readerArbeitstage.Read())
{
   switch (ctr++)
      {
         case 1: // January
             textBox53.Text = readerArbeitstage.GetInt32(1);
             break;
         case 2: // February
             textBox60.Text = readerArbeitstage.GetInt32(1);
             break;
         case 3: // March
             textBox68.Text = readerArbeitstage.GetInt32(1);
             break;
     } 
}

Or using a List<> would probably look like this:

while (readerArbeitstage.Read())
   {
      Arbeitstage.Add(readerArbeitstage.GetInt32(0));
   }

for (int i=0; i<Arbeitstage.Count-1;i++)
{
   switch (i)
      {
         case 1: // January
             textBox53.Text = Arbeitstage[i].ToString();
             break;
         case 2: // February
             textBox60.Text = Arbeitstage[i].ToString();
             break;
         case 3: // March
             textBox68.Text = Arbeitstage[i].ToString();
             break;
     } 
}

It's good by the way that you name your Textboxes properly and not just with numbers at the end, like probably txtJanuary, txtFebruary, txtMarch etc.

1st one will have the same problem as the OP asked about, second won't work at all. — Tony Hopkinson, Mar 05 '14 at 23:31
@TonyHopkinson Thank you for pointing that out specially the 2nd one. I just copy and pasted it from the 1st one. I updated the 2nd one therefore. — Edper, Mar 05 '14 at 23:37

score 0 · Answer 3 · answered Mar 05 '14 at 23:26

There's good few ways of doing that. One would be

change your query to select the month number - 1 and the count.

so you end up with something Like

Month, Value
2      200
7      601

etc

Pre-populate your list with the twelve months;

then when reading the values from the query do something like

while (readerArbeitstage.Read())
{
int mnth = readerArbeitstage['Month'].AsInteger;
Arbeitstage[mnth] = readerArbeitstage['value'].AsInteger;
}

You'd be a lot better off if you had your textboxes in an array by the way, or you could derive the name from monthNumber and then Find Them in the Controls collection.

score 0 · Answer 4 · answered Mar 06 '14 at 17:21

thank you very much! The idea with a zero populated List helped me. I changed the query and assigned the values as Tony Hopkins suggested. This has been the easiest solution for me, because I am new to C# and programming overall. But thank you for the hints on CASE/SWTCH and using the textboxes in an array. I'll try that out. Here is the Code that works for me:

  SqlCeCommand commandArbeitstage = new SqlCeCommand("SELECT DATEPART (month, Datum) AS   Monat, COUNT(IDStundensatz) AS AnzahlTage FROM tblstunden WHERE IDPersonal = @IDPersonal GROUP BY DATEPART(month,datum)", verbindung);
            commandArbeitstage.Parameters.Add("IDPersonal", SqlDbType.Int);
            commandArbeitstage.Parameters["@IDPersonal"].Value = IDPersonal;


            SqlCeDataReader readerArbeitstage = commandArbeitstage.ExecuteReader();
             Int32[] Arbeitstage = new Int32[13];

            while (readerArbeitstage.Read())
             {
                int mnth = Convert.ToInt32(readerArbeitstage["Monat"].ToString());
                Arbeitstage[mnth] = Convert.ToInt32(readerArbeitstage["AnzahlTage"].ToString());
             }

            ATageJan.Text = Arbeitstage[1].ToString();
            ATageFeb.Text = Arbeitstage[2].ToString();
            ATageMrz.Text = Arbeitstage[3].ToString();
            ATageApr.Text = Arbeitstage[4].ToString();
            ATageMai.Text = Arbeitstage[5].ToString();
            ATageJun.Text = Arbeitstage[6].ToString();
            ATageJul.Text = Arbeitstage[7].ToString();
            ATageAug.Text = Arbeitstage[8].ToString();
            ATageSep.Text = Arbeitstage[9].ToString();
            ATageOkt.Text = Arbeitstage[10].ToString();
            ATageNov.Text = Arbeitstage[11].ToString();
            ATageDez.Text = Arbeitstage[12].ToString();

ExecuteReader, Adding Values to List, index out of range

4 Answers4