467,219 Members | 1,479 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,219 developers. It's quick & easy.

SQL table update question in C#

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
  • viewed: 2738
Share:
1 Reply
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.

Similar topics

2 posts views Thread by Mike Leahy | last post: by
5 posts views Thread by PAUL | last post: by
7 posts views Thread by Serge Rielau | last post: by
9 posts views Thread by Oonz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.