创建DataRelation对象来表示数据库中的两个关系,代码如下:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text; //用于字符串处理
using System.Data.SqlClient; //用于数据处理等public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
UpdateDetailsGrid( ); DataSet ds = CreateDataSet( );
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind( ); //创建DataView,并将其绑定到GridView
DataView detailsView = new DataView(ds.Tables[1]);
DetailsGridView.DataSource = detailsView;
Session["DetailsView"] = detailsView;
DetailsGridView.DataBind( ); //将OrderRelationsGridView绑定到关系集合
OrderRelationsGridView.DataSource = ds.Relations;
OrderRelationsGridView.DataBind( );
}
}
//获取订单细节
public void OnSelectedIndexChangedHandler(object sender, EventArgs e)
{
UpdateDetailsGrid( );
} private void UpdateDetailsGrid( )
{
int index = GridView1.SelectedIndex;
if (index != -1)
{
//从GridView中获取订单号
DataKey key = GridView1.DataKeys[index];
int orderID = (int) key.Value;
DataView detailsView = (DataView)Session["detailsView"];
detailsView.RowFilter = "OrderID=" + orderID;
DetailsGridView.DataSource = detailsView;
DetailsGridView.DataBind( );
OrderDetailsPanel.Visible = true;
}
else
{
OrderDetailsPanel.Visible = false;
}
}
private DataSet CreateDataSet( )
{
//使用连接字符串连接到Orders数据库
string connectionString = "Data Source=CHENGPIN-FC27ED\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"; //创建连接对象,初始化连接字符串并打开连接
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); //创建Sqlcommand对象,并为其设置连接
System.Data.SqlClient.SqlCommand command=new System.Data.SqlClient.SqlCommand( );
command.Connection=connection; StringBuilder s = new StringBuilder("select OrderID,c.CompanyName,c.ContactName,");
s.Append(" c.ContactTitle,c.Phone,orderDate");
s.Append(" from orders o ");
s.Append("join customers c on c.CustomerID = o.CustomerID"); //为select语句设置命令文本
command.CommandText=s.ToString( ); //创建SqlDataAdapter,并设置其命令对象
//为bugs添加表映射
SqlDataAdapter dataAdapter=new SqlDataAdapter( );
dataAdapter.SelectCommand=command;
dataAdapter.TableMappings.Add("Table","Orders"); //创建DataSet,并使用DataAdapter填充
DataSet dataSet = new DataSet( );
dataAdapter.Fill(dataSet); //为订单创建第二个命令对象
System.Data.SqlClient.SqlCommand command2=new System.Data.SqlClient.SqlCommand( );
command2.Connection=connection; //确认添加了一个列,这样您可以为产品创建一个关系
StringBuilder s2=new StringBuilder("select od.OrderID,OrderDate,p.ProductID, ");
s.Append("ProductName,od.UnitPrice,Quantity");
s.Append(" from Orders o");
s.Append(" join [Order Details] od on o.orderid = od.orderid ");
s.Append(" join products p on p.productID = od.productid ");
command2.CommandText=s2.ToString( ); //创建第二个SqlDataAdapter
//添加命令和表映射,然后使用第二个SqlDataAdapter填充DataSet
SqlDataAdapter dataAdapter2=new SqlDataAdapter( );
dataAdapter2.SelectCommand=command2;
dataAdapter2.TableMappings.Add("Table","Order Details");
源错误行:
dataAdapter2.Fill(dataSet); //为产品创建第三个命令对象
System.Data.SqlClient.SqlCommand command3=new System.Data.SqlClient.SqlCommand( );
command3.Connection=connection; string strCommand3="Select ProductID,ProductName from Products";
command3.CommandText=strCommand3; //创建第三个SqlDataAdapter,然后添加命令和表映射
//使用第二个SqlDataAdapter填充DataSet
SqlDataAdapter dataAdapter3=new SqlDataAdapter( );
dataAdapter3.SelectCommand=command3;
dataAdapter3.TableMappings.Add("Table","Products");
dataAdapter3.Fill(dataSet); //声明DataRelation和DataColumn对象
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2; //为了创建关系,设置数据列
//该关系使用了OrderID,它位于Orders和Order Details之间
dataColumn1=dataSet.Tables["Orders"].Columns["OrderID"];
dataColumn2=dataSet.Tables["Order Details"].Columns["OrderID"]; dataRelation=new System.Data.DataRelation("OrdersToDetails",dataColumn1,dataColumn2); //为 DataSet设置新的DataRelation
dataSet.Relations.Add(dataRelation); //为了在Order Details 和Products之间创建关系,须重用DataColumn和DataRelation对象
dataColumn1=dataSet.Tables["Products"].Columns["ProductID"];
dataColumn2=dataSet.Tables["Order Details"].Columns["ProductID"];
dataRelation=new System.Data.DataRelation("ProductIDToName",dataColumn1,dataColumn2); //为DataSet添加关系
dataSet.Relations.Add(dataRelation); return dataSet;
} //CreateDataSet方法结束} //类结束
错误信息:',' 附近有语法错误。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: ',' 附近有语法错误。源错误:
行 110: dataAdapter2.SelectCommand=command2;
行 111: dataAdapter2.TableMappings.Add("Table","Order Details");
行 112: dataAdapter2.Fill(dataSet);
行 113:
行 114: //为产品创建第三个命令对象
源文件: d:\My Documents\Visual Studio 2005\WebSites\StoredProcedures\Default.aspx.cs 行: 112
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text; //用于字符串处理
using System.Data.SqlClient; //用于数据处理等public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
UpdateDetailsGrid( ); DataSet ds = CreateDataSet( );
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind( ); //创建DataView,并将其绑定到GridView
DataView detailsView = new DataView(ds.Tables[1]);
DetailsGridView.DataSource = detailsView;
Session["DetailsView"] = detailsView;
DetailsGridView.DataBind( ); //将OrderRelationsGridView绑定到关系集合
OrderRelationsGridView.DataSource = ds.Relations;
OrderRelationsGridView.DataBind( );
}
}
//获取订单细节
public void OnSelectedIndexChangedHandler(object sender, EventArgs e)
{
UpdateDetailsGrid( );
} private void UpdateDetailsGrid( )
{
int index = GridView1.SelectedIndex;
if (index != -1)
{
//从GridView中获取订单号
DataKey key = GridView1.DataKeys[index];
int orderID = (int) key.Value;
DataView detailsView = (DataView)Session["detailsView"];
detailsView.RowFilter = "OrderID=" + orderID;
DetailsGridView.DataSource = detailsView;
DetailsGridView.DataBind( );
OrderDetailsPanel.Visible = true;
}
else
{
OrderDetailsPanel.Visible = false;
}
}
private DataSet CreateDataSet( )
{
//使用连接字符串连接到Orders数据库
string connectionString = "Data Source=CHENGPIN-FC27ED\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"; //创建连接对象,初始化连接字符串并打开连接
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open( ); //创建Sqlcommand对象,并为其设置连接
System.Data.SqlClient.SqlCommand command=new System.Data.SqlClient.SqlCommand( );
command.Connection=connection; StringBuilder s = new StringBuilder("select OrderID,c.CompanyName,c.ContactName,");
s.Append(" c.ContactTitle,c.Phone,orderDate");
s.Append(" from orders o ");
s.Append("join customers c on c.CustomerID = o.CustomerID"); //为select语句设置命令文本
command.CommandText=s.ToString( ); //创建SqlDataAdapter,并设置其命令对象
//为bugs添加表映射
SqlDataAdapter dataAdapter=new SqlDataAdapter( );
dataAdapter.SelectCommand=command;
dataAdapter.TableMappings.Add("Table","Orders"); //创建DataSet,并使用DataAdapter填充
DataSet dataSet = new DataSet( );
dataAdapter.Fill(dataSet); //为订单创建第二个命令对象
System.Data.SqlClient.SqlCommand command2=new System.Data.SqlClient.SqlCommand( );
command2.Connection=connection; //确认添加了一个列,这样您可以为产品创建一个关系
StringBuilder s2=new StringBuilder("select od.OrderID,OrderDate,p.ProductID, ");
s.Append("ProductName,od.UnitPrice,Quantity");
s.Append(" from Orders o");
s.Append(" join [Order Details] od on o.orderid = od.orderid ");
s.Append(" join products p on p.productID = od.productid ");
command2.CommandText=s2.ToString( ); //创建第二个SqlDataAdapter
//添加命令和表映射,然后使用第二个SqlDataAdapter填充DataSet
SqlDataAdapter dataAdapter2=new SqlDataAdapter( );
dataAdapter2.SelectCommand=command2;
dataAdapter2.TableMappings.Add("Table","Order Details");
源错误行:
dataAdapter2.Fill(dataSet); //为产品创建第三个命令对象
System.Data.SqlClient.SqlCommand command3=new System.Data.SqlClient.SqlCommand( );
command3.Connection=connection; string strCommand3="Select ProductID,ProductName from Products";
command3.CommandText=strCommand3; //创建第三个SqlDataAdapter,然后添加命令和表映射
//使用第二个SqlDataAdapter填充DataSet
SqlDataAdapter dataAdapter3=new SqlDataAdapter( );
dataAdapter3.SelectCommand=command3;
dataAdapter3.TableMappings.Add("Table","Products");
dataAdapter3.Fill(dataSet); //声明DataRelation和DataColumn对象
System.Data.DataRelation dataRelation;
System.Data.DataColumn dataColumn1;
System.Data.DataColumn dataColumn2; //为了创建关系,设置数据列
//该关系使用了OrderID,它位于Orders和Order Details之间
dataColumn1=dataSet.Tables["Orders"].Columns["OrderID"];
dataColumn2=dataSet.Tables["Order Details"].Columns["OrderID"]; dataRelation=new System.Data.DataRelation("OrdersToDetails",dataColumn1,dataColumn2); //为 DataSet设置新的DataRelation
dataSet.Relations.Add(dataRelation); //为了在Order Details 和Products之间创建关系,须重用DataColumn和DataRelation对象
dataColumn1=dataSet.Tables["Products"].Columns["ProductID"];
dataColumn2=dataSet.Tables["Order Details"].Columns["ProductID"];
dataRelation=new System.Data.DataRelation("ProductIDToName",dataColumn1,dataColumn2); //为DataSet添加关系
dataSet.Relations.Add(dataRelation); return dataSet;
} //CreateDataSet方法结束} //类结束
错误信息:',' 附近有语法错误。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: ',' 附近有语法错误。源错误:
行 110: dataAdapter2.SelectCommand=command2;
行 111: dataAdapter2.TableMappings.Add("Table","Order Details");
行 112: dataAdapter2.Fill(dataSet);
行 113:
行 114: //为产品创建第三个命令对象
源文件: d:\My Documents\Visual Studio 2005\WebSites\StoredProcedures\Default.aspx.cs 行: 112
s.Append("ProductName,od.UnitPrice,Quantity");
s.Append(" from Orders o");
s.Append(" join [Order Details] od on o.orderid = od.orderid ");
s.Append(" join products p on p.productID = od.productid ");
command2.CommandText=s2.ToString( );
改为StringBuilder s2=new StringBuilder();
s2.Append("select od.OrderID,OrderDate,p.ProductID, ");
s2.Append("ProductName,od.UnitPrice,Quantity");
s2.Append(" from Orders o");
s2.Append(" join [Order Details] od on o.orderid = od.orderid ");
s2.Append(" join products p on p.productID = od.productid ");
command2.CommandText=s2.ToString( );
ProductName和Quantity是哪个表的字段不明确?
2.
s.Append(" join [Order Details] od on o.orderid = od.orderid ");
s.Append(" join products p on p.productID = od.productid ");
==>
s.Append(" left join [Order Details] od on o.orderid = od.orderid ");
s.Append(" left join products p on p.productID = od.productid ");
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.InvalidOperationException: ExecuteReader: CommandText 属性尚未初始化
1. s.Append("ProductName,od.UnitPrice,Quantity");
ProductName和Quantity是哪个表的字段不明确?在楼主的SQL语句中不指定表别名是没有错误的
最简单的 SQL 语法错误,这问题也拿出来问,简直就是在给程序员丢脸!////////////////////////////////////////
这位仁兄说话是否过火了?
你说下楼主的SQL语句哪里有问题了?
只是楼主把s2错写为s了,难道你就从没犯过低级错误了?
s2的值为select od.OrderID,OrderDate,p.ProductID,后面的接不上了
最简单的语法错误,请各位指点~
StringBuilder s2=new StringBuilder("select od.OrderID,OrderDate,p.ProductID, ");
s.Append("ProductName,od.UnitPrice,Quantity");s2和s是两个变量,怎能接得上呢?sbqcel(活死人 ━═☆ 吊死在一棵树上的猪!) 正解