Select 5 rows from database store each one in an array?

Question

I have a database that stores invoice information. Each invoice can have up to 5 jobs relation, each job has a unique id number.

I've performed a select statement selecting all jobs relevant to a single invoice.

I have tried many ways to read the selected job id and store them in a jobArray. I would prefer to have it selecting using a for loop but most of the ways I've tried use textBoxes (which I replaced with my array)

This is my most recent code;

     SqlCeCommand cmdCountJobs = new SqlCeCommand("SELECT COUNT(Job_ID)AS INVCOUNT FROM Invoice WHERE Invoice_Number = " + maxInvoice + " ", cn);
        cmdCountJobs.Connection = cn;
        reader = cmdCountJobs.ExecuteReader();
        while (reader.Read())
        {
            countValue = Convert.ToInt32(reader["INVCOUNT"].ToString());
        }         
        SqlCeCommand cmdJobSearch = new SqlCeCommand("SELECT Job_ID as ID FROM Invoice WHERE Invoice_Number = " + maxInvoice + " ", cn);
        cmdJobSearch.Connection = cn;
        reader = cmdJobSearch.ExecuteReader(); 
        SqlCeDataAdapter da = new SqlCeDataAdapter(cmdJobSearch);
        jobArray[0] = (reader.Read()) ? reader["ID"].ToString() : "";
        jobArray[1] = (reader.Read()) ? reader["ID"].ToString() : "";
        jobArray[2] = (reader.Read()) ? reader["ID"].ToString() : "";
        jobArray[3] = (reader.Read()) ? reader["ID"].ToString() : "";
        jobArray[4] = (reader.Read()) ? reader["ID"].ToString() : "";   
        }

Could you help me with this?

Why can't you do this `for (i = 0; i < 5; i++) { jobArray[i] = (reader.Read()) ? reader["ID"].ToString() : ""; }` ? — Vimal Stan, May 03 '13 at 13:29
You could also look into using a loop for assigning jobs to the invoice so if the number of jobs by invoice increase one day you won't have to change your code too much. — Sashenka, May 03 '13 at 13:29
You do not mention the problem. You tried the for-loop... so...? What happened? What was the problem? — Martin Mulder, May 03 '13 at 13:32
@noobob yes i have already open the connection but didn't see it was necessary to post it thats all. — AJ18, May 03 '13 at 13:39
@MartinMulder Using the for loop nothing happened it didn't run the while loop. — AJ18, May 03 '13 at 13:41

score 4 · Accepted Answer · edited May 23 '17 at 12:28

Why use an array? You could use a List(Of Int) to store the ID numbers using a normal loop.
And given the nature of lists you don't need to know before hand the number of jobs to set your array size, so you could have an Invoice with only 4 jobs or one with 6, but the logic of your code will not need to check for this.

    List<int> jobs = new List<int>();
    SqlCeCommand cmdJobSearch = new SqlCeCommand("SELECT Job_ID as ID FROM Invoice " + 
                                "WHERE Invoice_Number = @ivc", cn);
    cmdJobSearch.Connection = cn;
    cmdJobSearch.Parameters.AddWithValue("@ivc", maxInvoice);
    reader = cmdJobSearch.ExecuteReader(); 
    while(reader.Read())
         jobs.Add(Convert.ToInt32(reader["ID"]));

Also note that I have changed your query to avoid string concatenation. Probably this is not the case but it is a good practice to use parametrized queries to avoid Sql Injection

Of course, if you have the need to use an array somewhere in the remainder of your code, you can easily reference the list like an array

   TextBox1.Text = jobs[0].ToString();

or convert the list back to an array with

   int[] ids = jobs.ToArray();

@Steve can these values in the list be shown in separate text boxes? — AJ18, May 03 '13 at 13:33
Yes of course, why not. `TextBox1.Text = jobs[0].ToString()` or you can convert the list back to an array with `int[] ids = jobs.ToArray()` — Steve, May 03 '13 at 13:38

Select 5 rows from database store each one in an array?

1 Answers1