Q: Updating

[Geoff]

Hi

This question has just occurred to me and I'm hoping somebody can help me.

Suppose I have a table in an Access database. I have access to this table
via a data adaptor i.e. I create a data adaptor to the table, create the
update, delete etc. commands using a command builder and then fill a dataset
with the table.

My question is, if I add a column to the tables in the dataset, and then do
an update, will the new column appear in the source table in the Access
database?

The reason I'm uncertain as to whether it would happen is because the
update, insert, delete etc. commands are generated before the new column has
been created. If this is the case, how can the table be updated with the new
column?

Hope somebody can clarify things for me?

Geoff

 

[Cor Ligthert [MVP]]

Geoff,

AFAIK uses the commandbuilder the columns it gets from the select.

Therefore only those columns will be updated.

There will never been build a table in your database by a adonet command by
the way, with the exception of an execute.nonquiry supplying a SQL Create or
Alter statement.

I hope this helps,

Cor

 

[Geoff]

Hi Cor

Do I understand you correctly i.e. there is now way to create a new column
in a table in a database via an update command to a data adaptor (by adding
a column to a table of a dataset and then doing the update)?

Geoff

 

[Cor Ligthert [MVP]]

Geoff,

No however doing it with SQL code is absolute no hell of a job using the
Alter Table Sql code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

And than process this with executenonquery.

\\\
dim conn as new sqlconnections(connectionstring)
dim cmd as new sqlcommand(sqlstring, conn)
open.conn
cmd.executenonquery
close.conn
///

I hope this helps,

Cor

 

[Geoff]

Hi Cor

Very interesting as usual. Thanks. However, this spurs me on to another,
related question:

Suppose you have two tables; not necessarily with the same fields. You can
load these tables into a dataset quite easily. You can even Merge the two
tables together, However, it appears that after merging the tables you
cannot upload the resulting table "back" to one of the original tables.

Is this correct?

Geoff

 

[Cor Ligthert [MVP]]

Geoff,

Suppose you have two tables; not necessarily with the same fields. You can
load these tables into a dataset quite easily. You can even Merge the two
tables together, However, it appears that after merging the tables you
cannot upload the resulting table "back" to one of the original tables.

Is this correct?

As long as that the columns are in the database table you can do everything
you want with the merged table as long that you create the right
DeleteCommand, InsertCommand and UpdateCommand in the DataAdapter.

http://msdn.microsoft.com/library/d...emdatasqlclientsqldataadaptermemberstopic.asp

I hope that this is what you mean?

Cor

 

[Geoff]

Yes, thanks so much Cor.

Geoff

Geoff,

As long as that the columns are in the database table you can do
everything you want with the merged table as long that you create the
right DeleteCommand, InsertCommand and UpdateCommand in the DataAdapter.

http://msdn.microsoft.com/library/d...emdatasqlclientsqldataadaptermemberstopic.asp

I hope that this is what you mean?

Cor