what am I doing wrong ?

[SLIMSHIM]

Hi,
I"m new to c# and .net. I wrote a small program to add rows to an access
table.
the program goes thru the motions but the data never gets there.
here is my code. I am intentionaly not using the form controls. I"m trying
to do it solely thru code. You can safely assume I have an access datsabase
with one table called who with three columns(fields).
where did i go wrong ?

OleDbConnection con;
OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);
con.Close();

thanx in advance for all your help

[Doug]

Just looking at it quickly, it looks like you're connection isn't open.
I haven't done much with the oledbconnection object but I think that's
part of your problem. It may not be all of it though.

[SLIMSHIM]

I added con.open()
it didn'y make any difference.
like I said.
I get no errors ! It completes the proccess. when I check the database there
is no data.

"Doug" wrote:

Just looking at it quickly, it looks like you're connection isn't open.
I haven't done much with the oledbconnection object but I think that's
part of your problem. It may not be all of it though.

[Cor Ligthert [MVP]]

Slimshin,

Your code is not complete to update, you can add this,

OleDbConnection con;

try
{

OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);

SQLCommandBuilder cmd = new SQLCommandBuilder();
cm = cmd(OleDBbDataAdapter);
OleDbDataAdapter.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finnaly
{
con.close;
}

Now you can see what goes wrong if that still happen (I have typed this
direct in this message and did a long time not use the commandbuilder).

As it is working you can refactor your code, by instance using "using" for
the connection does make it look nicer.

I hope this helps,

Cor

con.Close();

[SLIMSHIM]

I still get no errors.
why is it not ready to update?
doesn't the call do row.add do the update ?
as a side issue using the try catch con.close is out of scope genrating
compiler error.

"Cor Ligthert [MVP]" wrote:

Slimshin,

Your code is not complete to update, you can add this,

OleDbConnection con;

try
{

OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);

SQLCommandBuilder cmd = new SQLCommandBuilder();
cm = cmd(OleDBbDataAdapter);
OleDbDataAdapter.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finnaly
{
con.close;
}

Now you can see what goes wrong if that still happen (I have typed this
direct in this message and did a long time not use the commandbuilder).

As it is working you can refactor your code, by instance using "using" for
the connection does make it look nicer.

I hope this helps,

Cor

con.Close();

[Cor Ligthert [MVP]]

Out of scope while you have placed the declaration outside above the method,
strange

Cor

"SLIMSHIM" <SL******@discussions.microsoft.comschreef in bericht
news:1A**********************************@microsof t.com...

>I still get no errors.
why is it not ready to update?
doesn't the call do row.add do the update ?
as a side issue using the try catch con.close is out of scope genrating
compiler error.

"Cor Ligthert [MVP]" wrote:

>Slimshin,

Your code is not complete to update, you can add this,

OleDbConnection con;

try
{

OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);

SQLCommandBuilder cmd = new SQLCommandBuilder();
cm = cmd(OleDBbDataAdapter);
OleDbDataAdapter.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finnaly
{
con.close;
}

Now you can see what goes wrong if that still happen (I have typed this
direct in this message and did a long time not use the commandbuilder).

As it is working you can refactor your code, by instance using "using"
for
the connection does make it look nicer.

I hope this helps,

Cor

con.Close();

[ChrisM]

Hi,

If I understand correctly, you are expecting to see the new row appear in
your database table?

This code will not do that.
You are creating a dataset, filling it with data from your database, and
adding a new row to it. However, the DataSet is (by design) disconnected
from the database, and you are doing nothing to write the changed dataset
back to your database.
Your will need to write some more code to do this. One way is to give your
DataAdapter 'Insert', 'Update' and 'Delete' queries in addition to the
'Select' query you have already written for it. You can create these queries
by hand, or you can look at the CommandBuilder object.
You will also need to call the .Update method on your DataAdapter once you
have made the required changes.

Hope this helps.

Chris.

"SLIMSHIM" <SL******@discussions.microsoft.comwrote in message
news:EE**********************************@microsof t.com...

Hi,
I"m new to c# and .net. I wrote a small program to add rows to an access
table.
the program goes thru the motions but the data never gets there.
here is my code. I am intentionaly not using the form controls. I"m trying
to do it solely thru code. You can safely assume I have an access
datsabase
with one table called who with three columns(fields).
where did i go wrong ?

