By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

SQL table update question in C#

P: n/a
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

Nov 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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...
Wing wrote:
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

Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.