i have stored procedure following code, works fine in sql server. need use returned rows work stacked column chart. don't know how receive these rows, though have tried code given after stored procedure code. appreciate if find out doing wrong. thank :)
stored procedure code:
alter procedure [dbo].[weekly] @id_beleg int /* contains id of machine number of orders required*/ begin declare @montag datetime declare @weekend datetime set @montag = dateadd(wk, datediff(wk,0,getdate()), 0) /* set @montag variable date on monday of current week*/ set @weekend = dateadd(day, 6, @montag) /* set @weekend variable date on sunday of current week*/ /* contains records of 1 process current week*/ create table #weeklysieben ( tag datetime, aufträge_anzahl int, anlage varchar(30) ); insert #weeklysieben (tag, aufträge_anzahl, anlage) select * dbo.sieben tag between @montag , getdate() order tag declare @tempdate datetime /* contains unique machine names*/ create table #anlage_sieben ( anl varchar(30) ) /* contains date days of current week*/ create table #alldays ( days datetime ) insert #anlage_sieben (anl) select kurztext anl dbo.tblbelegungseinheit id in (select id_belegungseinheit dbo.tblprozess_belegungseinheit id_prozess = 5) /* select machine id process number 5*/ while @montag <= @weekend begin insert #alldays (days) select days = @montag set @tempdate= @montag set @montag = dateadd(day, 1,@tempdate) end /* machine name * weekdays date*/ create table #weekdays ( daydate datetime, anlage varchar(30) ) insert #weekdays (daydate, anlage) select d.days daydate , a.anl anlage #anlage_sieben cross join #alldays d /* select number of orders on particular machine week days*/ select case when aufträge_anzahl null '0' else aufträge_anzahl end #weeklysieben w right join #weekdays wd on w.anlage = wd.anlage , w.tag = wd.daydate wd.anlage in (select kurztext dbo.tblbelegungseinheit id = @id_beleg ) end c# code:
try { int a; string sql = "weekly"; sqldataadapter dp = new sqldataadapter(); sqlcommand c = null; sqldatareader r = null; dataset ds = new dataset(); datatable dt = new datatable(); //for creating 'raum' number of stacked column bars (int = 0; < raum; i++) { c = new sqlcommand(sql, conn); c.commandtype = commandtype.storedprocedure; c.parameters.add("@raumn", sqldbtype.int).value= (int)dblorders[i]; r = c.executereader(); dp.selectcommand = c; dp.fill(ds, "aufträge_anzahl"); dt = ds.tables["aufträge_anzahl"]; //for adding points stacked column bar (int j = 0; j < dt.rows.count; j++) { = convert.toint32(dt.rows[j]); chart1.series[seriesarray[j]].points.addy(a); } chart1.databind(); dtsr.clear(); } } catch (sqlexception ex) { console.writeline("sql error" + ex.message.tostring()); } dblorders double array holds ids different machines, raum number of total machines. code generates no error, not retrieve data in chart.
Comments
Post a Comment