DataSet Filling column regarding others. RSS

• • Reply
  

  pluciorx

  Member

  53 Points

  69 Posts

  DataSet Filling column regarding others.

  Aug 26, 2009 06:58 PM|LINK

  HI

  I Have a DataSet filled like below::

  ID	PART	qtyNeeded	Qty Available	magasin	RELESABLE
  103131	PARTA	1	2	a
  103131	PARTA	1	0	b
  103131	PARTB	1	0	a
  103131	PARTB	1	0	b
  2222242	PARTC	1	5	a
  2222242	PARTA	1	2	a
  2222242	PARTA	1	0	b

  
  

  And i have to mark field Releasable with TRUE if for all parts in ID  exist quantity in QTY Available at least = 1 in any of magasines.

  Finally Data Set contains around 9000 rows

  there is no constant number of records for each ID regarding parts ( Some ID can have 2 some 3 and some 10 ... )

  each part can have max 5 magasines and min 1

  
  

  At the end DataSet Should look:

  ID	PART	qtyNeeded	Qty Available	magasin	RELEASABLE
  103131	PARTA	1	2	a	FALSE
  103131	PARTA	1	0	b	FALSE
  103131	PARTB	1	0	a	FALSE
  103131	PARTB	1	0	b	FALSE
  2222242	PARTC	1	5	a	TRUE
  2222242	PARTA	1	2	a	TRUE
  2222242	PARTA	1	0	b	TRUE

  
  

  I'm struggling with that two days.

  and any help would be appreciated
  

  <div style="overflow: hidden; position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px;" id="_mcePaste">

  aID	PART	qtyNeeded	Qty Available	magasin	RELESABLE
  103131	PARTA	1	2	a
  103131	PARTA	1	0	b
  103131	PARTB	1	0	a
  103131	PARTB	1	0	b
  2222242	PARTC	1	5	a
  2222242	PARTA	1	2	a
  2222242	PARTA	1	0	b

  </div>

  logic dataset algorithm

  << Plase mark my post as Answer if i helped You... >>
• • Reply
  

  Steelymar

  All-Star

  15283 Points

  2239 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:06 PM|LINK

   if you get this date from SQL server... is better to do this in SQL statement

  Regards,
  Stefan Uzunov
  MCTS: .NET Framework 3.5 ASP.NET Applications
• • Reply
  

  pluciorx

  Member

  53 Points

  69 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:15 PM|LINK

  Yes it is from SQL / How to do that ?
  

  << Plase mark my post as Answer if i helped You... >>
• • Reply
  

  Steelymar

  All-Star

  15283 Points

  2239 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:19 PM|LINK

   this is the way in Dataset:

  //DataSet ds = new DataSet();
  
  
          for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
          {
              string Where = "ID =" + ds.Tables[0].Rows[i]["ID"] + " and magasin = " + ds.Tables[0].Rows[i]["magasin"];
              ds.Tables[0].Rows[i]["RELEASABLE"] = (int)ds.Tables[0].Compute("sum(QtyAvailable)", Where) > 0 ? "TRUE" : "FALSE";
          }

  
   

  Regards,
  Stefan Uzunov
  MCTS: .NET Framework 3.5 ASP.NET Applications
  • Edited by pluciorx on Aug 26, 2009 07:44 PM
  • Edited by pluciorx on Aug 26, 2009 08:46 PM
• • Reply
  

  Steelymar

  All-Star

  15283 Points

  2239 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:25 PM|LINK

   in SQL:

  select T1.*,
  case
  when T2.sumQty =0 then 'FALSE' else 'TRUE' end as RELEASABLE
  from Mytable T1
  inner join
  (select ID,magasin ,sum([Qty Available]) as sumQty from Mytable group by ID,magasin) T2
  on T1.ID =T2.ID and T1.magasin= T2.magasin

  
   

  Regards,
  Stefan Uzunov
  MCTS: .NET Framework 3.5 ASP.NET Applications
  • Edited by pluciorx on Aug 26, 2009 07:44 PM
  • Edited by pluciorx on Aug 26, 2009 08:46 PM