OleDbConnection con;
OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);
con.Close();

thanx in advance for all your help

[Dave Sexton]

Hi,

DataSets are "disconnected" from the data source that was used to fill them.
In other words, they are only in-memory representations of the data in your
data store.

To update the data store with the changes in the DataSet you can call the
Update method on your data adapter and pass it the DataSet.

In order for the data adapter to successfully update the database, you must
supply the command for the update.

As Cor mentioned, you should really construct "con" in a C# "using" statement
to ensure that the connection is closed in the case of an exception being
thrown. You don't want to leave connections open. (Especially not in Access,
which I believe is really restrictive in the amount of concurrency it
supports):

DataSet ds = new DataSet();

using (OleDbConnection conn = new OleDbConnection(
testacc.Properties.Settings.Default.mytestConnecti onString))
{
using (OleDbDataAdapter adp = new OleDbDataAdapter(
"SELECT * FROM who", conn))
{
// The following line isn't normally required.
// adp.Fill opens the connection and closes it before returning,
// but since we want it to remain open for the next
// operation (Update) we'll open it now.
// adp.Fill won't close it in this case.
conn.Open();

adp.Fill(ds, "who");

DataRow row = ds.Tables["who"].NewRow();

row["name"] = "Joe";
row["address"] = "1 csharp road :)";
row["age"] = 25;

ds.Tables[0].Rows.Add(row);

// in order to update the database we must tell the adpater how:

using (OleDbCommand comm = new OleDbCommand(
"INSERT INTO who (name, address, age) VALUES (pName, pAddress,
pAge)", conn))
{
comm.Parameters.Add("pName", OleDbType.VarChar).SourceColumn =
"name";
comm.Parameters.Add("pAddress", OleDbType.VarChar).SourceColumn =
"address";
comm.Parameters.Add("pAge", OleDbType.Numeric).SourceColumn =
"age";

// because we have added a new row we must set the InsertCommand
adp.InsertCommand = comm;

// update the database
adp.Update(ds, "who");
}
}
}

--
Dave Sexton

"SLIMSHIM" <SL******@discussions.microsoft.comwrote in message
news:EE**********************************@microsof t.com...

Hi,
I"m new to c# and .net. I wrote a small program to add rows to an access
table.
the program goes thru the motions but the data never gets there.
here is my code. I am intentionaly not using the form controls. I"m trying
to do it solely thru code. You can safely assume I have an access datsabase
with one table called who with three columns(fields).
where did i go wrong ?

OleDbConnection con;
OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);
con.Close();

thanx in advance for all your help

[Peter Kirk]

"Dave Sexton" <dave@jwa[remove.this]online.comskrev i en meddelelse
news:%2******************@TK2MSFTNGP04.phx.gbl...

As Cor mentioned, you should really construct "con" in a C# "using"
statement to ensure that the connection is closed in the case of an
exception being thrown. You don't want to leave connections open.
(Especially not in Access, which I believe is really restrictive in the
amount of concurrency it supports):

Where I work I have been told that we should use try-catch-finally, and
close the connection in the finally, because we can't rely on the dispose
method of the connection to have been coded correctly...

[Dave Sexton]

Hi Peter,

>As Cor mentioned, you should really construct "con" in a C# "using"
statement to ensure that the connection is closed in the case of an
exception being thrown. You don't want to leave connections open.
(Especially not in Access, which I believe is really restrictive in the
amount of concurrency it supports):

Where I work I have been told that we should use try-catch-finally, and
close the connection in the finally, because we can't rely on the dispose
method of the connection to have been coded correctly...

The compiler uses a try..finally block when the using statement is compiled.
In the finally block, Dispose is called on the object specified in the using
statement. The object specified in the using statement must be implicitly
convertible to IDisposable or a compile-time error will occur. The using
statement is just an elegant shortcut to the same code as this:

OleDbConnection conn = new OleDbConnection(...);

try
{
// statements inside using block are compiled here
}
finally
{
conn.Dispose();
}

I prefer the using statement over the above code simply because it's more
legible, IMO.

