[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

ODBC .NET/Oracle DataSet Updates

(Vishy)

7/31/2002 2:00:00 AM

I display Oracle 8 data in an ASP.NET DataGrid server control, using
the ODBC.NET Data Provider. I use a separate Web Form with a single
direct OdbcCommand to insert new rows in my table successfully.
However, whenever I make changes to the DataGrid (and the DataSet
backing it), such as edit or delete a row, the DataSet changes are
never reflected in the database. There are no exceptions thrown and
everything seems to run fine, but the data in the data store remains
unaffected.

Here's my code:

DataSet ds;
OdbcDataAdapter da;
OdbcCommandBuilder cmdBuilder;

//...

private void DataGrid1_UpdateCommand(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
ds = (DataSet) Session["DataSet"];
da = (OdbcDataAdapter) Session["DataAdapter"];

// All columns [COL1-COLN] in table1 & table2
// are displayed and modifiable. COL1 is a primary key column
da.SelectCommand = new OdbcCommand("SELECT * FROM table1, table2");
cmdBuilder = new OdbcCommandBuilder(da);

DataGrid1.DataSource = ds;

// Updating rows in DataSet
// Locate DataSet row corresponding to DataGrid item
DataRow drChanged = ds.Tables[0].Rows[e.Item.DataSetIndex];

// Assign values from the datagrid to the datarow in the dataset
drChanged.BeginEdit();

drChanged["COL1"]=((TextBox)e.Item.Cells[1].Controls[0]).Text;
// similarly, change COL2 through COLN

drChanged.AcceptChanges();

// Sync DataSet with the data source
da.Update(ds);

// Put the row out of edit mode and rebind to data source
DataGrid1.EditItemIndex = -1;
DataGrid1.DataBind();
}

Any advice would be much appreciated. I have spent endless hours on
this already.

Thanks,
Vishy.
3 Answers

Bob Beauchemin

7/31/2002 2:14:00 AM

0

Hi Vishy,

Don't call AcceptChanges before calling Update. The DataSet is keeping
"before and after" images (for lack of a better term) for all the rows that
have been changed. AcceptChanges says "I don't care about updating a
database, apply the updates to the DataSet only". Therefore, when you call
Update, there are no "changed" rows in the DataSet to flush to the database.

Update actually calls DataSet.AcceptChanges (if all goes well) as the *last*
thing it does.

I'm curious as to why you are using ODBC.NET with Oracle ODBC instead of a
native .NET data provider for Oracle. There's at least three to choose from
now. ;-). Which ODBC driver are you using?

Cheers,
Bob Beauchemin
http://staff.develo...


"Vishy" <vishythewizkid@hotmail.com> wrote in message
news:c9ab0a55.0207301441.3933c8a0@posting.google.com...
> I display Oracle 8 data in an ASP.NET DataGrid server control, using
> the ODBC.NET Data Provider. I use a separate Web Form with a single
> direct OdbcCommand to insert new rows in my table successfully.
> However, whenever I make changes to the DataGrid (and the DataSet
> backing it), such as edit or delete a row, the DataSet changes are
> never reflected in the database. There are no exceptions thrown and
> everything seems to run fine, but the data in the data store remains
> unaffected.
>
> Here's my code:
>
> DataSet ds;
> OdbcDataAdapter da;
> OdbcCommandBuilder cmdBuilder;
>
> //...
>
> private void DataGrid1_UpdateCommand(object source,
> System.Web.UI.WebControls.DataGridCommandEventArgs e)
> {
> ds = (DataSet) Session["DataSet"];
> da = (OdbcDataAdapter) Session["DataAdapter"];
>
> // All columns [COL1-COLN] in table1 & table2
> // are displayed and modifiable. COL1 is a primary key column
> da.SelectCommand = new OdbcCommand("SELECT * FROM table1, table2");
> cmdBuilder = new OdbcCommandBuilder(da);
>
> DataGrid1.DataSource = ds;
>
> // Updating rows in DataSet
> // Locate DataSet row corresponding to DataGrid item
> DataRow drChanged = ds.Tables[0].Rows[e.Item.DataSetIndex];
>
> // Assign values from the datagrid to the datarow in the dataset
> drChanged.BeginEdit();
>
> drChanged["COL1"]=((TextBox)e.Item.Cells[1].Controls[0]).Text;
> // similarly, change COL2 through COLN
>
> drChanged.AcceptChanges();
>
> // Sync DataSet with the data source
> da.Update(ds);
>
> // Put the row out of edit mode and rebind to data source
> DataGrid1.EditItemIndex = -1;
> DataGrid1.DataBind();
> }
>
> Any advice would be much appreciated. I have spent endless hours on
> this already.
>
> Thanks,
> Vishy.

