Rich,
Although "upsert" sounds kinda funny, I believe it is what you need -
DataSet columns store an original and a current value. PreserveChanges
will keep the current value intact while overwriting the original
value. Upsert does the opposite of this as it keeps the original value
intact while overwriting the current value.
Here is an example of when PreserveChanges might come in handy.
Suppose a user named Peggy has opened a screen and loaded a DataGrid
with customers from a DataSet. Peggy modifies the city of the customer
with CustomerID ALFKI from Berlin to New York, but doesn't click the
Save button. She then goes off for a cup of coffee. Meanwhile,
Katherine modifies the same customer's city from Berlin to Miami.
You'll now have a data concurrency issue if Peggy comes back from her
break and saves the record. So in this situation, the original value
for Peggy's customer record was Berlin and since she changed it to New
York the current value is New York. Meanwhile, in the database the
city is now Miami. If you want to reset the original values of Peggy's
DataSet to what is in the database, you could get the data from the
database into a DataTableReader and then load it into the DataSet
using LoadOptions.PreserveChanges.
[quoted text, click to view] On Mar 28, 6:18 pm, Rich <R...@discussions.microsoft.com> wrote:
> Well, I found the answer to my own question:
>
> ds.Tables("tbl2").Load(reader, LoadOption.Upsert)
>
> I have to include this optional argument:
>
> LoadOption.Upsert
>
> I was thinking that may have been a type like maybe the VS2005 team meant
> Insert (maybe they did mean that), but maybe they meant Update/Insert.
> Either way, if I add this argument to the Load Method, now the data transfers
> all the way to the sql server no problem.
>
>
>
> "Rich" wrote:
> > Hello,
>
> > I have to read data from an external file into a ado.net table
>
> > ds.tblExternal.ReadXml(...)
>
> > and I want to load this data into a table that resides in a sql server
> > (2000) DB. I can do this if I loop through ds.tblExternal and add new rows
> > to the ado.net table from the DB:
>
> > da.Fill(ds, "tblfromDB") '--table from sql DB
>
> > Dim dr1 As DataRow
> > For Each dr As DataRow in ds.tblExternal.Rows
> > dr1 = ds.Tables("tblFromDB").NewRow
> > For Each dc As DataColumn in ds.tblExternal.Columns
> > dr1(dc.ColumnName) = dr(dc.ColumnName)
> > Next
> > ds.Tables("tblFromDB").Rows.Add(dr1)
> > Next
> > da.Update(ds,"tblFromDB") '--table on sql server DB populates OK here
>
> > If I use a dataTableReader to load data from tblExternal to "tblFromDB" I
> > don't have to do Looping, and this eliminates a lot of lines of code. The
> > problem is that if I load "tblFromDB" using a dataTableReader, it wont
> > update/populate the table on the DB end.
>
> > Dim reader As DataTableReader = ds.tblExternal.CreateDataReader
> > ds.Tables("tblFromDB").Load(reader)
> > da.Update(ds,"tblFromDB") '--doesn't update table on the sql server DB
>
> > This won't populate the table on the DB like looping will, however, if I set
> > a datagrideview.datasource to ds.Tables("tblFromDB") after loading
> > "tblFromDB" using the dataTableReader, the datagridview will display data
> > from "tblFromDB". I am guessing that new rows were not added to "tblFromDB"
> > when loading data using the dataTableReader.
>
> > Does anyone know what to do to make the data from the dataTableReader able
> > to update/populate the table on the sql server end? The goal is to not have
> > to loop through tables.
>
> > Or --- is there a better way to read data from the external file into the
> > sql server table?
>
> > Thanks,
> > Rich- Hide quoted text -
>
> - Show quoted text -
