SQL table update question in C#

[Wing]

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

 

[Wiebe Tijsma]

It's possible, but I'd try to avoid update/delete statements against
views to prevent.

Instead try to use 2 DataAdapters to perform the updating/deleting, or
even 3 Adapters, 1 for selecting and 2 others to perform the modification.

Also, if the amount of records are limited, you can add relations to the
dataset instead of creating the relation with a Sql statement...

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