search inside two access tables from C# form

Question

this is my code, How can I search inside Table1 and Table2 with same textbox1.text value, by the other words:

// [Table1]

| name     | age            |
| -------- | -------------- |
| aaa      | 12             |
| bbb      | 13             |
| ccc      | 14             |

//     [Table2]
| name     | gender         |
| -------- | -------------- |
| aaa      | male           |
| bbb      | female         |
| ccc      | male           |

How can I get age and gender values and display it inside txt.Text when [name] equal to textbox1.text?

thanks

 con.Open();
            // search inside table1
            OleDbCommand da = new OleDbCommand("SELECT * FROM [Table1] WHERE Table1.name='@name",con);
 
           //search inside table2
            OleDbCommand da2 = new OleDbCommand("SELECT * FROM [Table2] WHERE Table2.name='@name", con);
            
            // give @name value to table1 and table2 as parameters
            da.Parameters.AddWithValue("@name",textBox1.Text);
            da2.Parameters.AddWithValue("@name", textBox1.Text);

            // its time for executing 
            OleDbDataReader dr = da.ExecuteReader();
            OleDbDataReader dr2 = da2.ExecuteReader();

            // now display it in textBox as a string
            if (dr.Read())
            {
                dr2.Read();
                for (int x = 0; x < 4;x++ ) {
                    if(dr[x].ToString() !=""){

                        txt.Text += dr[x].ToString() + System.Environment.NewLine;
                    }
                }
            }
            else { MessageBox.Show("error"); }
            con.Close();

Why is this data even in two tables and not just one? Possibly should save birthdate and calculate age. — June7, Dec 30 '20 at 23:06
Your SQL seems to have a syntax error; odd number of apostrophes — Caius Jard, Dec 31 '20 at 00:03
Also, your logic for using the Read() methods of the readers is a bit wonky. Use a dataadapter and fill a datatable instead — Caius Jard, Dec 31 '20 at 00:04
@June7 this is just example i have 15 columns in each one... — Aiman, Dec 31 '20 at 08:15
@CaiusJard i would to use dataadapter but how can i display it in textbox — Aiman, Dec 31 '20 at 08:17
of course not,, person in table 1 may have empty value in some column — Aiman, Dec 31 '20 at 08:28

score 0 · Accepted Answer · answered Dec 31 '20 at 08:29

0

To join the data together and restrict to just what is written in a textbox and display in a datagridview Control:

using(var da = new OleDbDataAdapter("SELECT FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Name = t2.Name WHERE t1.name=@name", con)){
    da.SelectCommand.Parameters.AddWithValue("@name",textBox1.Text);

    var dt = new DataTable();
    da.Fill(dt);

    someDataGridView.DataSource = dt;
}

It's literally all you need to do; the dataadapter will open the connection etc; just write an sql, add parameters, fill a datatable, assign the table to be the DataSource of a grid (you'll need to add a datagridview to your form and name it and adjust the code name)

answered Dec 31 '20 at 08:29

Caius Jard

72,509
5
49
80

can I display it in textbox multiline ? – Aiman Dec 31 '20 at 08:45
Yes, you can.. but you shouldn't because it will look awful, unless you set a monospace font and space-pad the data out so it has a consistent width - that's what we did on terminal based systems 40 years ago. If your heart is set on it, write a loop that passes over the rows in the datatable and add string representations of them to a textbox. You'll end up with a result that is slow, lacks functionality (sorting, editing) and flexibility and is a chore to maintain and update. I can't see any positives – Caius Jard Dec 31 '20 at 08:48
sir, I know it look awful but i want to customize it and display data vertically and add some constant text in each line and then if the user have any edit before print it he can do it easily plus i have 15 columns in each one some columns have empty values, i donit want to show it to user – Aiman Dec 31 '20 at 09:02
Consider a reporting solution then; plenty exist - https://github.com/ritchiecarroll/Report.NET came up in the first 5 results for "c# report library" - never used it but it seems reasonable (TableLayoutSample looks to be what you want) – Caius Jard Dec 31 '20 at 09:07
If your question is resolved you should click the gray check mark to turn it green. This means it shows in a different color on the dashboard and people know it is answered – Caius Jard Dec 31 '20 at 09:51
its not exactly what i want, but you deserve accepting answer for your effort, and your last code working for datagridview – Aiman Dec 31 '20 at 09:59

search inside two access tables from C# form

1 Answers1