Join Two data tables

Question

Tank50 2 Junior Poster

17 Years Ago

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

.net:c# database-design microsoft:sql-server

6 Contributors
7 Replies
303 Views
1 Year Discussion Span
Latest Post 16 Years Ago Latest Post by kvprajapati

Dani AI

Generated 9 Months Ago

The compile error you saw is caused by trying to build a SQL string using DataTable objects and by an accidental assignment in the expression. Concatenating a DataTable into a string does not give a database table name (it yields a type/name), and the fragment ending with ... = table1.Columns[...] is interpreted by the compiler as an assignment, not part of a string. That is why the compiler complains the left side is not a variable you can assign to. was on the right track trying to join, but you must either issue a proper SQL JOIN to the database or perform a true in-memory join (not by embedding DataTable objects into an SQL string).

Preferred (and fastest) approach: let SQL Server do the join and return only the columns/rows you need. Use a parameterized command to avoid SQL injection and to let the server cache the plan. Example pattern:

string sql =
  "SELECT E.SalesExecutiveCode, E.ExecutiveName " +
  "FROM dbo.Executive AS E " +
  "INNER JOIN dbo.Retailer AS R ON E.ExecutiveCode = R.SalesExecutiveCode " +
  "WHERE R.RetailerCode = @code;";

using(var cmd = new SqlCommand(sql, conn)) {
  cmd.Parameters.Add("@code", SqlDbType.VarChar, 50).Value = "00000949";
  var dt = new DataTable();
  using(var da = new SqlDataAdapter(cmd)) da.Fill(dt);
}

If server-side joins are slow even for 20k rows, check indexing and the execution plan: ensure there are indexes on Retailer.RetailerCode and both join columns (Retailer.salesExecutiveCode, Executive.ExecutiveCode). and 's suggestions to push work to the DB and consider an indexed view are valid; indexed views help read-heavy workloads but have schema-binding and write-cost tradeoffs.

If you must join in memory (both tables already loaded), use a DataRelation or LINQ-to-DataSet to join rows reliably instead of string-building. showed import/merge is useful for union-like operations, but merging is not a relational join. Quick checklist: avoid SELECT *, filter in the WHERE, use parameters, inspect the query plan, and do not concatenate DataTable objects into SQL.

All 7 Replies

Renukavani 0 Junior Poster

17 Years Ago

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 171 Posting Virtuoso

17 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Tank50 2 Junior Poster · Answer 1 · 2008-10-02T18:37:40+00:00

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 171 Posting Virtuoso · Answer 2 · 2008-10-02T19:00:55+00:00

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 0 Newbie Poster · Answer 3 · 2010-06-23T13:28:58+00:00

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 30 Posting Whiz in Training · Answer 4 · 2010-06-24T09:04:34+00:00

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.

kvprajapati 1,826 Posting Genius Team Colleague · Answer 5 · 2010-06-24T09:07:39+00:00

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.

Join Two data tables

Dani AI

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers