Problem updating dataset to database

[Zachary Hilbun]

The below is some test code to help me learn how to update a dataset.
It is supposed to read the value of UserCounter and write it back.

The UserCounter is being read correctly as 0, is 1 when written back, but is
not being written back to the database.

Although I've used a foreach loop there is actually only 1 record in the
dataset.

I'm going to eventually use transactions but have commented them out for now
to test this problem.

I've tried using SqlCommandBuilder to build the Update and also manually.

The manual sql is commented out right now and does not work.

On the manual SQL I am unsure how to handle a field name and the @ when the
field name has a space in it.



int UserCounter;

SqlCommand commandUpdate = new SqlCommand ();

string sqlUsers;

string sqlUsersUpdate;

sqlUsers = string.Format ("SELECT [PO Next] FROM [Users] WHERE [User ID] =
'{0}'",
UserIDOfRecord);

sqlUsersUpdate = string.Format ("UPDATE [Users] SET [PO Next] = [@PO Next]
WHERE [User ID] = [@User ID]",
UserIDOfRecord);

conn_counts.Open ();

//tran transactionCount = conn_counts.BeginTransaction ();

DataSet dsUsers = new DataSet ();

SqlDataAdapter daUsers = new SqlDataAdapter ();

UserCounter = -1;

daUsers.SelectCommand = new SqlCommand ();

daUsers.SelectCommand.CommandText = sqlUsers;

daUsers.SelectCommand.Connection = conn_counts;

//tran daUsers.SelectCommand.Transaction = transactionCount;



SqlCommandBuilder custCB = new SqlCommandBuilder (daUsers);

/*

daUsers.UpdateCommand = new SqlCommand ();

daUsers.UpdateCommand.CommandText = sqlUsersUpdate;

daUsers.UpdateCommand.Connection = conn_counts;

daUsers.UpdateCommand.Transaction = transactionCount;

*/

daUsers.Fill (dsUsers, "Users");

foreach (DataRow row in dsUsers.Tables ["Users"].Rows)
UserCounter = (int) row [0];

foreach (DataRow row in dsUsers.Tables ["Users"].Rows)
row [0] = UserCounter;

dsUsers.AcceptChanges ();

daUsers.Update (dsUsers, "Users");

//tran transactionCount.Commit ();



===================================================
Zachary Hilbun
Software Consultant http://www.vianova.com
Via Nova OpenGL, Open Inventor 3D products, Windows 95/NT
Dallas, Texas VC++, C#, embedded systems, VxD's
1977 SSII SRX31185
Glock 30

 

[W.G. Ryan eMVP]

You are calling AcceptChanges right before update - this will ensure that
your update will Never get written back to the db b/c all of the rowstates
are reset. Take that out and you should be ok. However why are you using a
CommandBuilder and specifying the update logic. The one thing
CommandBuilders do for you is build the Crud logic based on Select
statements?

I'd run through the Configuration wizard and just call update - Also, you
may want to check out Bill Vaughn's Article on Weaning Developers from the
CommandBUilder at www.betav.com -> Articles -> MSDN - I found it very
helpful when I was learning ADO.NET.

BTW, doesn't look like your connection is getting closed and even if that
line is left out - if you don't wrap it in a using Block or a
try/catch/finally - you run the risk of not having it closed. That can cause
some real drama for you.

The biggest problem w/ the update though is the AcceptChanges right before
you call Update.

HTH,

Bill

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

The below is some test code to help me learn how to update a dataset.
It is supposed to read the value of UserCounter and write it back.

The UserCounter is being read correctly as 0, is 1 when written back, but is
not being written back to the database.

Although I've used a foreach loop there is actually only 1 record in the
dataset.

I'm going to eventually use transactions but have commented them out for now
to test this problem.

I've tried using SqlCommandBuilder to build the Update and also manually.

The manual sql is commented out right now and does not work.

On the manual SQL I am unsure how to handle a field name and the @ when the
field name has a space in it.



int UserCounter;

SqlCommand commandUpdate = new SqlCommand ();

string sqlUsers;

string sqlUsersUpdate;

sqlUsers = string.Format ("SELECT [PO Next] FROM [Users] WHERE [User ID] =
'{0}'",
UserIDOfRecord);

sqlUsersUpdate = string.Format ("UPDATE [Users] SET [PO Next] = [@PO Next]
WHERE [User ID] = [@User ID]",
UserIDOfRecord);

conn_counts.Open ();

//tran transactionCount = conn_counts.BeginTransaction ();

DataSet dsUsers = new DataSet ();

SqlDataAdapter daUsers = new SqlDataAdapter ();

UserCounter = -1;

daUsers.SelectCommand = new SqlCommand ();

daUsers.SelectCommand.CommandText = sqlUsers;

daUsers.SelectCommand.Connection = conn_counts;

//tran daUsers.SelectCommand.Transaction = transactionCount;



SqlCommandBuilder custCB = new SqlCommandBuilder (daUsers);

/*

daUsers.UpdateCommand = new SqlCommand ();

daUsers.UpdateCommand.CommandText = sqlUsersUpdate;

daUsers.UpdateCommand.Connection = conn_counts;

daUsers.UpdateCommand.Transaction = transactionCount;

*/

daUsers.Fill (dsUsers, "Users");

foreach (DataRow row in dsUsers.Tables ["Users"].Rows)
UserCounter = (int) row [0];

foreach (DataRow row in dsUsers.Tables ["Users"].Rows)
row [0] = UserCounter;

dsUsers.AcceptChanges ();

daUsers.Update (dsUsers, "Users");

//tran transactionCount.Commit ();



===================================================
Zachary Hilbun
Software Consultant http://www.vianova.com
Via Nova OpenGL, Open Inventor 3D products, Windows 95/NT
Dallas, Texas VC++, C#, embedded systems, VxD's
1977 SSII SRX31185
Glock 30