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
– Vimal Stan, Commented May 3, 2013 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
– Sashenka, Commented May 3, 2013 at 13:29
You do not mention the problem. You tried the for-loop... so...? What happened? What was the problem? — Martin Mulder
– Martin Mulder, Commented May 3, 2013 at 13:32
@noobob yes i have already open the connection but didn't see it was necessary to post it thats all. — AJ18
– AJ18, Commented May 3, 2013 at 13:39

Community · Accepted Answer · 2017-05-23 12:28:09Z

4

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();

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered May 3, 2013 at 13:29

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AJ18 Over a year ago

@Steve can these values in the list be shown in separate text boxes?

Steve Over a year ago

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()

AJ18 Over a year ago

@Steve Thanks a lot it was a great help.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related