DataSet Update

  • Thread starter Thread starter Brad Markisohn
  • Start date Start date
B

Brad Markisohn

I'm trying to update a modified dataset into an Access DB. Everything seems
to work okay and I've run AcceptChanges on the dataset, but the number of
updated records is always zero! Any assistance would be greatly
appreciated. I've included the update function from my code at the end of
this message.

TIA

Brad



public int UpdateDataSet(System.Data.DataSet ds, string strTableName)
{
int iRecordsUpdated = 0; //Count of records updated

if (Connect()) //Open a connection to the DB
{
//Creat the Select Command object
System.Data.OleDb.OleDbCommand objCommand = new
System.Data.OleDb.OleDbCommand("Select * from MyUsers",Connection());

//Create the Data Adapter using the previously created command
object
System.Data.OleDb.OleDbDataAdapter DA = new
System.Data.OleDb.OleDbDataAdapter(objCommand);

//Creat the command builder object using the Data Adapter
System.Data.OleDb.OleDbCommandBuilder cb = new
System.Data.OleDb.OleDbCommandBuilder(DA);

DA.InsertCommand = cb.GetInsertCommand(); //This is the default SQL
insert command
DA.DeleteCommand = cb.GetDeleteCommand(); //This is the default SQL
delete command
DA.UpdateCommand = cb.GetUpdateCommand(); //This is the default SQL
update command


iRecordsUpdated = DA.Update(ds); //Update the database with the
modified data set

Disconnect(); //Disconnect the database

}

return (iRecordsUpdated); //Return the number of records updated
}
 
Hi Brad,

When exactly do you invoke AcceptChanges?
Does your table have an primary key?
And I strongly recommend you that you create appropriate commands at design
time - avoid commandbuilder.
 
My guess is that you are calling AcceptChanges on the dataset someplace
before the update, thus marking all the rows as unmodified.

Additionally, you don't need to set the adapters Insert/Delte/Update command
properties to what the command builder generates. They are ignored, and the
command builder's version is automatically used. Those methods are there for
debug purposes only.
 
Miha,

My table does have a primary key. I invoke AcceptChanges before reaching
the call to updating via the data adapter. I get an error message if that
isn't done before the update and I assumed, probably incorrectly, that the
cause was that the dataset was not in an updatable state.

Brad


Miha Markic said:
Hi Brad,

When exactly do you invoke AcceptChanges?
Does your table have an primary key?
And I strongly recommend you that you create appropriate commands at design
time - avoid commandbuilder.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Brad Markisohn said:
I'm trying to update a modified dataset into an Access DB. Everything
seems
to work okay and I've run AcceptChanges on the dataset, but the number of
updated records is always zero! Any assistance would be greatly
appreciated. I've included the update function from my code at the end of
this message.

TIA

Brad



public int UpdateDataSet(System.Data.DataSet ds, string strTableName)
{
int iRecordsUpdated = 0; //Count of records updated

if (Connect()) //Open a connection to the DB
{
//Creat the Select Command object
System.Data.OleDb.OleDbCommand objCommand = new
System.Data.OleDb.OleDbCommand("Select * from MyUsers",Connection());

//Create the Data Adapter using the previously created command
object
System.Data.OleDb.OleDbDataAdapter DA = new
System.Data.OleDb.OleDbDataAdapter(objCommand);

//Creat the command builder object using the Data Adapter
System.Data.OleDb.OleDbCommandBuilder cb = new
System.Data.OleDb.OleDbCommandBuilder(DA);

DA.InsertCommand = cb.GetInsertCommand(); //This is the default SQL
insert command
DA.DeleteCommand = cb.GetDeleteCommand(); //This is the default SQL
delete command
DA.UpdateCommand = cb.GetUpdateCommand(); //This is the default SQL
update command


iRecordsUpdated = DA.Update(ds); //Update the database with the
modified data set

Disconnect(); //Disconnect the database

}

return (iRecordsUpdated); //Return the number of records updated
}
Click to expand...
Click to expand...
 
Marina,

Thanks for your reply. When I don't perform the AcceptChanges, I get an
Insert system error message. The SQL that is autogenerated looks like this:
"INSERT INTO MyTable(Name, Password, IsEnabled) VALUES(?, ?, ?)"

I'm not sure why this is failing? It is the SQL command that I would've
generated by hand since this is such a simple DB update.

Brad
 
I would guess it is because Password is an access keyword (and maybe Name
also). In general, I would discourage your from giving tables or column
names that are SQL keywords.

Try setting the QuotePrefix and QuoteSuffix properties on the command
builder to [ and ], respectively.
 
Besides quoting which is a good suggestion, what is the exact error you are
getting?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Marina said:
I would guess it is because Password is an access keyword (and maybe Name
also). In general, I would discourage your from giving tables or column
names that are SQL keywords.

Try setting the QuotePrefix and QuoteSuffix properties on the command
builder to [ and ], respectively.

Brad Markisohn said:
Marina,