Bob Beauchemin

7/31/2002 11:03:00 PM

0

Looks like in your select statement there are two tables, the CommandBuilder
only supports updating against a command that returns data from a single
table. But what the message really means is that the ODBC driver doesn't
support returning the appropriate metadata that the CommandBuilder is
looking for.

You may have to use a different ODBC driver or an OLE DB provider. I posted
a whitepaper on this (and other) issues called "Migrating Data Access Code
to ADO.NET" at my website below.

Bob Beauchemin
http://staff.develo...

"Vishy" <vishythewizkid@hotmail.com> wrote in message
news:c9ab0a55.0207310958.3b975aa8@posting.google.com...
> Hi Bob,
>
> Thanks - my DataAdapter now *recognizes* that there are changed rows
> and tries to go about updating it. In this respect, you suggestion
> seems to have worked. But now I run into different problems.
>
> At the Update statement an InvalidOperationException is thrown:
> "Dynamic SQL generation is not supported against a SelectCommand that
> does not return any base table information." My code is still attached
> below. I am pretty sure there is a primary key column in my table, so
> I don't think that's the reason for this exception. I also tried
> assigning to DataAdapter.SelectCommand.CommandText the original SELECT
> statement which I used to generate the DataGrid, but that didn't
> change anything.
>
> I am aware through
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q3...
> [MS KB Article] that certain .NET Data Providers may not work properly
> with the .NET Framework and throw this exception. I am using the ODBC
> .NET Data Provider and I'd like to continue using it because we are
> trying to keep our patchwork of databases interoperable. But if there
> is no way around this exception, I will switch .NET Data Providers to
> either the MS ODBC one specifically for Oracle or the native one from
> Oracle.
>
> Any further ideas why this exception might be occurring?
>
> Thanks,
> Vishy.
>
>
> "Bob Beauchemin" <no_bobb_spam@develop.com> wrote in message
news:<OwiA2cCOCHA.2664@tkmsftngp11>...
> > Hi Vishy,
> >
> > Don't call AcceptChanges before calling Update. The DataSet is keeping
> > "before and after" images (for lack of a better term) for all the rows
that
> > have been changed. AcceptChanges says "I don't care about updating a
> > database, apply the updates to the DataSet only". Therefore, when you
call
> > Update, there are no "changed" rows in the DataSet to flush to the
database.
> >
> > Update actually calls DataSet.AcceptChanges (if all goes well) as the
*last*
> > thing it does.
> >
> > I'm curious as to why you are using ODBC.NET with Oracle ODBC instead of
a
> > native .NET data provider for Oracle. There's at least three to choose
from
> > now. ;-). Which ODBC driver are you using?
> >
> > Cheers,
> > Bob Beauchemin
> > http://staff.develo...
> >
> >
> > > Here's my code:
> > >
> > > DataSet ds;
> > > OdbcDataAdapter da;
> > > OdbcCommandBuilder cmdBuilder;
> > >
> > > //...
> > >
> > > private void DataGrid1_UpdateCommand(object source,
> > > System.Web.UI.WebControls.DataGridCommandEventArgs e)
> > > {
> > > ds = (DataSet) Session["DataSet"];
> > > da = (OdbcDataAdapter) Session["DataAdapter"];
> > > oC = (OdbcConnection) Session["OdbcConnection"];
> > >
> > > // All columns [COL1-COLN] in table1 & table2
> > > // are displayed and modifiable. COL1 is a primary key column
> > > da.SelectCommand = new OdbcCommand("SELECT * FROM table1, table2",
oC);
> > > cmdBuilder = new OdbcCommandBuilder(da);
> > >
> > > DataGrid1.DataSource = ds;
> > >
> > > // Updating rows in DataSet
> > > // Locate DataSet row corresponding to DataGrid item
> > > DataRow drChanged = ds.Tables[0].Rows[e.Item.DataSetIndex];
> > >
> > > // Assign values from the datagrid to the datarow in the dataset
> > > drChanged.BeginEdit();
> > >
> > > drChanged["COL1"]=((TextBox)e.Item.Cells[1].Controls[0]).Text;
> > > // similarly, change COL2 through COLN
> > >
> > > drChanged.AcceptChanges();
> > >
> > > // Sync DataSet with the data source
> > > da.Update(ds);
> > >
> > > // Put the row out of edit mode and rebind to data source
> > > DataGrid1.EditItemIndex = -1;
> > > DataGrid1.DataBind();
> > > }
> > >
> > > Any advice would be much appreciated. I have spent endless hours on
> > > this already.
> > >
> > > Thanks,
> > > Vishy.

