CODEWITHSUNDEEP
c#sql-serverwinformscrystal-reports
Xeroth
Xeroth

Reputation: 107

Crystal report Ambiguous column name error.

I am making a invoice program and i have need to prepare from 2 Tables in my database, i made a relationship union with name Invoice_No in both tables and i call both and call my invoice data from Invoice_No but when i execute all working good, after entering data and when i press print button it giving me an error Ambiguous column name Invoice_No, give me a solution

 Cursor = Cursors.WaitCursor;
            frmSalesinvoice frm = new frmSalesinvoice();
            invoice rpt = new invoice();
            //The report you created.
            SqlConnection myConnection = default(SqlConnection);
            SqlCommand MyCommand = new SqlCommand();
            SqlDataAdapter myDA = new SqlDataAdapter();
            DS_Invoice_all myDS = new DS_Invoice_all();
            myConnection = new SqlConnection(cs);
            MyCommand.Connection = myConnection;
            MyCommand.CommandText = "select * from Invoice_Info,Items_Soled where Items_Soled.Invoice_No=Invoice_Info.Invoice_No and Invoice_No= '" + textBoxInvoiceNo.Text + "'";
            MyCommand.CommandType = CommandType.Text;
            myDA.SelectCommand = MyCommand;
            myDA.Fill(myDS, "Invoice_Info");
            myDA.Fill(myDS, "Items_Soled");
            rpt.SetDataSource(myDS);
            frm.crystalReportViewer1.ReportSource = rpt;
            frm.Show();

Upvotes: 2

Views: 1052

Answers (3)

M.Ali
M.Ali

Reputation: 69584

You need to use the table name here since the column exists in both tables. Sql Server is getting confused that which column you are referring to :)

select * 
from Invoice_Info,Items_Soled 
where Items_Soled.Invoice_No=Invoice_Info.Invoice_No 
and [TableName].Invoice_No = ????

JOIN
A better way to achieve the same result will be to use JOIN with ON clause, something like this

select * 
from Invoice_Info INNER JOIN Items_Soled 
ON Items_Soled.Invoice_No=Invoice_Info.Invoice_No 
WHERE [TableName].Invoice_No = ????

Edit

 Cursor = Cursors.WaitCursor;
            frmSalesinvoice frm = new frmSalesinvoice();
            invoice rpt = new invoice();
            //The report you created.
            SqlConnection myConnection = default(SqlConnection);
            SqlCommand MyCommand = new SqlCommand();
            SqlDataAdapter myDA = new SqlDataAdapter();
            DS_Invoice_all myDS = new DS_Invoice_all();
            myConnection = new SqlConnection(cs);
            MyCommand.Connection = myConnection;
            MyCommand.CommandText = "select * from Invoice_Info INNER JOIN Items_Soled ON
 Items_Soled.Invoice_No=Invoice_Info.Invoice_No WHERE [TableName].Invoice_No = '" + textBoxInvoiceNo.Text + "'";
            MyCommand.CommandType = CommandType.Text;
            myDA.SelectCommand = MyCommand;
            myDA.Fill(myDS, "Invoice_Info");
            myDA.Fill(myDS, "Items_Soled");
            rpt.SetDataSource(myDS);
            frm.crystalReportViewer1.ReportSource = rpt;
            frm.Show();

Upvotes: 0

Sam
Sam

Reputation: 7313

You need to change your SQL query so you don't have any duplicate column names. You can alias the columns so you can get unique names.

For example, if both columns have a field named ID you should make them unique.

select 

info.Id As info_id,
soled.Id As soled_id,
--rest of your columns here with the table prefix

from Invoice_Info info
inner join Items_Soled soled
on soled.Invoice_No=info.Invoice_No
where info.Invoice_No= '

Also, you should try and avoid using Old Style joins they can make queries more difficult to read..

Upvotes: 1

campagnolo_1
campagnolo_1

Reputation: 2750

This error happens when you are joining 2 or more tables in a statement and there are columns among these tables that have the same name and you didn’t prefix the column name with the table name when referencing the column in your statement.

Items_Sold.Invoice_No=Invoice_Info.Invoice_No

Upvotes: 0

Related Questions