Data set question

[EMW]

I have two datasets:

dataset1 is filled with the contents of the database, which is closed
afterwards, and then the dataset is changed.
dataset2 is filled with the contents of the database.

Now I want to update dataset2 with the changes in dataset1.

I've tried : dataset2 = dataset1.copy

but that generates an error.

How can I update to the database after it has been closed and re-opened
again?

rg,
Eric

 

[Cor]

Hi EMW,

Definitly not the copy, if the schemas are the same than you can add the
rows with the dataset merge but for that the key has not to be the same
(gives a concurrency error).

Maybe the "remove" is usable to remove the existing rows in dataset2 first
and to do that merge.

(I asume you have got the changes in dataset 1 with "dataset1.getchanges")

But I think that itterating is the simplest way to go.

(And you do not have to itteratate thru the items, you can copy the
"itemarray")

Cor

 

[Gerry O'Brien [MVP]]

Have you looked into the DataDiff option?

 

[EMW]

Thanks,

I've also tried the merge like:

dataset2.clear
dataset2.merge(dataset1)

when I look in the dataset I see everything is present including the
changes, but the update command stil stops on an error.


please explain a little more about itterating?

rg,
Eric

 

[EMW]

I will now! Thanks!

rg,
Eric

Have you looked into the DataDiff option?

 

[EMW]

where is it part off, because vsnet search doesn't find it...

 

[EMW]

The error message I get:

Additional information: Dynamische SQL-generatie voor UpdateCommand wordt
niet ondersteund op basis van een SelectCommand die geen informatie over
sleutelkolommen retourneert.

translated:

Additional information: Dynamic SQL generation for UpdateCommand is not
supported on the basis of a SelectCommand that does not provide information
on key columns.

I don't know what this means..

rg,
Eric

 

[EMW]

Ok, got it. iteration is jus a step-by-step method.

 

[Cor]

Hi EMW,

I started to examine your problem again

And I do not understand why you do this, maybe you can explain it a little
bit more.

How can I update to the database after it has been closed and re-opened

You can always use dataset1 for that, but when you go do things with it you
will probably get constraints errors.

That key problem is easy to overcome by the way.

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
This is for that.

So tell something more?

Cor

 

[Jay B. Harlow [MVP - Outlook]]

EMW,
Are you wanting to update the database or a second dataset?

Now I want to update dataset2 with the changes in dataset1.

You need to use DataSet.GetChanges following by DataSet.Merge. Note if you
are calling DataSet.AcceptChanges in the mix you will loose your actual
changes...

How can I update to the database after it has been closed and re-opened
again?

Create a DataAdapter with the respective Insert, Update, and Delete Commands
and call DataAdapter.Update with your dataset. Note if you are calling
DataSet.AcceptChanges in the mix you will loose your actual changes...

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers using both of the above methods in detail.

David's book is both a good tutorial on ADO.NET plus a good desk reference
once you know ADO.NET!

Hope this helps
Jay

 

[Jay B. Harlow [MVP - Outlook]]

EMW,
You used a Select statement that ADO.NET cannot use to figure out the
Primary key of the table the select is over (for example you used a join).

Have you considered explicitly creating the UpdateCommand with the correct
syntax?

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers creating SelectCommand that allow creation of the UpdateCommand, plus
info on why you should explicitly create the UpdateCommand.

David's book is both a good tutorial on ADO.NET plus a good desk reference
once you know ADO.NET!

Hope this helps
Jay

 

[EMW]

I want to update the SQL database.
Dataset2 is linked to the database with the FILL command, so then I want to
write the changes into that dataset and then do an Update.

I think I'm going to do the folowing:

get the datarow that has changed, then look up (find) the matching datarow
in the linked dataset and then copy the changed cells. After that go to the
next datarow...etc...

It is probably not the fastes way to do it, but it should do the trick. It's
only about 4000 rows to look in...

thanks!.
Eric

 

[EMW]

I don't want to keep the database open for a long time.
The dataset is connected to a datagrid and people can fill in some extra
info.

I'm looking for a fast way to update the database.
I don't think I can just connect to the database and do an Update without
some filling first....but if it is possible.....

So basicly I have a procedure with the changed dataset as an argument and
inside the procedure I want to update the sql database. I like all my
database 'actions' to be put in one single module instead of everywhere in
the program. To keep the programsize as small as possible.

Is it more clear now?

rg,
Eric

 

[Jay B. Harlow [MVP - Outlook]]

EMW,

I think I'm going to do the folowing:

Why??

The DataAdapter already does this for you, by constructing a DataAdapter
(SqlDataAdapter, OracleDataAdapter, ODBCDataAdabter, or OleDbDataAdapter)
setting its UpdateCommand, DeleteCommand, InsertCOmmand properties with the
correct SQL Statements, then calling its Update method. The DataAdapter
itself will "get the datarow that has changed, then look up (find) the
match..."

Datasets are not "linked" to a database! The contain a copy of the data in a
database when you call Fill, any changes you make are then copied back to
the Database when you do the Update. With minor effort you can actually use
a DataSet copy copy records from one Database to a second Database.

I really do not see what you need the Dataset2 for! After I used fill on a
dataset, I would simply use that dataset.

Can you better explain what you are using it for?

Hope this helps
Jay

I want to update the SQL database.
Dataset2 is linked to the database with the FILL command, so then I want to
write the changes into that dataset and then do an Update.

I think I'm going to do the folowing:

get the datarow that has changed, then look up (find) the matching datarow
in the linked dataset and then copy the changed cells. After that go to the
next datarow...etc...

It is probably not the fastes way to do it, but it should do the trick. It's
only about 4000 rows to look in...

thanks!.
Eric



"Jay B. Harlow [MVP - Outlook]" <[email protected]> schreef in bericht

EMW,
Are you wanting to update the database or a second dataset?

You need to use DataSet.GetChanges following by DataSet.Merge. Note if you
are calling DataSet.AcceptChanges in the mix you will loose your actual
changes...

Create a DataAdapter with the respective Insert, Update, and Delete Commands
and call DataAdapter.Update with your dataset. Note if you are calling
DataSet.AcceptChanges in the mix you will loose your actual changes...

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers using both of the above methods in detail.

David's book is both a good tutorial on ADO.NET plus a good desk reference
once you know ADO.NET!

Hope this helps
Jay

 

[Jay B. Harlow [MVP - Outlook]]

EMW,

I don't want to keep the database open for a long time.
The dataset is connected to a datagrid and people can fill in some extra
info.

Then don't! You can open & close your Connection only when you do the
DataAdapter.Fill or DataAdapter.Update.

I'm looking for a fast way to update the database.

DataAdapter.Update on a single DataSet is a fast way to update a database. I
would expect getting a second DataSet involved would slow things down (as
you now have 2 datasets & you are coping data).

I don't think I can just connect to the database and do an Update without
some filling first....but if it is possible.....

You statement is how ADO.NET is designed:

1. Create DataSet ds
2. Connect
3. DataAdapter.Fill ds
4. Disconnect

work with dataset, making changes, additions, deletions

5. Connect
6. DataAdpater.Update ds
7. Disconnet

Again Sceppa's book covers all this in great detail.

So basicly I have a procedure with the changed dataset as an argument and
inside the procedure I want to update the sql database.

That sounds normal ADO.NET. The dataset you pass to the procedure is the
exact same one you would pass to the DataAdapter.Update.

I like all my
database 'actions' to be put in one single module instead of everywhere in
the program. To keep the programsize as small as possible.

Normally I put all my "actions" in a Data Component (Project - Add
Component) that has my Connections, Commands, and DataAdapters on it. I then
have two methods: Fill & Update that call Fill & Update on each contained
DataAdapter. This way the procedure above will call the Update on my "Data
Component". The procedure would either create a new instance of the Data
Component or the DataComponent would be a singleton or a property on a
second higher level class...

I use a Component as it allows me to visually design my Connections,
Commands, and DataAdapters.

Remember that you do not need to use the same DataAdpater that Filled a
DataSet to Update the same Dataset.

Hope this helps
Jay

 

[Gerry O'Brien [MVP]]

Sorry, wrong term.

I meant Diffgram.

DataDiff...... what was I thinking?????



--
Gerry O'Brien
Visual Developer .NET MVP

 

[EMW]

Actually it is now clear to me.
I didn't think one could use an Update without using the Fill, so indeed I
don't need the second dataset anymore.

thank you for your help!
rg,
Eric

EMW,

I think I'm going to do the folowing:

Why??

The DataAdapter already does this for you, by constructing a DataAdapter
(SqlDataAdapter, OracleDataAdapter, ODBCDataAdabter, or OleDbDataAdapter)
setting its UpdateCommand, DeleteCommand, InsertCOmmand properties with the
correct SQL Statements, then calling its Update method. The DataAdapter
itself will "get the datarow that has changed, then look up (find) the
match..."

Datasets are not "linked" to a database! The contain a copy of the data in a
database when you call Fill, any changes you make are then copied back to
the Database when you do the Update. With minor effort you can actually use
a DataSet copy copy records from one Database to a second Database.

I really do not see what you need the Dataset2 for! After I used fill on a
dataset, I would simply use that dataset.

Can you better explain what you are using it for?

Hope this helps
Jay

I want to update the SQL database.
Dataset2 is linked to the database with the FILL command, so then I want to
write the changes into that dataset and then do an Update.

I think I'm going to do the folowing:

get the datarow that has changed, then look up (find) the matching datarow
in the linked dataset and then copy the changed cells. After that go to the
next datarow...etc...

It is probably not the fastes way to do it, but it should do the trick. It's
only about 4000 rows to look in...

thanks!.
Eric



"Jay B. Harlow [MVP - Outlook]" <[email protected]> schreef in bericht

EMW,
Are you wanting to update the database or a second dataset?

Now I want to update dataset2 with the changes in dataset1.
You need to use DataSet.GetChanges following by DataSet.Merge. Note if you
are calling DataSet.AcceptChanges in the mix you will loose your actual
changes...

How can I update to the database after it has been closed and re-opened
again?
Create a DataAdapter with the respective Insert, Update, and Delete Commands
and call DataAdapter.Update with your dataset. Note if you are calling
DataSet.AcceptChanges in the mix you will loose your actual changes...

David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press
covers using both of the above methods in detail.

David's book is both a good tutorial on ADO.NET plus a good desk reference
once you know ADO.NET!

Hope this helps
Jay



I have two datasets:

dataset1 is filled with the contents of the database, which is closed
afterwards, and then the dataset is changed.
dataset2 is filled with the contents of the database.

Now I want to update dataset2 with the changes in dataset1.

I've tried : dataset2 = dataset1.copy

but that generates an error.

How can I update to the database after it has been closed and re-opened
again?

rg,
Eric

 

[EMW]

this is the code I use, but I still get that message:

con = New SqlClient.SqlConnection("Server=" + strServer + _
";database=" + strPMWOdatabase + _
";Integrated security=SSPI" + _
";Connect Timeout=" + strTimeOut)
Try
con.Open()
Catch ex As Exception
writeSiteGegevensUpdate = False
con = Nothing
Exit Function
End Try
strSql = "SELECT * FROM WOlist"
sqlAdapt = New SqlClient.SqlDataAdapter
sqlAdapt.SelectCommand = New SqlClient.SqlCommand(strSql, con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(sqlAdapt)
sqlAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
Try
sqlAdapt.Update(dsWO, "WOlist")
Catch ex As Exception
writeSiteGegevensUpdate = False
MsgBox(ex.Message + vbCrLf + ex.ToString)
con.Close()
Exit Function
End Try


this is the message:
Additional information: Dynamic SQL generation for UpdateCommand is not
supported on the basis of a SelectCommand that does not provide information
on key columns.


thanks.
Eric

 

[EMW]

keeps one searching....

Sorry, wrong term.

I meant Diffgram.

DataDiff...... what was I thinking?????

 

[Cor]

Hi EMW,

I don't want to keep the database open for a long time.
The dataset is connected to a datagrid and people can fill in some extra
info.

Why should you, for a dataadapter, you even have not to open it, it opens
itself and closes it when it has done his work (if you have not open it
explicit).

I'm looking for a fast way to update the database.
I don't think I can just connect to the database and do an Update without
some filling first....but if it is possible.....

No, an update is always an update from an existing situation in the database
reflected in the dataset which has additions, updates and deletes which are
administrated in the rowstate.

So basicly I have a procedure with the changed dataset as an argument and
inside the procedure I want to update the sql database. I like all my
database 'actions' to be put in one single module instead of everywhere in
the program. To keep the programsize as small as possible.

This I understand, but why do you need need 2 dataset for that?

Cor