problem getting data from 2 tables match.

utsav rathour

I have 2 tables where "userid" is common. first table has Username along with userid. And the second table has loan details along with userid. Now i want to make a search form where the client can enter Username to search for Loan details. How do i do it? It is kinda urgent.

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
you can do it by following way

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                //show CurrentSelected Row's Column 1 value in TextBox1
                textBox1.Text = dataGridView1.CurrentRow.Cells[0].Value.ToString();

                //show CurrentSelected Row's Column 2 value in TextBox2
                textBox2.Text = dataGridView1.CurrentRow.Cells[1].Value.ToString();

            }
        }
0
utsav rathour17y ago
Thank you so much friend. you have been of so much help to me. Thank you very much.
0
utsav rathour17y ago
Well thats ok but since we are taking values from two tables. in the dataridview and we need data from the one selected from the gridview. Wont that be a problem to use another ds.tables["loan"]; thing for that purpose. Any other way.
enerally I want to be able to show the data selected from the datagridview( after search) in a text box. for example the amount, or any other data. Could you help me do that and provide me the code. i would love to do it by myself with some help but currently i am runnin out of time.
Thanks
0
Kirtan Patel17y ago
Friend,

to do this we need to fill ds.Table["load"] with query "Select * from loan"

separately because in previous code we have filled the dt with mixed data from both tables "loan" and "debtors"

and if you need more help just let me know i will send you that files with modified code :)

just let me know where you want to update and how you want to update it :)

thank you :)
0
utsav rathour17y ago
One more problem arose though. I used to do this to show the value in textbox on the content_click property of datagridview
this.textBox1.Text = ds.Tables["loan"].Rows[this.dataGridView1.CurrentCell.RowIndex][3].ToString();

Now with datatable how do i show this. Sry to bother again.
0
utsav rathour17y ago
Thanks for the help man, and i'll check it. If it works sure, will check that Do you like my answer. Thanks for being of so mch help.
0
Kirtan Patel17y ago
Friend,

congrats problem solved :)

dont forget to mark "Do you like this answer" please :)

It was Happening Because Your Database Design Was not Correct ..

1. You have Defined AutoNumber type in DebtorID and in Loan Table you have Defined debtor_id as Text DataType ( cause od Error Type missmatch)

2. you have not defined foreign key relation ship between debtors and Loan table

corrected Database is in "Application1" folder

Download Corrected Code

0
utsav rathour17y ago
Here is the link,And I have mailed it to you too http://demo.codepixelz.com/accounts.zip
0
Kirtan Patel17y ago
Hey Friend ,

can you send me your code in Zip File

just upload it here and Send Me Download link

i will Resolve it Quickly ( [email protected] )

0
utsav rathour17y ago
Well I dont c any change in your latest reply. except for that you have missed a " on the query. The error it is giving is still the same. "Type mismatch in expression." and yes i changed the line that says, dataGridView1.DataSource = dt; to dataGridView1.DataSource = dt1; since it is dt1 that u have declared.
0
Kirtan Patel17y ago
```
OleDbCommand comm = new OleDbCommand("SELECT debtor.debtor_id, debtor.fullname, loan.loan_id,loan.amount,loan.duration FROM debtor INNER JOIN loan ON debtor.debtor_id = loan.debtor_id where debtor.fullname like '%"+txtSearch.Text+"%', cn); 
DataTable dt1 = new DataTable(); 
OleDbDataAdapter da1 = new OleDbDataAdapter(comm);
da1.Fill(dt1); 
dataGridView1.DataSource = dt;
```
0
utsav rathour17y ago
Well Kirtan here is what i did to your query. ------------------------------------------------ OleDbCommand comm = new OleDbCommand("SELECT debtor.debtor_id, debtor.fullname, loan.loan_id,loan.amount,loan.duration FROM debtor INNER JOIN loan ON debtor.debtor_id = loan.debtor_id where debtor.fullname like '%" + txtSearch.Text + "%'", cn); // comm.Parameters.AddWithValue("@username", txtSearch.Text.Trim()); DataTable dt1 = new DataTable(); OleDbDataAdapter da1 = new OleDbDataAdapter(comm); da1.Fill(dt1); //Show Data In DataGridView dataGridView1.DataSource = dt1; ---------------------------------------------- But it gives an error stating type mismatch.. And i even tried using your query as: ---------------------------------------------- da1 = new OleDbDataAdapter("SELECT debtor.debtor_id, debtor.fullname, loan.loan_id,loan.amount,loan.duration FROM debtor INNER JOIN loan ON debtor.debtor_id = loan.debtor_id where debtor.fullname like '%" + txtSearch.Text + "%'", cn); ds1 = new DataSet(); da.Fill(ds1, "debtor"); da.Fill(ds1, "loan"); dataGridView1.DataMember = "debtor"; dataGridView1.DataMember = "loan"; dataGridView1.DataSource = ds1; ----------------------------------------------- But it gives error stating "new keyword....." Where did i go wrong.
0
utsav rathour17y ago
Thanks man for the help. I'll let you know if it worked for me. Your help was really apriciated.
0

Kirtan Patel17y ago

Friend, Here is Code for completing your task

dont forget to mark "Do you like this answer" if my answer helped you :)

private void btnSearch_Click(object sender, EventArgs e)
        {
            //Connecting to database
            SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True");
            
            //Join query 
            SqlCommand comm = new SqlCommand("SELECT Table1.userid, Table1.username, Table2.loandetail FROM Table1 INNER JOIN Table2 ON Table1.userid = Table2.userid where Table1.username=@username",con);
            comm.Parameters.AddWithValue("@username", txtSearch.Text.Trim());
            DataTable dt = new DataTable(); 
            SqlDataAdapter da= new SqlDataAdapter(comm);
            da.Fill(dt);
            
            //Show Data In DataGridView
            dataGridView1.DataSource = dt;
            con.Close(); 
        }

Main thing Here is Query

SELECT Table1.userid, Table1.username, Table2.loandetail FROM Table1 INNER JOIN Table2 ON Table1.userid = Table2.userid where Table1.username=