I want to combine the tables and getting their data and structure? [Resolved]

Posted by Sudheep.grandhe under Sql Server on 12/9/2011 | Points: 10 | Views : 1226 | Status : [Member] | Replies : 23
Hi,


i am having the tables like tbl_report1,tbl_report2 .i combine these two tables and retrieve the data and structure into tbl_customer.

tbl_report1 columns:
invoiceno(fk),orderno,paritculars,quantity,perprice,amount,invoicedate,ondate,compname,invoiceid

tbl_report2 columns:
compname,compaddress,invoiceno(pk),orderno,invoicedate,ondate,amountstatus,netamount,netvat,tamount

select t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount INTO tbl_report12 from tbl_report1 t1 RIGHT OUTER JOIN tbl_report2 t2 on t1.invoiceno=t2.invoiceno

if i execute the above query it shows the particulars,quantity,perprice,amount columns are null.

if i execute the tbl_report1 in this invoiceno column values shows null.but,in tbl_report2 the column invoiceno shows values.i dont no where i did the mistake.can you please help me.please do the needful.

Best,
Sudheep.



Responses

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi,
I got problem.
t2.invoiceno =" + Convert .ToInt32 (cmb_customername .SelectedValue )+" need to handle properly.

do one thing , try it now

private void cmb_customername_SelectedIndexChanged(object sender, EventArgs e)
{
int invoiceno = 0;
if( cmb_customername.SelectedValue.ToString() != "'System.Data.DataRowView" && cmb_customername.SelectedValue.ToString() != "" && cmb_customername.SelectedValue.ToString() != String.Empty )
{
invoiceno = Convert.ToInt32(cmb_customername.SelectedValue);
// make sure that invoiceno is int type only otherwise it would not convert to int. else if its contain alphanumeric then conver to string instead of int
}
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
con.Open();
SqlCommand cmd = new SqlCommand(" select t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,

t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount

from tbl_report2 t2 left join tbl_report1 t1 on t1.invoiceno=t2.invoiceno where t2.invoiceno ="+ invoiceno +"", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds );
if(ds.Tables.Count > 0)
{
txt_customeraddress.Text = ds.Tables[0].Rows[0]["customerid"].ToString();
}

}


Regards,
Sunil

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Sudheep,

As per your query you will get same data as u mentioned above if table "tbl_report1" does not invoice number because its a foreignkey and its allow null.
So better to check it correct first find out for which invoiceno it would not show records from table "tbl_report1" and query same invoiceno with table "tbl_report1" and you will come to know that whether same invoiceno present or not . if not then as per right outer join it will show all columns as NULL from table "tbl_report1".

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Thanks for replying to me.as you said in the tbl_report1 it shows the records.if i write the above query it wont shows the records

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
That means there is no date integrity in your both of tables.
Quick solution..
You r getting correct data only thing confusion,
Suppose.

tbl_report1 columns:
invoiceno(fk),orderno,paritculars,quantity,perprice,amount,invoicedate,ondate,compname,invoiceid
and data as
(1,1,NULL,1,20,200,"08/08/2011","08/08/2011",'ABC',11)
tbl_report2 columns:
compname,compaddress,invoiceno(pk),orderno,invoicedate,ondate,amountstatus,netamount,netvat,tamount
and here as
('ABC',1,1,""08/08/2011","08/08/2011",'Active',NULL,200,20,1000)
('ABC',2,1,""08/08/2011","08/08/2011",'Active',NULL,200,20,1000)


what would you expect from your query based on above data present in both of your tables.

Output :
You will get two rows based on query because right join on tbl_report2 so it will return all record from tbl_report2 table and matching records from tbl_report1.
So as we can see only one invoiceno present in tbl_report1 that is 1 so in final result you will get all data corresponding to invoiceno = 1 for 2 it wil return null for all comumns in tbl_report1 becasue invoice no =2 does not exist in tbl_report1.




Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

I am agree with you.As you said not only particulars it shows the null values in amount,perprice,quantity also .because of right join it shows all the records in tbl_report2 only.but it wont shows the tbl_report1.can i go for left join or full join.which one is better.can you please give me solution for this.

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Better go for left join ..
take base first and left join on supporting table.

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

now it shows in tbl_report2 null values for all columns

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Use below query

t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,

t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount
from tbl_report2 t2 left join tbl_report1 t1 on t1.invoiceno=t2.invoiceno


because here tbl_report2 is base table that contain PK and tbl_report1 is supporing table that contain FK that means there could be a invoice no or it may contains NULL also right .

So above query outwill be all data from tbl_report2 and matching records from tbl_report1 , even though not matching with invoice no all columns will be null.




Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,


it will display null values only in tbl_report1 columns

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
That means in table tbl_report1 dont have matching invoiceno what u have in tbl_report2.
for testing purpose manually update invoice column in tbl_report1 with what u have in tbl_report2 invoice no .
and then you will come to know where is exactly problem exists.

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

as you said exactly correct.in tbl_report1 no matching invoiceno w.r.t to tbl_report2.if i am going for the update it will execute.

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Yes Correct . Update one record and then run your query .
Hope i will work properly..

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

it's working sir.But,in combobox if i selected one value it has to display the remaining textboxes.but,it will not display.what is the error.please do the needful.

public partial class UpdateCustomerInvoice : Form
{
public UpdateCustomerInvoice()
{
InitializeComponent();
}
string str;
SqlConnection con;
SqlDataAdapter da;
DataSet ds;
DataSet ds1;
private void UpdateCustomerInvoice_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand(" select t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,

t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount

from tbl_report2 t2 left join tbl_report1 t1 on t1.invoiceno=t2.invoiceno", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
cmb_customername.Items.Add(row.ItemArray[0].ToString());
}
con.Close();
}

private void cmb_customername_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
con.Open();
SqlCommand cmd = new SqlCommand(" select t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,

t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount

from tbl_report2 t2 left join tbl_report1 t1 on t1.invoiceno=t2.invoiceno", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds );
DataRow trow;
ds.Tables[0].PrimaryKey = (new DataColumn[]{
ds .Tables [0].Columns [0]});
trow = ds.Tables[0].Rows.Find(cmb_customername.Text);
txt_customeraddress.Text = trow["custaddress"].ToString();
}
}
}

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
You mean first you want bind cmb_customername combobox with company name and based on selected company name you want to display company adress in textbox right .

If my understading matches with your requirement then i will give ur some tips to achieve that without writing so much of code.

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,


Absolutely your correct sir,based on the selection of comboxbox remaining things has to display like customer address,invoiceno,particulars,quantity etc..

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

public partial class UpdateCustomerInvoice : Form 

{
public UpdateCustomerInvoice()
{
InitializeComponent();
}
string str;
SqlConnection con;
SqlDataAdapter da;
DataSet ds;
DataSet ds1;
private void UpdateCustomerInvoice_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand(" select distinct compname,invoiceno from tbl_report2", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
// Combobox Databinding
If(ds.Tables.Count > 0 )
{
cmb_customername.DataSource = ds.Tables[0];
cmb_customername.DisplayMember = "compname";
cmb_customername.ValueMember = "invoiceno";
}
con.Close();
}

private void cmb_customername_SelectedIndexChanged(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
con.Open();
SqlCommand cmd = new SqlCommand(" select t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,

t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount

from tbl_report2 t2 left join tbl_report1 t1 on t1.invoiceno=t2.invoiceno where t2.invoiceno ="+ Convert.ToInt32(cmb_customername.SelectedValue) +"", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds );
if(ds.Tables.Count > 0)
{
txt_customeraddress.Text = ds.Tables[0].Rows[0]["customerid"].ToString();
}

}
}


On page load combobox binded with CompanyName as displaymember that means for user it will display as Companyname but as back side is bind invoiceno as value member.

And at selected index changed it will pass that selected invoice no and based on that u have to hit your db and fetch corresponding data again and for sure there will be only one record for one primary key as invoice no so that is reason during binding its hard coded row as 0 index.

Please check syntax properly because all code i have modified in notepad :)

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Thanks for replying to me.But,it shows the following error.
Unable to cast object of type 'System.Data.DataRowView' to type 'System.IConvertible'. in this line SqlCommand cmd=new SqlCommand ("select t2.compname,t2.compaddress,t2.invoiceno,t2.orderno,t2.ondate,t2.invoicedate,t2.amountstatus,t2.netamount,t2.netvat,t2.tamount,t1.particulars,t1.quantity,t1.perprice,t1.amount from tbl_report2 t2 left join tbl_report1 t1 on t1.invoiceno=t2.invoiceno where t2.invoiceno =" + Convert .ToInt32 (cmb_customername .SelectedValue )+"",con );


Best,
Sudheep.

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

here invoiceno declared as bigint .again it shows the same error.
Hi,

i wrote the query it show the following errors.please correct those errors.
Unable to cast object of type 'System.Data.DataRowView' to type 'System.IConvertible'.
if (cmb_customername.SelectedValue.ToString() != "'System.data.datarowview" && cmb_customername.SelectedValue.ToString() != "" && cmb_customername.SelectedValue.ToString() != string.Empty)
{
invoiceno = Convert.ToInt32(cmb_customername.SelectedValue);//error shows here:Unable to cast object of type 'System.Data.DataRowView' to type 'System.IConvertible'
}

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Fine.
Now try it earlier one extra qoutation was there as 'System.Data.DataRowView
if (cmb_customername.SelectedValue.ToString() != "System.Data.DataRowView" && cmb_customername.SelectedValue.ToString() != "" && cmb_customername.SelectedValue.ToString() != string.Empty) 

{
invoiceno = Convert.ToInt32(cmb_customername.SelectedValue);//error shows here:Unable to cast object of type 'System.Data.DataRowView' to type 'System.IConvertible'
}


debut it and see what value it return for selectedvalue .


Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

As you said i debug the coding it shows the combox item count as 17 and invoiceno=0

Best,
Sudheep.

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

Posted by: Sksingh on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
I guess intitially selected index get fires one datasource made on any combobox.
let it be first let form to be load and then selected one item from combobox and then check what value it return on selectedindexchanged event.

Regards,
Sunil

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

Posted by: Sudheep.grandhe on: 12/10/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

first i am debugging the form _load code.in that one thing i am observe that.it will execute that displaymember,valuemember.it will go to the combobox selectedindexchanged event.if i am trying to select the value from a combobox it will execute the form.

Best,
Sudheep.

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

Posted by: Sudheep.grandhe on: 12/10/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

The problem was resolved.But,in the amount status i put radiobuttons as paid,unpaid if i select any one value from the combobox it displays all the values except this paid and unpaid.in the database the column name is amountstatus.

Best,
Sudheep.

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

Login to post response