how can i join these two tables ?(windows applications)

Posted by Sudheep.grandhe under C# on 11/28/2011 | Points: 10 | Views : 1227 | Status : [Member] | Replies : 1
Hi,
I am having two tables they are tbl_report1,tbl_report2.the columns
Tbl_report1 columns:
Invoiceno,particulars,quantity,perprice,amount,invoicedate,ondate,invoiceid,orderno
Tbl_report2 columns:
Compname,compaddress,invoiceno,orderno,ondate,invoicedate,amountstatus,netamount,netvat,tamount
Tbl_customer columns:
Custid,custname,custaddress,custcity,custstate,custstdcode,custphoneno,custmobileno
How can I combine these two tables
.in my form I am having these fields like I mentioned above two tables.my issue is if I select the compname it should display the remaining fields.for loading only companynames I wrote the code is below.
private void loadcompanies()
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand("select '0' as compaddress,'selectone' as compname union select compaddress,compname from dbo.tbl_report2 ", con);//I am using here customer table.for this reason only it shows customer address itself.can you please correct this for getting all fields.
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
da.Fill(dt);
cmb_companyname.DataSource = dt;
cmb_companyname.DisplayMember = "compname";
cmb_companyname.ValueMember = "compaddress";
}
But it shows the customeraddressonly.for that I wrote the code in selectedindexchangedevent.
private void cmb_companyname_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand("select t2.compaddress,t1.invoiceno,t1.orderno,t1.ondate,t1.particulars,t1.perprice,t1.amount,t2.netvat,t2.tamount,t2.netamount from tbl_report2 t2,tbl_report1 t1 where t1.compname='hdfcbank' and t2.compname='hdfcbank'", con);
//SqlCommand cmd = new SqlCommand("select custaddress from tbl_customer where custname=@custname", con);
//cmd.Parameters.AddWithValue("@custname",cmb_companyname .Text );
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
txt_customeraddress.Text = cmb_companyname.SelectedValue.ToString();
//txt_orderno.Text = cmb_companyname.SelectedValue.ToString();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
txt_customeraddress .Text =dt .Rows [0]["customeraddress"].ToString ();
//txt_customeraddress.Text = dt.Rows[0][0].ToString();
txt_invoiceno.Text = dt.Rows[0][1].ToString();
txt_orderno.Text = dt.Rows[0][2].ToString();
txt_ondate.Text = dt.Rows[0][3].ToString();
cmb_particulars.Text = dt.Rows[0][4].ToString();
txt_quantity.Text = dt.Rows[0][5].ToString();
txt_perprice.Text = dt.Rows[0][6].ToString();
txt_amount.Text = dt.Rows[0][7].ToString();
txt_vat.Text = dt.Rows[0][8].ToString();
txt_totalamount.Text = dt.Rows[0][9].ToString();
txt_netamount.Text = dt.Rows[0][10].ToString();
}

}
}

Best,
Sudheep.



Responses

Posted by: Arefin on: 11/29/2011 [Member] Starter | Points: 25

Up
0
Down
Hey Sudheep.grandhe,

I have said about this issue at your another post. you can follow the same procedure.

Thanks,
Arefin

Sudheep.grandhe, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response