Ambiguous column name 'compname'(windows applications)?

Posted by Sudheep.grandhe under C# on 11/30/2011 | Points: 10 | Views : 2027 | Status : [Member] | Replies : 6
Hi,

it shows this kind of error.in my coding.please do the needful.
Error:Ambiguous column name 'compname'

private void loadcompanies()
{
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.quantity,t1.perprice,t1.amount,t2.netvat,t2.tamount,t2.netamount from tbl_report2 t2,tbl_report1 t1 where t1.invoiceno= t2.invoiceno and t2.compname=t1.compname AND compname ='hdfcbank'",con );
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 = "compname";
}
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.quantity,t1.perprice,t1.amount,t2.netvat,t2.tamount,t2.netamount from tbl_report2 t2,tbl_report1 t1 where t1.invoiceno= t2.invoiceno and t2.compname=t1.compname AND 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();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
txt_customeraddress .Text =dt .Rows [0][0].ToString ();
txt_customeraddress.Text = dt.Rows[0]["compaddress"].ToString();
txt_invoiceno.Text = dt.Rows[0]["invoiceno"].ToString();
txt_orderno.Text = dt.Rows[0]["orderno"].ToString();
txt_ondate.Text = dt.Rows[0]["ondate"].ToString();
cmb_particulars.Text = dt.Rows[0]["particulars"].ToString();
txt_quantity.Text = dt.Rows[0]["quantity"].ToString();
txt_perprice.Text = dt.Rows[0]["perprice"].ToString();
txt_amount.Text = dt.Rows[0]["amount"].ToString();
txt_vat.Text = dt.Rows[0]["netvat"].ToString();
txt_totalamount.Text = dt.Rows[0]["tamount"].ToString();
txt_netamount.Text = dt.Rows[0]["netamount"].ToString();
}

}

Best,
Sudheep.



Responses

Posted by: Sksingh on: 11/30/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,
You have to correct below query

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.quantity,t1.perprice,t1.amount,t2.netvat,t2.tamount,t2.netamount from tbl_report2 t2,tbl_report1 t1 where t1.invoiceno= t2.invoiceno and t2.compname=t1.compname AND compname ='hdfcbank'",con );


as

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.quantity,t1.perprice,t1.amount,t2.netvat,t2.tamount,t2.netamount from tbl_report2 t2,tbl_report1 t1 where t1.invoiceno= t2.invoiceno and t2.compname=t1.compname AND t1.compname ='hdfcbank'",con );


see above there underline statement change similary to whereever refered the same.



Regards,
Sunil

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

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

Up
0
Down
Hi,

If i wrote this code it will execute .But,it wont shows the data in my form

Best,
Sudheep.

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

Posted by: Sksingh on: 11/30/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

In your select statemenet you are not selecting compname but during binding with compname
see below

cmb_companyname.DataSource = dt; 

cmb_companyname.DisplayMember = "compname";
cmb_companyname.ValueMember = "compname";


fetch t1.companme in your select statement and check it out.



Regards,
Sunil

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

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

Up
0
Down
Hi,

As you said i am fetching the t1.compname in the select statement.it wont display the compname and other fields what i include in the select statement.please help me.

Best,
Sudheep.

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

Posted by: Sksingh on: 11/30/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

private void loadcompanies() 

{
SqlConnection con = new SqlConnection("data source=mars-104;initial catalog=marsweb;integrated security=true");
SqlCommand cmd = new SqlCommand("select t1.compname, t2.compaddress,t1.invoiceno,t1.orderno,t1.ondate,t1.particulars,t1.quantity,t1.perprice,t1.amount,t2.netvat,t2.tamount,t2.netamount from tbl_report2 t2,tbl_report1 t1 where t1.invoiceno= t2.invoiceno and t2.compname=t1.compname AND t1.compname ='hdfcbank'",con );
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 = "compname";
}


try above code and let me know if any problem..


Regards,
Sunil

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

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

Up
0
Down
Hi,

I tried your code.But,it wont display the details.

Best,
Sudheep.

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

Login to post response