The using statement doesn't compile a "catch" statement. If you except to be
able to handle a particular exception in code, you must add your own
try..catch around the using statement, or better yet, just use your own
try..catch..finally:

OleDbConnection conn = new OleDbConnection(...);

try
{
// use conn
}
catch (OleDbException ex)
{
// handle exception
}
finally
{
conn.Dispose();
}

--
Dave Sexton

[SLIMSHIM]

thank you for your help!
i"m somewhat confused as to why we need to generate all parameters of the
dataset to do an update. Doesn't the dataset already know which column is
which ?
can you do something like a "INSERT INTO who * VALUES *"
If you can retrieve the datarow why can't we just update the datarow?
please excuse my nubiness!!!
I"m so used to working with tables and records...
Can I retrieve the parameters from the database without needing to retype it
for the select statement ?

"Dave Sexton" wrote:

Hi,

DataSets are "disconnected" from the data source that was used to fill them.
In other words, they are only in-memory representations of the data in your
data store.

To update the data store with the changes in the DataSet you can call the
Update method on your data adapter and pass it the DataSet.

In order for the data adapter to successfully update the database, you must
supply the command for the update.

As Cor mentioned, you should really construct "con" in a C# "using" statement
to ensure that the connection is closed in the case of an exception being
thrown. You don't want to leave connections open. (Especially not in Access,
which I believe is really restrictive in the amount of concurrency it
supports):

DataSet ds = new DataSet();

using (OleDbConnection conn = new OleDbConnection(
testacc.Properties.Settings.Default.mytestConnecti onString))
{
using (OleDbDataAdapter adp = new OleDbDataAdapter(
"SELECT * FROM who", conn))
{
// The following line isn't normally required.
// adp.Fill opens the connection and closes it before returning,
// but since we want it to remain open for the next
// operation (Update) we'll open it now.
// adp.Fill won't close it in this case.
conn.Open();

adp.Fill(ds, "who");

DataRow row = ds.Tables["who"].NewRow();

row["name"] = "Joe";
row["address"] = "1 csharp road :)";
row["age"] = 25;

ds.Tables[0].Rows.Add(row);

// in order to update the database we must tell the adpater how:

using (OleDbCommand comm = new OleDbCommand(
"INSERT INTO who (name, address, age) VALUES (pName, pAddress,
pAge)", conn))
{
comm.Parameters.Add("pName", OleDbType.VarChar).SourceColumn =
"name";
comm.Parameters.Add("pAddress", OleDbType.VarChar).SourceColumn =
"address";
comm.Parameters.Add("pAge", OleDbType.Numeric).SourceColumn =
"age";

// because we have added a new row we must set the InsertCommand
adp.InsertCommand = comm;

// update the database
adp.Update(ds, "who");
}
}
}

--
Dave Sexton

"SLIMSHIM" <SL******@discussions.microsoft.comwrote in message
news:EE**********************************@microsof t.com...

Hi,
I"m new to c# and .net. I wrote a small program to add rows to an access
table.
the program goes thru the motions but the data never gets there.
here is my code. I am intentionaly not using the form controls. I"m trying
to do it solely thru code. You can safely assume I have an access datsabase
with one table called who with three columns(fields).
where did i go wrong ?

OleDbConnection con;
OleDbDataAdapter dbad;
DataSet ds;
DataRow dr;
con = new
OleDbConnection(testacc.Properties.Settings.Defaul t.mytestConnectionString);
dbad = new OleDbDataAdapter("select * from who",con);
ds = new DataSet();
dbad.Fill(ds, "who");
dr=ds.Tables["who"].NewRow();
dr["name"] = "Joe";
dr["adress"] = "123 anystreet";
dr["age"] = 25;
ds.Tables["who"].Rows.Add(dr);
con.Close();

thanx in advance for all your help

[Dave Sexton]

Hi,

i"m somewhat confused as to why we need to generate all parameters of the
dataset to do an update. Doesn't the dataset already know which column is
which ?

We're not generating parameters on the DataSet, we're generating parameters
for the OleDbCommand object. The DataSet just contains data and isn't aware
of the database implementation, adapters, commands, connections or anything
else like that. The names of the DataColumns don't have to be the same as the
corresponding fields in the database.

can you do something like a "INSERT INTO who * VALUES *"

Unfortunately no, because that's not valid SQL.

The adapter doesn't process your SQL statement, the database does. The
adapter's job is to simply read each DataRow from the specified DataTable (or
all DataTables) and check the RowState to determine which command (e.g.,
InsertCommand, UpdateCommand, DeleteCommand) should be used to update the row
in the database. New rows are added using the specified InsertCommand,
modified rows are updated using the specified UpdateCommand and deleted rows
are removed using the specified DeleteCommand.

