如何在ASP.NET中使用sql将数据从数据库填充到不同的gridview中？ [英] How I can populate data from database into different gridview using sql in ASP.NET?
本文介绍了如何在ASP.NET中使用sql将数据从数据库填充到不同的gridview中？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是开发的代码，如果我正在处理任何事情，或者语法在任何地方出错，请不要理解它出错的地方。
我希望从同一个数据库中读取数据（来自两个表）然后它将填入不同的网格视图。
专家可以帮助我吗？
使用系统;
使用 System.Collections.Generic;
使用 System.Linq;
使用 System.Web;
使用 System.Web.UI;
使用 System.Web.UI.WebControls;
使用 System.Data.Sql;
使用 System.Data.SqlClient;
使用 System.Data;
使用 System.Collections;
命名空间 Final_PlannedVsActuals
{
public partial class PlannedVsActuals：System.Web.UI.Page
{
SqlCommand cmd = < span class =code-keyword> new SqlCommand（）;
SqlConnection con;
SqlDataAdapter da = new SqlDataAdapter（）;
int [] Planned_Hrs = new int [ 50 ];
int [] Actual_Hrs = new int [ 50 ];
int [] delta = new int [ 50 ];
DataTable dt = new DataTable（）;
受保护 void Page_Load（对象发件人，EventArgs e）
{
尝试
{
con = new SqlConnection（ @ 数据源= IN1W7D-300386;初始目录= MISIN; Integrated Security = True）;
con.Open（）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'Con open'） < /脚本>中）;
if （！IsPostBack）
{
Cal_WkDelta（）;
Show_Country（）;
}
}
catch （例外情况）
{Response.Write（ + ex）; }
}
private void Cal_WkDelta（）
{
尝试
{
GridView4.Visible = true ;
string wk，grd;
for （ int w = 1 ; w < = 5 ; w ++） / / it itrate周
{
wk = string .Concat （ Week _，w）;
for （ int count = 0 ; count< gridview4.rows.count; count ++）>
{
grd = Convert.ToString（GridView4.Rows [count] ）;
string s1 = DELETE FROM Daily_log_week ;
string s2 = 创建视图Daily_log_week as （SELECT [country]，[Monthc]，[yearc]，[sector]，[TIME_SPENT]，[datec]， +
（'Week'+'_'+（convert（nvarchar（10），（（（（day（datec）-1）/ 7）+1）））））as） Weekn +
FROM [MISIN]。[dbo]。[tbl_daily_log]其中sector ='SEA'或扇区='MEA'或扇区='NEA' +
GROUP BY country，Monthc，yearc，sector，TIME_SPENT，DATEc）;;
string s3 = DELETE FROM Daily_log_week ;
string s4 = 创建视图sector_week_actual_hours as SELECT [country]，[Monthc]，[yearc]，[sector]， +
Sum（[TIME_SPENT]）as Actual_Hrs，[weekn] FROM [MISIN]。[dbo]。[Daily_log_week] +
按国家/地区分组，Monthc，yearc，sector，weekn;;
SqlCommand cmd = new SqlCommand（s1，con）;
cmd.ExecuteNonQuery（）;
SqlCommand sqlcmd = new SqlCommand（s2，con）;
sqlcmd.ExecuteNonQuery（）;
SqlCommand scmd = new SqlCommand（s3，con）;
scmd.ExecuteNonQuery（）;
SqlCommand dlogcmd = new SqlCommand（s4，con）;
dlogcmd.ExecuteNonQuery（）;
string s5 = 选择Plnned_Hrs作为来自PlanHrs的p，其中Week_Number = wk和Country = grd;
SqlCommand spcmd = new SqlCommand（s5，con）;
SqlDataAdapter da = new SqlDataAdapter（spcmd）;
DataSet ds = new DataSet（）;
da.Fill（ds， plan）;
DataRow drow = ds.Tables [ 0 ]。行[count];
int pp = Convert.ToInt32（drow [ 1 ]）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'1'）< ; /脚本>中）;
// int pp = cmd.ExecuteNonQuery（）;
Planned_Hrs [count] = pp;
// g1.Rows [count] .Cells [Column1]。控制[03] = pp ;
string s2 = 从sector_week_actual_hours中选择TIME_SPENT，其中Weekn = wk和country = grd;
cmd = new SqlCommand（s2，con）;
da.Fill（ds， plan）;
drow = ds.Tables [ 0 ]。行[count];
int at = Convert.ToInt32（drow [ 1 ]）;
// int at = cmd.ExecuteNonQuery（）;
Actual_Hrs [count ] = at;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'8'）< ; /脚本>中）;
delta [count] = Planned_Hrs [count] - Actual_Hrs [count];
Response.Write（ week + w）;
dt.Rows [count] [ 0 ] = Planned_Hrs [count];
dt.Rows [count] [ 1 ] = Actual_Hrs [count];
dt.Rows [count] [ 2 ] = delta [count];
count ++;
}
如果（w == 1 ）
{
g1.Visible = true ;
g1.DataSource = dt;
g1.DataBind（）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'2'）< ; /脚本>中）;
}
else
if （w == 2 ）
{
g1.DataSource = dt;
g1.DataBind（）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'3'）< ; /脚本>中）;
}
else
if （w == 3 ）
{
g3.DataSource = dt;
g3.DataBind（）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'4'）< ; /脚本>中）;
}
else
if （w == 4 ）
{
g4.DataSource = dt;
g4.DataBind（）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'5'）< ; /脚本>中）;
}
else
{
g5.DataSource = dt;
g5.DataBind（）;
Response.Write（ < script LANGUAGE ='JavaScript'> alert（'6'）< ; /脚本>中）;
}
}
}
catch （例外情况）
{
Response.Write （ + ex）;
}
}
私有 void Show_Country（）
{
尝试
{
string sql1 = 从PlanHrs中选择DISTINCT国家;
cmd = new SqlCommand（sql1，con）;
da = new SqlDataAdapter（cmd）;
DataSet ds1 = new DataSet（）;
da.Fill（ds1， Country）;
GridView4.DataSource = ds1.Tables [ 0 ];
GridView4.DataBind（）;
}
catch （例外情况）
{
Response.Write（ + ex）;
}
}
}
}
解决方案
此代码非常糟糕，如果它是代码开发的，请考虑抛弃代码并从头开始只保留SQL。
随着代码的结构，我怀疑问题出在SQL（虽然我老实说没有调查），只需把它扔掉，然后按照这个简单的例子说明如何做正确的事。
http://www.aspsnippets.com/Articles/ASPNet-Nested-GridViews-GridView-inside -GridView-with-Expand-and-Collapse-feature.aspx [ ^ ]
并将SQL删除您的代码;）必须是服务器端或至少在数据管理员课程中，或者你（总是）处于一个痛苦的世界（如果不是现在，然后是后来），我们大多数人都放弃了ASP3中的这种方法，因为随着时间的推移，维护和它的成本比动态和速度更快给
This is code, developed, I dont if I am mising anything, or the syntax gone wrong anywhere, dont understand where its going wrong.
I wish to read the data from same database (from two tables) and then it will get filled into different gridviews.
Can experts help me?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
namespace Final_PlannedVsActuals
{
public partial class PlannedVsActuals : System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand();
SqlConnection con;
SqlDataAdapter da = new SqlDataAdapter();
int[] Planned_Hrs = new int[50];
int[] Actual_Hrs = new int[50];
int[] delta = new int[50];
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
try
{
con= new SqlConnection(@"Data Source=IN1W7D-300386;Initial Catalog=MISIN;Integrated Security=True");
con.Open();
Response.Write("<script LANGUAGE='JavaScript' >alert('Con open')</script>");
if (!IsPostBack)
{
Cal_WkDelta();
Show_Country();
}
}
catch(Exception ex)
{ Response.Write(""+ex); }
}
private void Cal_WkDelta()
{
try
{
GridView4.Visible = true;
string wk,grd;
for (int w =1;w <= 5;w++)//to itrate weeks
{
wk=string.Concat("Week_",w);
for(int count= 0;count<gridview4.rows.count;count++)>
{
grd=Convert.ToString(GridView4.Rows[count]);
string s1 = "DELETE FROM Daily_log_week";
string s2 = "create view Daily_log_week as (SELECT [country],[Monthc],[yearc],[sector],[TIME_SPENT],[datec]," +
"('Week'+'_'+(convert(nvarchar(10),((((day(datec)-1)/7)+1))))) as Weekn" +
"FROM [MISIN].[dbo].[tbl_daily_log] where sector = 'SEA' or sector = 'MEA' or sector = 'NEA'" +
"GROUP BY country, Monthc, yearc,sector, TIME_SPENT,DATEc);";
string s3 = "DELETE FROM Daily_log_week";
string s4 = "create view sector_week_actual_hours as SELECT [country],[Monthc],[yearc],[sector]," +
"Sum([TIME_SPENT]) as Actual_Hrs,[weekn] FROM [MISIN].[dbo].[Daily_log_week]" +
"group by country, Monthc, yearc,sector,weekn;";
SqlCommand cmd = new SqlCommand(s1, con);
cmd.ExecuteNonQuery();
SqlCommand sqlcmd = new SqlCommand(s2, con);
sqlcmd.ExecuteNonQuery();
SqlCommand scmd = new SqlCommand(s3, con);
scmd.ExecuteNonQuery();
SqlCommand dlogcmd = new SqlCommand(s4, con);
dlogcmd.ExecuteNonQuery();
string s5 = "select Plnned_Hrs as p from PlanHrs where Week_Number=wk and Country =grd";
SqlCommand spcmd = new SqlCommand(s5, con);
SqlDataAdapter da = new SqlDataAdapter(spcmd);
DataSet ds = new DataSet();
da.Fill(ds, "plan");
DataRow drow = ds.Tables[0].Rows[count];
int pp = Convert.ToInt32(drow[1]);
Response.Write("<script LANGUAGE='JavaScript' >alert('1')</script>");
//int pp=cmd.ExecuteNonQuery();
Planned_Hrs[count] = pp;
// g1.Rows[count].Cells["Column1"].Control[03]= pp;
string s2 = "select TIME_SPENT from sector_week_actual_hours where Weekn=wk and country =grd";
cmd = new SqlCommand(s2, con);
da.Fill(ds, "plan");
drow = ds.Tables[0].Rows[count];
int at = Convert.ToInt32(drow[1]);
//int at=cmd.ExecuteNonQuery();
Actual_Hrs[count] = at;
Response.Write("<script LANGUAGE='JavaScript' >alert('8')</script>");
delta[count] = Planned_Hrs[count] - Actual_Hrs[count];
Response.Write("week"+w);
dt.Rows[count][0] = Planned_Hrs[count];
dt.Rows[count][1] = Actual_Hrs[count];
dt.Rows[count][2] = delta[count];
count++;
}
if (w == 1)
{
g1.Visible = true;
g1.DataSource = dt;
g1.DataBind();
Response.Write("<script LANGUAGE='JavaScript' >alert('2')</script>");
}
else
if (w == 2)
{
g1.DataSource = dt;
g1.DataBind();
Response.Write("<script LANGUAGE='JavaScript' >alert('3')</script>");
}
else
if (w == 3)
{
g3.DataSource = dt;
g3.DataBind();
Response.Write("<script LANGUAGE='JavaScript' >alert('4')</script>");
}
else
if (w == 4)
{
g4.DataSource = dt;
g4.DataBind();
Response.Write("<script LANGUAGE='JavaScript' >alert('5')</script>");
}
else
{
g5.DataSource = dt;
g5.DataBind();
Response.Write("<script LANGUAGE='JavaScript' >alert('6')</script>");
}
}
}
catch (Exception ex)
{
Response.Write(""+ex);
}
}
private void Show_Country()
{
try
{
string sql1 = "select DISTINCT Country from PlanHrs ";
cmd = new SqlCommand(sql1,con);
da = new SqlDataAdapter(cmd);
DataSet ds1 = new DataSet();
da.Fill(ds1,"Country");
GridView4.DataSource = ds1.Tables[0];
GridView4.DataBind();
}
catch (Exception ex)
{
Response.Write("" + ex);
}
}
}
} 解决方案
This code is pretty awfull, if it is code developed, consider throwing the code behind away and starting from scratch only keeping the SQL.
With the structure of the code, i doubt the problem is with the SQL (although i honestly didn't investigate), just throw it all away and follow this simple example of how to do things right.
http://www.aspsnippets.com/Articles/ASPNet-Nested-GridViews-GridView-inside-GridView-with-Expand-and-Collapse-feature.aspx[^]
And get that SQL out of your code behind ;) must be server side or at least in datamanager classes or you're (always) in for a world of pain (if not now, then later) most of us abandoned that approach in ASP3 because over time, with maintenance and such it costed more than the dynamic and rapidity gave
这篇关于如何在ASP.NET中使用sql将数据从数据库填充到不同的gridview中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
