SQL table update question in C#

**Wiebe Tijsma** · Nov 17 '05, 05:30 AM

Re: SQL table update question in C#

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:[color=blue]
> 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.Collecti ons;
> using System.Componen tModel;
> using System.Data;
> using System.Drawing;
> using System.Web;
> using System.Web.Sess ionState;
> using System.Web.UI;
> using System.Web.UI.W ebControls;
> using System.Web.UI.H tmlControls;
> using System.Data.Sql Client;
>
> namespace relation_test
> {
> public class WebForm1 : System.Web.UI.P age
> {
> protected System.Data.Sql Client.SqlConne ction thisConnection;
> protected System.Data.Sql Client.SqlComma nd photoRelation;
> protected System.Web.UI.W ebControls.Data Grid DataGrid1;
>
> private void Page_Load(objec t sender, System.EventArg s e)
> {
> if(!IsPostBack)
> {
> string found = "ae627ecb-a964-4672-a1f7-33261f450a6c";
>
> SqlDataAdapter tableJoinAdapte r = new SqlDataAdapter ("SELECT
> ShoppingCart.Re cordID,T_Photo. PhotoName,
> ShoppingCart.Qu antity, T_Photo.Price,
> ShoppingCart.Ca rtID FROM ShoppingCart INNER JOIN T_Photo ON
> ShoppingCart.Ph otoID = T_Photo.PhotoID
> WHERE ShoppingCart.Ca rtID = '"+found+"'" , thisConnection) ;
>
> SqlCommandBuild er thisBuilder = new
> SqlCommandBuild er(tableJoinAda pter);
>
> DataSet shoppingCartDat aSet = new DataSet();
>
> tableJoinAdapte r.Fill(shopping CartDataSet, "ShoppingCart") ;
>
> DataColumn[] keys = new DataColumn[1];
> keys[0] =
> shoppingCartDat aSet.Tables["ShoppingCa rt"].Columns["RecordID"];
> shoppingCartDat aSet.Tables["ShoppingCa rt"].PrimaryKey = keys;
>
> DataGrid1.DataS ource = shoppingCartDat aSet;
> DataGrid1.DataB ind();
>
> Session["source1"] = shoppingCartDat aSet;
> Session["source2"] = tableJoinAdapte r;
> }
> }
>
> private void DeleteButtonCom mand(object source,
> System.Web.UI.W ebControls.Data GridCommandEven tArgs e)
> {
> DataSet pDataSet = (DataSet)Sessio n["Source1"];
> SqlDataAdapter pAdapter = (SqlDataAdapter )Session["Source2"];
> string key = DataGrid1.DataK eys[e.Item.ItemInde x].ToString();
> DataRow row = pDataSet.Tables["ShoppingCa rt"].Rows.Find(key) ;
> row.Delete();
> pAdapter.Update (pDataSet.Table s["ShoppingCa rt"]); //error msg show
> up after running this line
> DataGrid1.DataS ource = pDataSet;
> DataGrid1.DataB ind();
> }
> }
> }
>
> 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
>[/color]

SQL table update question in C#

SQL table update question in C#

Comment