(Vishy)

8/1/2002 2:02:00 AM

0

Hi Bob,

Thanks - my DataAdapter now *recognizes* that there are changed rows
and tries to go about updating it. In this respect, you suggestion
seems to have worked. But now I run into different problems.

At the Update statement an InvalidOperationException is thrown:
"Dynamic SQL generation is not supported against a SelectCommand that
does not return any base table information." My code is still attached
below. I am pretty sure there is a primary key column in my table, so
I don't think that's the reason for this exception. I also tried
assigning to DataAdapter.SelectCommand.CommandText the original SELECT
statement which I used to generate the DataGrid, but that didn't
change anything.

I am aware through http://support.microsoft.com/default.aspx?scid=KB;EN-US;q3...
[MS KB Article] that certain .NET Data Providers may not work properly
with the .NET Framework and throw this exception. I am using the ODBC
.NET Data Provider and I'd like to continue using it because we are
trying to keep our patchwork of databases interoperable. But if there
is no way around this exception, I will switch .NET Data Providers to
either the MS ODBC one specifically for Oracle or the native one from
Oracle.

Any further ideas why this exception might be occurring?

Thanks,
Vishy.


"Bob Beauchemin" <no_bobb_spam@develop.com> wrote in message news:<OwiA2cCOCHA.2664@tkmsftngp11>...
> Hi Vishy,
>
> Don't call AcceptChanges before calling Update. The DataSet is keeping
> "before and after" images (for lack of a better term) for all the rows that
> have been changed. AcceptChanges says "I don't care about updating a
> database, apply the updates to the DataSet only". Therefore, when you call
> Update, there are no "changed" rows in the DataSet to flush to the database.
>
> Update actually calls DataSet.AcceptChanges (if all goes well) as the *last*
> thing it does.
>
> I'm curious as to why you are using ODBC.NET with Oracle ODBC instead of a
> native .NET data provider for Oracle. There's at least three to choose from
> now. ;-). Which ODBC driver are you using?
>
> Cheers,
> Bob Beauchemin
> http://staff.develo...
>
>
> > Here's my code:
> >
> > DataSet ds;
> > OdbcDataAdapter da;
> > OdbcCommandBuilder cmdBuilder;
> >
> > //...
> >
> > private void DataGrid1_UpdateCommand(object source,
> > System.Web.UI.WebControls.DataGridCommandEventArgs e)
> > {
> > ds = (DataSet) Session["DataSet"];
> > da = (OdbcDataAdapter) Session["DataAdapter"];
> > oC = (OdbcConnection) Session["OdbcConnection"];
> >
> > // All columns [COL1-COLN] in table1 & table2
> > // are displayed and modifiable. COL1 is a primary key column
> > da.SelectCommand = new OdbcCommand("SELECT * FROM table1, table2", oC);
> > cmdBuilder = new OdbcCommandBuilder(da);
> >
> > DataGrid1.DataSource = ds;
> >
> > // Updating rows in DataSet
> > // Locate DataSet row corresponding to DataGrid item
> > DataRow drChanged = ds.Tables[0].Rows[e.Item.DataSetIndex];
> >
> > // Assign values from the datagrid to the datarow in the dataset
> > drChanged.BeginEdit();
> >
> > drChanged["COL1"]=((TextBox)e.Item.Cells[1].Controls[0]).Text;
> > // similarly, change COL2 through COLN
> >
> > drChanged.AcceptChanges();
> >
> > // Sync DataSet with the data source
> > da.Update(ds);
> >
> > // Put the row out of edit mode and rebind to data source
> > DataGrid1.EditItemIndex = -1;
> > DataGrid1.DataBind();
> > }
> >
> > Any advice would be much appreciated. I have spent endless hours on
> > this already.
> >
> > Thanks,
> > Vishy.