Join Two data tables

[Tank50]

Hi guys

I want to join two tables and executive the sql statement.I mention what is code i wrote ,Once Complie the code it says
Error1 The left-hand side of an assignment must be a variable, property or indexer

SqlConnection Con3 = new SqlConnection("Data Source=IT;Initial Catalog=Test;Integrated Security=True");
            Con3.Open();
            string str1 = "Select * from dbo.Executive ";
            string str2 = "select  * from Retailer";
            SqlDataAdapter adpter = new SqlDataAdapter(str1,Con3);
            SqlDataAdapter adpter1=new SqlDataAdapter(str2,Con3);
            DataTable Executive = new DataTable();
            DataTable Retailer =new DataTable();
            adpter.Fill(Executive);
            adpter1.Fill(Retailer);
           // MessageBox.Show(Retailer.Rows.Count.ToString());
            DataTable table1 = new DataTable();
            string str4 = "select b.SalesExecutiveCode,ExecutiveName from" + Executive + " inner join" + "(" + "select SalesExecutiveCode from" + Retailer + "where RetailerCode='00000949'" + ")" + "as"+table1+" on" + Executive.Columns["ExecutiveCode"] = table1.Columns["SalesExecutiveCode"];

In above coding I create the datatable and I used it as alias when i join the two tables.But i think no data in that data,Pls tell me what is wrong in this coding

Thanks

[Renukavani]

Hi guys

I want to join two tables and executive the sql statement.I mention what is code i wrote ,Once Complie the code it says Error1 The left-hand side of an assignment must be a variable, property or indexer

SqlConnection Con3 = new SqlConnection("Data Source=IT;Initial Catalog=Test;Integrated Security=True");
            Con3.Open();
            string str1 = "Select * from dbo.Executive ";
            string str2 = "select  * from Retailer";
            SqlDataAdapter adpter = new SqlDataAdapter(str1,Con3);
            SqlDataAdapter adpter1=new SqlDataAdapter(str2,Con3);
            DataTable Executive = new DataTable();
            DataTable Retailer =new DataTable();
            adpter.Fill(Executive);
            adpter1.Fill(Retailer);
           // MessageBox.Show(Retailer.Rows.Count.ToString());
            DataTable table1 = new DataTable();
            string str4 = "select b.SalesExecutiveCode,ExecutiveName from" + Executive + " inner join" + "(" + "select SalesExecutiveCode from" + Retailer + "where RetailerCode='00000949'" + ")" + "as"+table1+" on" + Executive.Columns["ExecutiveCode"] = table1.Columns["SalesExecutiveCode"];

In above coding I create the datatable and I used it as alias when i join the two tables.But i think no data in that data,Pls tell me what is wrong in this coding

Thanks

hi
y r u dumping all this in str4. for ur identification, store first select query in 1 var and second query in 2 variable and check the condition . i think problem in after "as"

[LizR]

why not just do a joined query on the server, its much more efficient

[Tank50]

Hi

Yes,I did that before LizR,but in one tables there are more that 20,000,and and to Process that coding it takes at least 3 minitues.Therfore I decieded to put it into dataset and quey,but I dont know that is good method,I want to imporve the speed of the search process.

Thanks

[LizR]

It shouldnt do not in the database you selected it from. Assuming you have indexes 30k records should be processable in a matter of 1 or 2 seconds.

Im thinking your selection code needs tweeking as the DB should do the join, which is why its slow.

it should go something more like

str1 = "
select * from dbo.Executive inner join Retailer on Executive.ExecutiveCode = Retailer.salesExecutiveCode"

which should join your 2 tables in 1 big grid, if you then only need 1 value such as retailercode, then add the "where RetailerCode='00000949'" to that original selection..

Not only have you then only downloaded the records who matched the criteria, you've made the potentially remote server do all the work not your app.

[nmswamy_delhi]

Try this.. But your both DataTable must have same data schema

DataTable dt = dba.SelectParticular("Angelique_TransMaster T,Angelique_Group G,Angelique_Subitem SI,Angelique_Item I,Angelique_TransDetails TD,Angelique_Unit U", "T.PartyID,T.BillDate,G.GroupName,SI.SubItemName,I.ItemName,I.IID,'' as PID,TD.Quantity,TD.Rate,U.UnitName,TD.Amount,TD.BilledQty,'0.00' as BilledAmt", "T.TranID=TD.TranID and TD.SubIID=SI.SubIID and SI.GID=G.GID and TD.SubIID=SI.SubIID and TD.IID=I.IID and I.UID=U.UID and TD.BillingStatus=0 and TD.Activity=2");

DataTable dt1 = dba.SelectParticular("Angelique_TransMaster T,Angelique_Product P,Angelique_TransDetails TD,Angelique_Unit U", "T.PartyID,T.BillDate,'NA' as GroupName,'NA' as SubItemName, P.ProductName as ItemName,0 as IID, P.PID,TD.Quantity,TD.Rate,U.UnitName,TD.Amount,TD.BilledQty,'0.00' as BilledAmt", "T.TranID=TD.TranID and TD.PID=P.PID and P.UID=U.UID and TD.BillingStatus=0 and TD.Activity=2");
        
        if (dt.Rows.Count > 0)
        {
            foreach (DataRow drow in dt1.Rows)
            {
                dt.ImportRow(drow);
            }
            ViewState["Data"] = dt;
            dt.Constraints.Add("PKIID", dt.Columns["IID"], true);
            SearchFromViewState();
        }

[pritesh2010]

hey why are you doing this much stuck just create a view with the join of two table and then create a index on that view then fatch the recored from the datatable using where condition. it's very simple.

[__avd]

Dear members.

Please do not resurrect old threads.If you have any questions please ask. You are welcome to start your own threads.

Thread Locked.