• • Reply
  

  pluciorx

  Member

  53 Points

  69 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:36 PM|LINK

  wow!

  i will be able to test that tomorrow but i have question

  It Should be placed just after Data set is filled ?

  I'm filling it like :

  cmd = "SELECT .....
  
  
  dap1.SelectCommand = cmd
  dap1.Fill(Data, "Dane")
  
  
  FunCREATEreleasableCOL() // some code here to create RELEASABLE column;
  
  
  gridview1.datasource = Data;
  
  
  gridview1.databind();

  
  
  

  
  

  With your Code it should be like:
  

  cmd = "SELECT .....
  
  dap1.SelectCommand = cmd
  dap1.Fill(Data, "Dane")
  
  FunCREATEreleasableCOL() // some code here to create RELEASABLE column;
  
  for (int i = 0; i < Data.Tables[0].Rows.Count; i++)
          {
              string Where = "ID =" + Data.Tables[0].Rows[i]["ID"] + " and magasin = " + Data.Tables[0].Rows[i]["magasin"];
              Data.Tables[0].Rows[i]["RELEASABLE"] = (int)Data.Tables[0].Compute("sum(QtyAvailable)", Where) > 0 ? "TRUE" : "FALSE";
          }
  
  gridview1.datasource = Data;
  
  gridview1.databind();
  
  
  

  
  
  

  ?
  

  << Plase mark my post as Answer if i helped You... >>
  • Edited by pluciorx on Aug 26, 2009 07:39 PM
• • Reply
  

  Steelymar

  All-Star

  15283 Points

  2239 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:45 PM|LINK

   yes. but you should add before that RELEASABLE column:

  Data.Tables[0].Columns.Add("RELEASABLE");

  for (int i = 0; i < Data.Tables[0].Rows.Count; i++)  

  ..........

  Regards,
  Stefan Uzunov
  MCTS: .NET Framework 3.5 ASP.NET Applications
• • Reply
  

  pluciorx

  Member

  53 Points

  69 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 07:54 PM|LINK

  FunCREATEreleasableCOL() <- in that place i have some code to create That column.

  
  

  << Plase mark my post as Answer if i helped You... >>
• • Reply
  

  pluciorx

  Member

  53 Points

  69 Posts

  Re: DataSet Filling column regarding others.

  Aug 26, 2009 08:46 PM|LINK

  i have made a test code :

  SqlConnection con = new SqlConnection (@"Data Source=xxxxxxxxxxx");
            
  
              SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Test", con);
              DataSet DS = new DataSet();
                       
  
              da.Fill(DS,"Test");
  
  
              DS.Tables[0].Columns.Add("RELEASABLE");
  
  
              for (int i = 0; i < DS.Tables[0].Rows.Count; i++)
              {
                  string Where = "ID =" + DS.Tables[0].Rows[i]["ID"] + " and magasin = " + DS.Tables[0].Rows[i]["magasin"];
                  DS.Tables[0].Rows[i]["RELEASABLE"] = (int)DS.Tables[0].Compute("sum(QtyAva)", Where) > 0 ? "TRUE" : "FALSE";
              }
  
  
              GridView1.DataSourceID = null;
              GridView1.DataSource = DS;
              GridView1.DataBind();
          }

  
  
  

  and seems that there is an error somewhere

  as it gives error : 

  Cannot find column [a].

  at line:
  

  
  

  DS.Tables[0].Rows[i]["RELEASABLE"] = (int)DS.Tables[0].Compute("sum(QtyAva)", Where) > 0 ? "TRUE" : "FALSE";

  
  

  any Suggestions ?
  

  << Plase mark my post as Answer if i helped You... >>
  • Edited by pluciorx on Aug 26, 2009 09:03 PM
• • Reply
  

  Steelymar

  All-Star

  15283 Points

  2239 Posts

  Re: DataSet Filling column regarding others.

  Aug 27, 2009 04:09 AM|LINK

   magasin is string...thats way value should be in quotation marks:

  string Where = "ID =" + DS.Tables[0].Rows[i]["ID"] + " and magasin = '" + DS.Tables[0].Rows[i]["magasin"]+"'" ;

  have you try SQL version?

  Regards,
  Stefan Uzunov
  MCTS: .NET Framework 3.5 ASP.NET Applications