Thanks for your reply. When I don't perform the AcceptChanges, I get an
Insert system error message. The SQL that is autogenerated looks like this:
"INSERT INTO MyTable(Name, Password, IsEnabled) VALUES(?, ?, ?)"

I'm not sure why this is failing? It is the SQL command that I would've
generated by hand since this is such a simple DB update.

Brad

end
Click to expand...
Click to expand...
 
Miha,

I am now able to add and delete rows. The problem appeared that the dataset
wasn't getting updated properly. I used BeginEdit() and EndEdit() to get
the rowstate to change. The only remaining problem is that I'm having
trouble updating specific values in a row using the following syntax:
ds.Tables[0].Rows[0][0].UserName = newvalue;
After this line, the rowstate does show that it's been modified, but the old
value is still in the UserName column of the dataset.

Brad


Miha Markic said:
Besides quoting which is a good suggestion, what is the exact error you are
getting?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Marina said:
I would guess it is because Password is an access keyword (and maybe Name
also). In general, I would discourage your from giving tables or column
names that are SQL keywords.

Try setting the QuotePrefix and QuoteSuffix properties on the command
builder to [ and ], respectively.

Brad Markisohn said:
Marina,

Thanks for your reply. When I don't perform the AcceptChanges, I get an
Insert system error message. The SQL that is autogenerated looks like this:
"INSERT INTO MyTable(Name, Password, IsEnabled) VALUES(?, ?, ?)"

I'm not sure why this is failing? It is the SQL command that I would've
generated by hand since this is such a simple DB update.

Brad

My guess is that you are calling AcceptChanges on the dataset someplace
before the update, thus marking all the rows as unmodified.

Additionally, you don't need to set the adapters Insert/Delte/Update
command
properties to what the command builder generates. They are ignored,
and
the
command builder's version is automatically used. Those methods are
there
for
debug purposes only.

I'm trying to update a modified dataset into an Access DB.
Everything
seems
to work okay and I've run AcceptChanges on the dataset, but the
number
of
updated records is always zero! Any assistance would be greatly
appreciated. I've included the update function from my code at the end
of
this message.

TIA

Brad



public int UpdateDataSet(System.Data.DataSet ds, string
strTableName)
{
int iRecordsUpdated = 0; //Count of records updated

if (Connect()) //Open a connection to the DB
{
//Creat the Select Command object
System.Data.OleDb.OleDbCommand objCommand = new
System.Data.OleDb.OleDbCommand("Select * from MyUsers",Connection());

//Create the Data Adapter using the previously created
command
object
System.Data.OleDb.OleDbDataAdapter DA = new
System.Data.OleDb.OleDbDataAdapter(objCommand);

//Creat the command builder object using the Data Adapter
System.Data.OleDb.OleDbCommandBuilder cb = new
System.Data.OleDb.OleDbCommandBuilder(DA);

DA.InsertCommand = cb.GetInsertCommand(); //This is the default
SQL
insert command
DA.DeleteCommand = cb.GetDeleteCommand(); //This is the default
SQL
delete command
DA.UpdateCommand = cb.GetUpdateCommand(); //This is the default
SQL
update command


iRecordsUpdated = DA.Update(ds); //Update the database
Click to expand...
with
the
modified data set

Disconnect(); //Disconnect the database

}

return (iRecordsUpdated); //Return the number of records
updated
}
Click to expand...
Click to expand...
Click to expand...
 
Hi Brad,

Are you sure that the syntax is correct?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Brad Markisohn said:
Miha,

I am now able to add and delete rows. The problem appeared that the
dataset
wasn't getting updated properly. I used BeginEdit() and EndEdit() to get
the rowstate to change. The only remaining problem is that I'm having
trouble updating specific values in a row using the following syntax:
ds.Tables[0].Rows[0][0].UserName = newvalue;
After this line, the rowstate does show that it's been modified, but the
old
value is still in the UserName column of the dataset.

Brad


Miha Markic said:
Besides quoting which is a good suggestion, what is the exact error you are
getting?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Marina said:
I would guess it is because Password is an access keyword (and maybe
Name
also). In general, I would discourage your from giving tables or column
names that are SQL keywords.

Try setting the QuotePrefix and QuoteSuffix properties on the command
builder to [ and ], respectively.

Marina,

Thanks for your reply. When I don't perform the AcceptChanges, I get an
Insert system error message. The SQL that is autogenerated looks like
this:
"INSERT INTO MyTable(Name, Password, IsEnabled) VALUES(?, ?, ?)"

I'm not sure why this is failing? It is the SQL command that I would've
generated by hand since this is such a simple DB update.

Brad

My guess is that you are calling AcceptChanges on the dataset someplace
before the update, thus marking all the rows as unmodified.

Additionally, you don't need to set the adapters Insert/Delte/Update
command
properties to what the command builder generates. They are ignored,
and
the
command builder's version is automatically used. Those methods are
there
for
debug purposes only.

I'm trying to update a modified dataset into an Access DB.
Everything
seems
to work okay and I've run AcceptChanges on the dataset, but the
number
of
updated records is always zero! Any assistance would be greatly
appreciated. I've included the update function from my code at
the
end
of
this message.

