i wrote the query it shows this error?Ambiguous column name [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

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

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

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

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

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

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

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

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

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