Hi all,
I have created 2 tables in sql database and join these 2 tables before
assign the result to the dataset, and display the result in datagrid.
Everything is fine up to this point. The problem come up when I want to
delete one of rows in datagrid and update the change to the
corresponding table.
The error msg show up "Dynamic SQL generation is not supported against
multiple base tables."
My code is showing below:
-------------------------------------------------
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace relation_test
{
public class WebForm1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlConnection thisConnection;
protected System.Data.SqlClient.SqlCommand photoRelation;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
private void Page_Load(object sender, System.EventArgs e)
{
if(!IsPostBack)
{
string found = "ae627ecb-a964-4672-a1f7-33261f450a6c";
SqlDataAdapter tableJoinAdapter = new SqlDataAdapter ("SELECT
ShoppingCart.RecordID,T_Photo.PhotoName,
ShoppingCart.Quantity, T_Photo.Price,
ShoppingCart.CartID FROM ShoppingCart INNER JOIN T_Photo ON
ShoppingCart.PhotoID = T_Photo.PhotoID
WHERE ShoppingCart.CartID = '"+found+"'", thisConnection);
SqlCommandBuilder thisBuilder = new
SqlCommandBuilder(tableJoinAdapter);
DataSet shoppingCartDataSet = new DataSet();
tableJoinAdapter.Fill(shoppingCartDataSet, "ShoppingCart");
DataColumn[] keys = new DataColumn[1];
keys[0] =
shoppingCartDataSet.Tables["ShoppingCart"].Columns["RecordID"];
shoppingCartDataSet.Tables["ShoppingCart"].PrimaryKey = keys;
DataGrid1.DataSource = shoppingCartDataSet;
DataGrid1.DataBind();
Session["source1"] = shoppingCartDataSet;
Session["source2"] = tableJoinAdapter;
}
}
private void DeleteButtonCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataSet pDataSet = (DataSet)Session["Source1"];
SqlDataAdapter pAdapter = (SqlDataAdapter)Session["Source2"];
string key = DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
DataRow row = pDataSet.Tables["ShoppingCart"].Rows.Find(key);
row.Delete();
pAdapter.Update(pDataSet.Tables["ShoppingCart"]); //error msg show
up after running this line
DataGrid1.DataSource = pDataSet;
DataGrid1.DataBind();
}
}
}
If this Update function can not handle what I want it to do, is there
any other way can update the corresponding table??
Thanks for your time.
wing
