i wrote the query it shows this error?Ambiguous column name [Resolved]

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

I wrote the following query.it shows this error.
Error:Ambiguous column name 'ondate','invoicedate','invoiceno','compname'

i am having the tables like tbl_report1,tbl_report2 .i combine these two tables and retrieve the data and structure into tbl_company.but it shows the error.
tbl_report1 columns:
invoiceno(fk),orderno,paritculars,quantity,perprice,amount,invoicedate,ondate,compname
tbl_report2 columns:
compname,compaddress,invoiceno(pk),orderno,invoicedate,ondate,amountstatus,netamount,netvat,tamount

tbl_company columns-i want to combine above both tables and getting data and structure in that time it shows the error.

Best,
Sudheep.



Responses

Posted by: Dot Net Team on: 12/3/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
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


Use this query.....

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

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

Up
0
Down
Hi,
If you look at below tables columns then column name 'ondate','invoicedate','invoiceno','compname' are common in both the tables.

tbl_report1 columns:
invoiceno(fk),orderno,paritculars,quantity,perprice,amount,invoicedate,ondate,compname
tbl_report2 columns:
compname,compaddress,invoiceno(pk),orderno,invoicedate,ondate,amountstatus,netamount,netvat,tamount


So better try to use alias name for those columns and then fetch information or if all above repeated columns have same value then fetch any one data based on alias name .

This will solve your problem.

Regards,
Sunil

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

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

Up
0
Down
Hi,

Thanks for replying to me.keep doing as you said using alias names .i used that.But,i got the table structure only but i need table's structure and data.i wrote my query like this.if any error's please correct it.please do the needful.
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 inner join tbl_report2 t2 on t1.invoiceno=t2.invoiceno


Best,
Sudheep.

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

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

Up
0
Down
Hi,

Before insert into 'tbl_report12" table, check it out whether your query returns data ?
Your query is correct .

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
inner join
tbl_report2 t2
on t1.invoiceno=t2.invoiceno


run above query and see whether any records returns ?

Regards,
Sunil

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

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

Up
0
Down
Hi,

I am run the above query it executes .But,it retrives the tables structure only no records

Best,
Sudheep.

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

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

Up
0
Down
Hi,

So it means data itself not there in your tables based on invoiceno (PK nad FK basis)

Regards,
Sunil

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

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

Up
0
Down
Hi,

Thanks for replying to me.as you said data is not there in my tables base on invoiceno.one more thing i have to tell to you .in tbl_report1 invoiceno column have the data as null only .in tbl_report2 it shows some invoiceno like 1,2,3 etc..

Best,
Sudheep.

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

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

Up
0
Down
Hi,

if i wrote this query in the form it shows the error as There is already an object named 'tbl_report12' in the database.
private void loadcompanies()
{
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 INTO tbl_report12 from tbl_report1 t1 RIGHT OUTER JOIN tbl_report2 t2 on t1.invoiceno=t2.invoiceno ",con );
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
da.Fill(dt);//error comes here
cmb_companyname.DataSource = dt;
cmb_companyname.DisplayMember = "compname";
cmb_companyname.ValueMember = "compname";
}

Best,
Sudheep.

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

Posted by: Dot Net Team on: 12/3/2011 [Member] Starter | Points: 25

Up
0
Down
SqlCommand("DROP TABLE tbl_report12 ; 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 ",con );

use it , don't know it's right way ?

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

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

Up
0
Down
Hi,


Thanks for replying to me.keep doing.it's working.

Best,
Sudheep.

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

Login to post response