get data depending on date

[rubenrav]

i have one list and one database. The list is made after i've decided a number in a numbericUpDown. So for an example i can get the list

fj1Section:
1
34
33
32
31
20
19
18
17
16

each of this sections exists in the database in the 2. column.

And in the database, each section have a date.

i wan't to find out what's row in the database have the oldest date and the section needs to be the same as one number in my list.

i've made it a little easier in the connection queri, to make the database descending by the date, so it would be enough to check the first item in the database and check if the section number is the same, if not, go to next row.

connection.Open();
                    OleDbDataReader reader = command.ExecuteReader();
                    for (int i = 0; i < fj1Sections.Count; i++)
                    {
                        while (reader.Read())
                        {
                            string section_no = reader[1].ToString();
                            if ((section_no == fj1Sections[i].ToString())
                            {
                                //result;

                                richTextBox1.Text = "Sekction: " + fj1Sections[i].ToString();

                            }

                        }
                    }

I've wrote this code, but it just check the first number in the list and find the date for this section, but most likely, it's not the oldest one.

Any help?

[agrothe]

Why don't you use a SQL Statement instead? If you only need one record (oldest date) then it isn't efficient to bring back all records from the database.

Amend your query to something,

SELECT TOP 1 [fieldname], [etc...], FROM tablename WHERE [column2] = '<listvalue>' ORDER BY [datefield]

That will give you one row with the oldest date where the column2 is equal to the value in your ComboBox.

Note: Highly recommended to use a parameterized query and not just concatenate the string, but this is just the sql example.

This reduces network traffic and greatly enhances your response time.

[rubenrav]

Okay so it's possible to say that say where should be a whole list. So the program is taking out one value for each number inn the list and is only selecting the one with oldest date?

Cool!

[agrothe]

Not sure if I understand you right. Can you give me a few examples database rows and what outcome you want?

Given database rows:

ID        Num        Date
1         34         1/16/2011
2         34         2/5/2011
3         13         5/5/2011

I'm assuming that when you Choose "34" in your dropdown, you want to get the oldest date, in this case, 1/16/2011.

Is that right? If so, my previous query does the trick. If not, we need to further refine.

[rubenrav]

it's a very long story to describe it.

But lets say my database look like this:

area - section - date
1 4 1-1-2008
1 21 1-3-2008
1 15 1-3-2008
1 34 1-7-2009
1 32 20-7-2010
1 31 20-1-2011

them when i choose 4 i the dropDown the list fj1Section will be made with this values: 1,35,33,32,20,19,17,16,18

then it will go trough the database and go trough the database with section column equal all of the values in fj1Sction list. So in this cage it will only compare row number 4, 5 and 6 and find out section 34 have oldest date.

I've already wrote this code:

richTextBox1.Text = "";
                bool test = true;
                string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Ruben\\Documents\\Magic Briefcase\\Alcoa\\car_mos.accdb;Persist Security Info=False";
                string queryString =
                    "SELECT dbo_SectionMeasure.[BakingFurnaceNo], dbo_SectionMeasure.[SectionNo], dbo_SectionMeasure.[MeasureDate], dbo_SectionMeasure.[IsLastValue] FROM dbo_SectionMeasure WHERE (((dbo_SectionMeasure.[IsLastValue])=True)) ORDER BY dbo_SectionMeasure.[MeasureDate] DESC;";
                OleDbConnection connection = new OleDbConnection(connectionString);
                OleDbCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                try
                {
                    connection.Open();
                    OleDbDataReader reader = command.ExecuteReader();
                    for (int i = 0; i < fj1Sections.Count; i++)
                    {
                        while (reader.Read())
                        {
                            string section_no = reader[1].ToString();
                            if ((section_no == fj1Sections[i].ToString())
                            {
                                //result;

                                richTextBox1.Text = "Section: " + fj1Sections[i].ToString() + "       Measurdate " + reader[2].ToString();
                            }

                        }
                    }
                }
                catch (Exception ee)
                {
                    richTextBox1.Text = ee.ToString();
                    throw;
                }

But like i said minute ago this gives me not what i want.

[agrothe]

Why the for loop? The while loop goes through all the rows.

[agrothe]

Ok, I understand a bit more now after running your code.

You are looping over each of the number in your list AND looping through your records.

It's not working because the while(reader.read()) does not reset each loop. The .read() function is forward only. See this MSDN article on the class.

I hope this helps. Try populating a dataset, then you can using it multiple times.

[rubenrav]

i've got a new idea. You know in the MS Access you can use query design to make queries. I think if i want to select top 1, like you said to me. i first want to use a for loop to make a query string where all of the numbers in my fj1Sections are represented.

I would need this where part:

WHERE (((dbo_SectionMeasure.BakingFurnaceNo)=1) AND ((dbo_SectionMeasure.IsLastValue)=True)) And ((dbo_SectionMeasure.SectionNo)=1) OR (((dbo_SectionMeasure.SectionNo)=2)) OR (((dbo_SectionMeasure.SectionNo)=3)) OR (((dbo_SectionMeasure.SectionNo)=4)) OR (((dbo_SectionMeasure.SectionNo)=5)) OR (((dbo_SectionMeasure.SectionNo)=6))

and think i can make this part:

((dbo_SectionMeasure.SectionNo)=1) OR (((dbo_SectionMeasure.SectionNo)=2)) OR (((dbo_SectionMeasure.SectionNo)=3)) OR (((dbo_SectionMeasure.SectionNo)=4)) OR (((dbo_SectionMeasure.SectionNo)=5)) OR (((dbo_SectionMeasure.SectionNo)=6))

by:

string wherestring
for (i = 0; i<(fj1Sections.Count;i++){
wherestring += "((dbo_SectionMeasure.SectionNo)="+ fj1Sections[i].ToString() + ") OR";}

wherestring += "((dbo_SectionMeasure.SectionNo)="+ fj1Sections[fj1.Sections.Count].ToString() + ") //Just to don't end with an or.

and then i just put it in here:

"WHERE (((dbo_SectionMeasure.BakingFurnaceNo)=1) AND ((dbo_SectionMeasure.IsLastValue)=True)) And "+ wherestring

[agrothe]

OK, so let me get this straight:

If you have the following:

1       35        1/1/2009        true
1       35        1/1/2011        true
1       16        1/1/2009        true
1       16        1/1/2011        true

You want to get rows 1 and 3 correct? The oldest date for 35 and oldest date for 16, right?

[rubenrav]

No, since the isLast is true. there will only be one row for each section. And i want to compare the section that is in my list. if the list is containing 35 but not 16, it will give 35 as answer, but if 35 and 16 is in the list, the oldest one will be showed.

[agrothe]

Ok, still not 100% sure what you are trying to accomplish, but I do know that the biggest issue is the way you are looping with the data reader.

I've taken your original code and converted to a datatable and it outputs the following in the richtextbox1: Section: 32 Measurdate 7/20/2010 12:00:00 AM Here is the modified code with a datatable you can actually use to loop with:

int[] fj1Sections = {1,35,33,32,20,19,17,16,18};
            bool test = true;
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=car_mos.accdb;Persist Security Info=False";
            string queryString =
                "SELECT dbo_SectionMeasure.[BakingFurnaceNo], dbo_SectionMeasure.[SectionNo], dbo_SectionMeasure.[MeasureDate], dbo_SectionMeasure.[IsLastValue] FROM dbo_SectionMeasure ORDER BY dbo_SectionMeasure.[MeasureDate] DESC;";
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            OleDbCommand command = new OleDbCommand(queryString, connection);
            DataTable dt = new DataTable();
            dt.Load(command.ExecuteReader());

            try
                {

                    OleDbDataReader reader = command.ExecuteReader();
                    
                    for (int i = 0; i < fj1Sections.Count() ; i++)
                    {
                        for(int x=0;x<dt.Rows.Count;x++)
                        {
                            string section_no = dt.Rows[x][1].ToString();
                            if ((section_no == fj1Sections[i].ToString()))
                            {
                                //result;

                                richTextBox1.Text = "Section: " + fj1Sections[i].ToString() + "       Measurdate " + dt.Rows[x][2].ToString();
                            }
 
                        }
                    }
                }
                catch (Exception ee)
                {
                    richTextBox1.Text = ee.ToString();
                    
                }

I'm sure there is a more efficient way to do this, but hopefully this gets you going!

See more on the DataTable usage here