For each row, the adapter checks the SourceColumn of each parameter on the
command chosen for that row and grabs the values for the parameters from the
corresponding DataColumns. After the adapter sets the parameters on the
command from the SourceColumns the command is executed. The next row is then
processed.

That's the basics of how adapters work but they do offer more control and
functionality than I've described here.

If you can retrieve the datarow why can't we just update the datarow?

That's the adapter's job. The DataRow is just in-memory data and has no
relation at all to the actual data source. Of course, if you only have a
single row then you really don't even need an adapter at all. Just set the
parameters and execute the appropriate command yourself:

OleDbCommand comm = new OleDbCommand("INSERT INTO ...", conn);

comm.AddWithValue("pName", row["name"]);
comm.AddWithValue("pAddress", row["address"]);
comm.AddWithValue("pAge", row["age"]);

comm.ExecuteNonQuery();

please excuse my nubiness!!!
I"m so used to working with tables and records...
Can I retrieve the parameters from the database without needing to retype it
for the select statement ?

Once you have a select statement, as Cor pointed out, you can use an
OleDbCommandBuilder object to create the other commands. Your select
statement must return a unique or primary key field in order for the command
builder to work (an exception is thrown otherwise according to the article
below).

"Automatically Generating Commands"
http://msdn2.microsoft.com/en-us/library/tf579hcz.aspx

"OleDbCommandBuilder Class"
http://msdn.microsoft.com/library/de...ClassTopic.asp

If you're using stored procedures and would like to automatically derive the
parameters there's an easy way to do that as well:

"OleDbCommandBuilder.DeriveParameters Method"
http://msdn2.microsoft.com/en-us/lib...arameters.aspx

--
Dave Sexton

[SLIMSHIM]

THanks once again you're all great!

I tried the code you wrote and I get this error ..
Update unable to find TableMapping['who'] or DataTable 'who'.
why doesn't it know or find the table.
if I view ds id debugging mide it shows the table name as Table.
I know i"m missing something simple . your help would be much appreciated.
"Dave Sexton" wrote:

Hi,

i"m somewhat confused as to why we need to generate all parameters of the
dataset to do an update. Doesn't the dataset already know which column is
which ?

We're not generating parameters on the DataSet, we're generating parameters
for the OleDbCommand object. The DataSet just contains data and isn't aware
of the database implementation, adapters, commands, connections or anything
else like that. The names of the DataColumns don't have to be the same as the
corresponding fields in the database.

can you do something like a "INSERT INTO who * VALUES *"

Unfortunately no, because that's not valid SQL.

The adapter doesn't process your SQL statement, the database does. The
adapter's job is to simply read each DataRow from the specified DataTable (or
all DataTables) and check the RowState to determine which command (e.g.,
InsertCommand, UpdateCommand, DeleteCommand) should be used to update the row
in the database. New rows are added using the specified InsertCommand,
modified rows are updated using the specified UpdateCommand and deleted rows
are removed using the specified DeleteCommand.

For each row, the adapter checks the SourceColumn of each parameter on the
command chosen for that row and grabs the values for the parameters from the
corresponding DataColumns. After the adapter sets the parameters on the
command from the SourceColumns the command is executed. The next row is then
processed.

That's the basics of how adapters work but they do offer more control and
functionality than I've described here.

If you can retrieve the datarow why can't we just update the datarow?

That's the adapter's job. The DataRow is just in-memory data and has no
relation at all to the actual data source. Of course, if you only have a
single row then you really don't even need an adapter at all. Just set the
parameters and execute the appropriate command yourself:

OleDbCommand comm = new OleDbCommand("INSERT INTO ...", conn);