TIA

Brad



public int UpdateDataSet(System.Data.DataSet ds, string
strTableName)
{
int iRecordsUpdated = 0; //Count of records updated

if (Connect()) //Open a connection to the DB
{
//Creat the Select Command object
System.Data.OleDb.OleDbCommand objCommand = new
System.Data.OleDb.OleDbCommand("Select * from MyUsers",Connection());

//Create the Data Adapter using the previously created
command
object
System.Data.OleDb.OleDbDataAdapter DA = new
System.Data.OleDb.OleDbDataAdapter(objCommand);

//Creat the command builder object using the Data Adapter
System.Data.OleDb.OleDbCommandBuilder cb = new
System.Data.OleDb.OleDbCommandBuilder(DA);

DA.InsertCommand = cb.GetInsertCommand(); //This is the
default
SQL
insert command
DA.DeleteCommand = cb.GetDeleteCommand(); //This is the
default
SQL
delete command
DA.UpdateCommand = cb.GetUpdateCommand(); //This is the
default
SQL
update command


iRecordsUpdated = DA.Update(ds); //Update the database with
the
modified data set

Disconnect(); //Disconnect the database

}

return (iRecordsUpdated); //Return the number of records
updated
}
Click to expand...
Click to expand...
Click to expand...
 
Miha,

This is very perplexing - which probably means this is a real beginner
problem! Anyhow, the add new rows and delete existing rows works fine using
the autogenerated queries, but I still can't modify the contents of an
existing row in the database. I did read that the dataset keeps the
original data until the AcceptChanges function is called to maintain
consistancy with the database. That explains that even though the rowstate
is Modified, the contents appear unchanged. Interestingly, the
dataadapter.Update() call changes the rowstate in the dataset from modified
to unchanged. I thought that I needed to run the datasets AccepteChanges
function to get this to happen. Any suggestions?

Brad

Miha Markic said:
Hi Brad,

Are you sure that the syntax is correct?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Brad Markisohn said:
Miha,

I am now able to add and delete rows. The problem appeared that the
dataset
wasn't getting updated properly. I used BeginEdit() and EndEdit() to get
the rowstate to change. The only remaining problem is that I'm having
trouble updating specific values in a row using the following syntax:
ds.Tables[0].Rows[0][0].UserName = newvalue;
After this line, the rowstate does show that it's been modified, but the
old
value is still in the UserName column of the dataset.

Brad


Miha Markic said:
Besides quoting which is a good suggestion, what is the exact error you are
getting?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I would guess it is because Password is an access keyword (and maybe
Name
also). In general, I would discourage your from giving tables or column
names that are SQL keywords.

Try setting the QuotePrefix and QuoteSuffix properties on the command
builder to [ and ], respectively.

Marina,

Thanks for your reply. When I don't perform the AcceptChanges, I
Click to expand...
get
an
Insert system error message. The SQL that is autogenerated looks like
this:
"INSERT INTO MyTable(Name, Password, IsEnabled) VALUES(?, ?, ?)"

I'm not sure why this is failing? It is the SQL command that I would've
generated by hand since this is such a simple DB update.

Brad

My guess is that you are calling AcceptChanges on the dataset someplace
before the update, thus marking all the rows as unmodified.

Additionally, you don't need to set the adapters Insert/Delte/Update
command
properties to what the command builder generates. They are ignored,
and
the
command builder's version is automatically used. Those methods are
there
for
debug purposes only.

I'm trying to update a modified dataset into an Access DB.
Everything
seems
to work okay and I've run AcceptChanges on the dataset, but the
number
of
updated records is always zero! Any assistance would be greatly
appreciated. I've included the update function from my code at
the
end
of
this message.

TIA

Brad



public int UpdateDataSet(System.Data.DataSet ds, string
strTableName)
{
int iRecordsUpdated = 0; //Count of records updated

if (Connect()) //Open a connection to
Click to expand...
the
DB
{
//Creat the Select Command object
System.Data.OleDb.OleDbCommand objCommand = new
System.Data.OleDb.OleDbCommand("Select * from MyUsers",Connection());

//Create the Data Adapter using the previously created
command
object
System.Data.OleDb.OleDbDataAdapter DA = new
System.Data.OleDb.OleDbDataAdapter(objCommand);

//Creat the command builder object using the Data Adapter
System.Data.OleDb.OleDbCommandBuilder cb = new
System.Data.OleDb.OleDbCommandBuilder(DA);

DA.InsertCommand = cb.GetInsertCommand(); //This is the
default
SQL
insert command
DA.DeleteCommand = cb.GetDeleteCommand(); //This is the
default
SQL
delete command
DA.UpdateCommand = cb.GetUpdateCommand(); //This is the
default
SQL
update command


iRecordsUpdated = DA.Update(ds); //Update the database with
the
modified data set

Disconnect(); //Disconnect the database

}

return (iRecordsUpdated); //Return the number of records
updated
}
Click to expand...
Click to expand...
Click to expand...
 
Back
Top