problem getting data from 2 tables match.

Question

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.

Kirtan Patel · 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(); } }

utsav rathour · Answer

Thank you so much friend. you have been of so much help to me. Thank you very much.

utsav rathour · Answer

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

Kirtan Patel · Answer

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

utsav rathour · Answer

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.

utsav rathour · Answer

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.

Kirtan Patel · Answer

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

utsav rathour · Answer

Here is the link,And I have mailed it to you too http://demo.codepixelz.com/accounts.zip

Kirtan Patel · Answer

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 ( kirtan007@gmail.com )

utsav rathour · Answer

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.

Kirtan Patel · Answer

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: Consolas, "Courier New", Courier, Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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;

utsav rathour · Answer

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.

utsav rathour · Answer

Thanks man for the help. I'll let you know if it worked for me. Your help was really apriciated.

Kirtan Patel · Answer

Friend, Here is Code for completing your task dont forget to mark " Do you like this answer " if my answer helped you :) .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: Consolas, "Courier New", Courier, Monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 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=