comm.AddWithValue("pName", row["name"]);
comm.AddWithValue("pAddress", row["address"]);
comm.AddWithValue("pAge", row["age"]);

comm.ExecuteNonQuery();

please excuse my nubiness!!!
I"m so used to working with tables and records...
Can I retrieve the parameters from the database without needing to retype it
for the select statement ?

Once you have a select statement, as Cor pointed out, you can use an
OleDbCommandBuilder object to create the other commands. Your select
statement must return a unique or primary key field in order for the command
builder to work (an exception is thrown otherwise according to the article
below).

"Automatically Generating Commands"
http://msdn2.microsoft.com/en-us/library/tf579hcz.aspx

"OleDbCommandBuilder Class"
http://msdn.microsoft.com/library/de...ClassTopic.asp

If you're using stored procedures and would like to automatically derive the
parameters there's an easy way to do that as well:

"OleDbCommandBuilder.DeriveParameters Method"
http://msdn2.microsoft.com/en-us/lib...arameters.aspx

--
Dave Sexton

[Dave Sexton]

Hi,

The data adapter maps the result sets in a batch to table names using a
standard convention: Table, Table1, Table2, ...

So when you call Fill on the adapter, the table created from the first result
set is being named, "Table", unless you specify the Fill method as follows:

adp.Fill(ds, "who");

The "who" is key here because it tells the adapter to fill a table named,
"who". Since a DataTable with the name "who" doesn't exist yet at that point
in my example, the adapter creates it. Without specifying the second argument
the data would be stored in a new DataTable named, "Table".

Another way to direct the first result set of the Fill method into the desired
table is to create a DataTableMapping that maps "Table" to "who" and add it to
the adapter.

"Setting Up DataTable and DataColumn Mappings"
http://msdn.microsoft.com/library/de...mnMappings.asp

I tested the code on the 2.0 framework before I posted it and it worked just
fine. Make sure you don't leave anything out. Get it to run first before you
start changing things, which I highly recommend if you want to really
understand how it works.

After you understand basic data access the next step might be to investigate
strong-typing your DataSets. This allows you to predefine the DataTables
within your DataSet (including their names and columns). Visual Studio
provides design-time support for creating strong-typed DataSets, and in VS
20005, "Table Adapters".

"Working with a Typed DataSet"
http://msdn2.microsoft.com/en-us/library/esbykkzb.aspx

--
Dave Sexton

"SLIMSHIM" <SL******@discussions.microsoft.comwrote in message
news:74**********************************@microsof t.com...

THanks once again you're all great!

I tried the code you wrote and I get this error ..
Update unable to find TableMapping['who'] or DataTable 'who'.
why doesn't it know or find the table.
if I view ds id debugging mide it shows the table name as Table.
I know i"m missing something simple . your help would be much appreciated.
"Dave Sexton" wrote:

>Hi,

i"m somewhat confused as to why we need to generate all parameters of the
dataset to do an update. Doesn't the dataset already know which column is
which ?

We're not generating parameters on the DataSet, we're generating parameters
for the OleDbCommand object. The DataSet just contains data and isn't
aware
of the database implementation, adapters, commands, connections or anything
else like that. The names of the DataColumns don't have to be the same as
the
corresponding fields in the database.

can you do something like a "INSERT INTO who * VALUES *"

Unfortunately no, because that's not valid SQL.

The adapter doesn't process your SQL statement, the database does. The
adapter's job is to simply read each DataRow from the specified DataTable
(or
all DataTables) and check the RowState to determine which command (e.g.,
InsertCommand, UpdateCommand, DeleteCommand) should be used to update the
row
in the database. New rows are added using the specified InsertCommand,
modified rows are updated using the specified UpdateCommand and deleted
rows
are removed using the specified DeleteCommand.

For each row, the adapter checks the SourceColumn of each parameter on the
command chosen for that row and grabs the values for the parameters from
the
corresponding DataColumns. After the adapter sets the parameters on the
command from the SourceColumns the command is executed. The next row is
then
processed.

That's the basics of how adapters work but they do offer more control and
functionality than I've described here.

If you can retrieve the datarow why can't we just update the datarow?

