No Data After DataAdapter Update

[Fred Chateau]

Any obvious reason here why data is not being loaded into the database?

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataSet, "POI_Entity");

DataRow dataRow = dataSet.Tables["POI_Entity"].NewRow();
dataRow["PK_POI"] = 1;
dataRow["POI_Entity_Id"] = dataSet.Identity[0].POI_Entity_ID;
dataRow["Chain_Id"] = dataSet.Identity[0].Chain_ID;
dataRow["Category_Id"] = dataSet.Category_ID[0].Category_ID_Text;
dataRow["Name"] = dataSet.POI_Name[0].Text;

int rows = dataAdapter.Update(dataSet, "POI_Entity");

The data being transferred into the POI_Entity DataTable is from two XML
tables in the same dataset. I can see the correct data from the XML tables
in the Autos window. The POI_Entity table in the database has no data either
before or after update is called. The rows variable shows 0 data was
inserted.

 

[Nicholas Paldino [.NET/C# MVP]]

Fred,

Yes, when you call NewRow, it creates the new row, but does not attach
it to the table. Before you pass the dataset back to the adapter, you have
to call the Add method on the DataRowsCollection exposed by the Rows
property on the DataTable instance, like so:

// Create a new row.
DataRow dataRow = dataSet.Tables["POI_Entity"].NewRow();
dataRow["PK_POI"] = 1;
dataRow["POI_Entity_Id"] = dataSet.Identity[0].POI_Entity_ID;
dataRow["Chain_Id"] = dataSet.Identity[0].Chain_ID;
dataRow["Category_Id"] = dataSet.Category_ID[0].Category_ID_Text;
dataRow["Name"] = dataSet.POI_Name[0].Text;

// Add the row to the table.
dataSet.Tables["POI_Entity"].Rows.Add(dataRow);

// Pass to the data adapter here.

You don't have to load the data from the server in order to use a data
adapter or update the data at the server level. If you know the structure
you are updating, you can just make sure your data set matches that
structure, and send only the changes instead of loading all the data (of
course, you might need the data in this case as well, but one can't tell
from the example).

 

[Fred Chateau]

It worked...

I'm not quite sure what you're saying about not loading the data from the
server, but it sounds like that is what I am trying to do. I just need to
insert new data into existing tables. I tried DataAdapter.FillSchema and it
seems to work. So, I'm assuming if I use FillSchema, no existing data will
load into the DataSet. Is that correct?

Thank you very much for your assistance.

--
Regards,

Fred Chateau
http://hotelmotelnow.com

Fred,

Yes, when you call NewRow, it creates the new row, but does not attach
it to the table. Before you pass the dataset back to the adapter, you
have to call the Add method on the DataRowsCollection exposed by the Rows
property on the DataTable instance, like so:

// Create a new row.
DataRow dataRow = dataSet.Tables["POI_Entity"].NewRow();
dataRow["PK_POI"] = 1;
dataRow["POI_Entity_Id"] = dataSet.Identity[0].POI_Entity_ID;
dataRow["Chain_Id"] = dataSet.Identity[0].Chain_ID;
dataRow["Category_Id"] = dataSet.Category_ID[0].Category_ID_Text;
dataRow["Name"] = dataSet.POI_Name[0].Text;

// Add the row to the table.
dataSet.Tables["POI_Entity"].Rows.Add(dataRow);

// Pass to the data adapter here.

You don't have to load the data from the server in order to use a data
adapter or update the data at the server level. If you know the structure
you are updating, you can just make sure your data set matches that
structure, and send only the changes instead of loading all the data (of
course, you might need the data in this case as well, but one can't tell
from the example).


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Any obvious reason here why data is not being loaded into the database?

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataSet, "POI_Entity");

DataRow dataRow = dataSet.Tables["POI_Entity"].NewRow();
dataRow["PK_POI"] = 1;
dataRow["POI_Entity_Id"] = dataSet.Identity[0].POI_Entity_ID;
dataRow["Chain_Id"] = dataSet.Identity[0].Chain_ID;
dataRow["Category_Id"] = dataSet.Category_ID[0].Category_ID_Text;
dataRow["Name"] = dataSet.POI_Name[0].Text;

int rows = dataAdapter.Update(dataSet, "POI_Entity");

The data being transferred into the POI_Entity DataTable is from two XML
tables in the same dataset. I can see the correct data from the XML
tables in the Autos window. The POI_Entity table in the database has no
data either before or after update is called. The rows variable shows 0
data was inserted.

--
Regards,

Fred Chateau
http://hotelmotelnow.com

 

[Nicholas Paldino [.NET/C# MVP]]

Fred,

That should work. If all you are doing is inserting, and you don't need
the data, then this should perform better.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

It worked...

I'm not quite sure what you're saying about not loading the data from the
server, but it sounds like that is what I am trying to do. I just need to
insert new data into existing tables. I tried DataAdapter.FillSchema and
it seems to work. So, I'm assuming if I use FillSchema, no existing data
will load into the DataSet. Is that correct?

Thank you very much for your assistance.

--
Regards,

Fred Chateau
http://hotelmotelnow.com

Fred,

Yes, when you call NewRow, it creates the new row, but does not attach
it to the table. Before you pass the dataset back to the adapter, you
have to call the Add method on the DataRowsCollection exposed by the Rows
property on the DataTable instance, like so:

// Create a new row.
DataRow dataRow = dataSet.Tables["POI_Entity"].NewRow();
dataRow["PK_POI"] = 1;
dataRow["POI_Entity_Id"] = dataSet.Identity[0].POI_Entity_ID;
dataRow["Chain_Id"] = dataSet.Identity[0].Chain_ID;
dataRow["Category_Id"] = dataSet.Category_ID[0].Category_ID_Text;
dataRow["Name"] = dataSet.POI_Name[0].Text;

// Add the row to the table.
dataSet.Tables["POI_Entity"].Rows.Add(dataRow);

// Pass to the data adapter here.

You don't have to load the data from the server in order to use a data
adapter or update the data at the server level. If you know the
structure you are updating, you can just make sure your data set matches
that structure, and send only the changes instead of loading all the data
(of course, you might need the data in this case as well, but one can't
tell from the example).


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Any obvious reason here why data is not being loaded into the database?

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
dataAdapter.Fill(dataSet, "POI_Entity");

DataRow dataRow = dataSet.Tables["POI_Entity"].NewRow();
dataRow["PK_POI"] = 1;
dataRow["POI_Entity_Id"] = dataSet.Identity[0].POI_Entity_ID;
dataRow["Chain_Id"] = dataSet.Identity[0].Chain_ID;
dataRow["Category_Id"] = dataSet.Category_ID[0].Category_ID_Text;
dataRow["Name"] = dataSet.POI_Name[0].Text;

int rows = dataAdapter.Update(dataSet, "POI_Entity");

The data being transferred into the POI_Entity DataTable is from two XML
tables in the same dataset. I can see the correct data from the XML
tables in the Autos window. The POI_Entity table in the database has no
data either before or after update is called. The rows variable shows 0
data was inserted.

--
Regards,

Fred Chateau
http://hotelmotelnow.com