That's the adapter's job. The DataRow is just in-memory data and has no
relation at all to the actual data source. Of course, if you only have a
single row then you really don't even need an adapter at all. Just set the
parameters and execute the appropriate command yourself:

OleDbCommand comm = new OleDbCommand("INSERT INTO ...", conn);

comm.AddWithValue("pName", row["name"]);
comm.AddWithValue("pAddress", row["address"]);
comm.AddWithValue("pAge", row["age"]);

comm.ExecuteNonQuery();

please excuse my nubiness!!!
I"m so used to working with tables and records...
Can I retrieve the parameters from the database without needing to retype
it
for the select statement ?

Once you have a select statement, as Cor pointed out, you can use an
OleDbCommandBuilder object to create the other commands. Your select
statement must return a unique or primary key field in order for the
command
builder to work (an exception is thrown otherwise according to the article
below).

"Automatically Generating Commands"
http://msdn2.microsoft.com/en-us/library/tf579hcz.aspx

"OleDbCommandBuilder Class"
http://msdn.microsoft.com/library/de...ClassTopic.asp

If you're using stored procedures and would like to automatically derive
the
parameters there's an easy way to do that as well:

"OleDbCommandBuilder.DeriveParameters Method"
http://msdn2.microsoft.com/en-us/lib...arameters.aspx

--
Dave Sexton

[Dave Sexton]

Hi,

Sorry, but msdn2 was offline when I responded last so the link on Typed
DataSets I posted couldn't be verified. It turns out, now that msdn2 is
working again, that the link really only discusses command-line support and
the XSD generator tool.

Here's a more appropriate article with a section that discusses Typed vs.
UnTyped DataSets:

"DataSets in Visual Studio Overview"
http://msdn2.microsoft.com/en-us/lib...d6(VS.80).aspx

Here's an article that discusses the new features of DataSets in VS 2005:

"New DataSet Features in Visual Studio 2005"
http://msdn.microsoft.com/library/de...wdtastvs05.asp

To get started quickly, just add a new file to your project and choose
"DataSet" from the list of file types.

Have fun :)

--
Dave Sexton

"SLIMSHIM" <SL******@discussions.microsoft.comwrote in message
news:74**********************************@microsof t.com...

THanks once again you're all great!

I tried the code you wrote and I get this error ..
Update unable to find TableMapping['who'] or DataTable 'who'.
why doesn't it know or find the table.
if I view ds id debugging mide it shows the table name as Table.
I know i"m missing something simple . your help would be much appreciated.
"Dave Sexton" wrote:

>Hi,

i"m somewhat confused as to why we need to generate all parameters of the
dataset to do an update. Doesn't the dataset already know which column is
which ?

We're not generating parameters on the DataSet, we're generating parameters
for the OleDbCommand object. The DataSet just contains data and isn't
aware
of the database implementation, adapters, commands, connections or anything
else like that. The names of the DataColumns don't have to be the same as
the
corresponding fields in the database.

can you do something like a "INSERT INTO who * VALUES *"

Unfortunately no, because that's not valid SQL.

The adapter doesn't process your SQL statement, the database does. The
adapter's job is to simply read each DataRow from the specified DataTable
(or
all DataTables) and check the RowState to determine which command (e.g.,
InsertCommand, UpdateCommand, DeleteCommand) should be used to update the
row
in the database. New rows are added using the specified InsertCommand,
modified rows are updated using the specified UpdateCommand and deleted
rows
are removed using the specified DeleteCommand.

For each row, the adapter checks the SourceColumn of each parameter on the
command chosen for that row and grabs the values for the parameters from
the
corresponding DataColumns. After the adapter sets the parameters on the
command from the SourceColumns the command is executed. The next row is
then
processed.

That's the basics of how adapters work but they do offer more control and
functionality than I've described here.

If you can retrieve the datarow why can't we just update the datarow?

That's the adapter's job. The DataRow is just in-memory data and has no
relation at all to the actual data source. Of course, if you only have a
single row then you really don't even need an adapter at all. Just set the
parameters and execute the appropriate command yourself:

OleDbCommand comm = new OleDbCommand("INSERT INTO ...", conn);

comm.AddWithValue("pName", row["name"]);
comm.AddWithValue("pAddress", row["address"]);
comm.AddWithValue("pAge", row["age"]);

comm.ExecuteNonQuery();

please excuse my nubiness!!!
I"m so used to working with tables and records...
Can I retrieve the parameters from the database without needing to retype
it
for the select statement ?

Once you have a select statement, as Cor pointed out, you can use an
OleDbCommandBuilder object to create the other commands. Your select
statement must return a unique or primary key field in order for the
command
builder to work (an exception is thrown otherwise according to the article
below).

"Automatically Generating Commands"
http://msdn2.microsoft.com/en-us/library/tf579hcz.aspx

"OleDbCommandBuilder Class"
http://msdn.microsoft.com/library/de...ClassTopic.asp

If you're using stored procedures and would like to automatically derive
the
parameters there's an easy way to do that as well:

"OleDbCommandBuilder.DeriveParameters Method"
http://msdn2.microsoft.com/en-us/lib...arameters.aspx

--
Dave Sexton

[Peter Kirk]

"Dave Sexton" <dave@jwa[remove.this]online.comskrev i en meddelelse
news:%2****************@TK2MSFTNGP02.phx.gbl...

>Where I work I have been told that we should use try-catch-finally, and
close the connection in the finally, because we can't rely on the dispose
method of the connection to have been coded correctly...

The compiler uses a try..finally block when the using statement is
compiled. In the finally block, Dispose is called on the object specified
in the using statement. The object specified in the using statement must
be implicitly convertible to IDisposable or a compile-time error will
occur. The using statement is just an elegant shortcut to the same code
as this:

OleDbConnection conn = new OleDbConnection(...);

try
{
// statements inside using block are compiled here
}
finally
{
conn.Dispose();
}

I prefer the using statement over the above code simply because it's more
legible, IMO.

Personally I prefer the using-construction as well. But my "superiors" have
dictated we must write code like this:

conn = open connection;
try
{
use connection
}
catch
{
any required exception handling
}
finally
{
conn.Close();
}

For some reason we are not allowed to trust that the developers who wrote
the connection class knew how to write the Dispose method. We are however
allowed to accept they knew how to write Close.... From what I can read
about the connection class, the Dispose method does little more than call
Close anyway, but my bleats fall on deaf ears.

Peter

[Dave Sexton]

Hi Peter,

I understand :)

Some shops even require that their programmers only use try..catch..finally
blocks because they want to swallow the exceptions. Terrible idea, IMO.

If you download Reflector you can prove to them that the Dispose method calls
Close, internally:

"Lutz Roeder's Programming .NET"
(Download Reflector for .NET)
http://www.aisto.com/roeder/dotnet/
You can also prove it by writing a very simple application:

SqlConnection conn = null;

using (conn = new SqlConnection("real connection string"))
{
conn.Open();
}

Console.WriteLine(conn.State); // prints "Closed"

--
Dave Sexton

"Peter Kirk" <[e-mail] in message
news:ur**************@TK2MSFTNGP02.phx.gbl...

>
"Dave Sexton" <dave@jwa[remove.this]online.comskrev i en meddelelse
news:%2****************@TK2MSFTNGP02.phx.gbl...

>>Where I work I have been told that we should use try-catch-finally, and
close the connection in the finally, because we can't rely on the dispose
method of the connection to have been coded correctly...

The compiler uses a try..finally block when the using statement is
compiled. In the finally block, Dispose is called on the object specified
in the using statement. The object specified in the using statement must
be implicitly convertible to IDisposable or a compile-time error will
occur. The using statement is just an elegant shortcut to the same code as
this:

OleDbConnection conn = new OleDbConnection(...);

try
{
// statements inside using block are compiled here
}
finally
{
conn.Dispose();
}

I prefer the using statement over the above code simply because it's more
legible, IMO.

Personally I prefer the using-construction as well. But my "superiors" have
dictated we must write code like this:

conn = open connection;
try
{
use connection
}
catch
{
any required exception handling
}
finally
{
conn.Close();
}

For some reason we are not allowed to trust that the developers who wrote
the connection class knew how to write the Dispose method. We are however
allowed to accept they knew how to write Close.... From what I can read
about the connection class, the Dispose method does little more than call
Close anyway, but my bleats fall on deaf ears.

Peter