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
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:Ka*****************@newssvr11.news.prodigy.co m...

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

[Zachary Hilbun]

I removed the AcceptChanges and it now appears to be trying to update the
datbase. SqlCommandBuilder is incorrectly generating the SQL. Some of the
field names have a space in them and SqlCommandBuilder is not putting []
around them when it uses them.
When I first wrote this test code I was building the update SQL by hand but
couldn't figure out how to refer to these fields, either as [@User ID] or
@[User ID].
I didn't show my connection close or the try/catch statments.
================================================== =
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" <Wi*********@gmail.com> wrote in message
news:O7**************@TK2MSFTNGP12.phx.gbl...

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
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:Ka*****************@newssvr11.news.prodigy.co m...

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]

Have you set the QuotePrefix and QuoteSuffix properties? That should
address the space problem you mention - although it's probably worth it to
bite the bullet and get rid of the names with spaces in them - even if you
always remember to address this issue - another newer developer will
probably forget - lot's of downside for little upside.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:2A*****************@newssvr11.news.prodigy.co m...

I removed the AcceptChanges and it now appears to be trying to update the
datbase. SqlCommandBuilder is incorrectly generating the SQL. Some of the field names have a space in them and SqlCommandBuilder is not putting []
around them when it uses them.
When I first wrote this test code I was building the update SQL by hand but couldn't figure out how to refer to these fields, either as [@User ID] or
@[User ID].
I didn't show my connection close or the try/catch statments.
================================================== =
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" <Wi*********@gmail.com> wrote in message
news:O7**************@TK2MSFTNGP12.phx.gbl...

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
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:Ka*****************@newssvr11.news.prodigy.co m...

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

[Zachary Hilbun]

Adding QuotePrefix and QuoteSuffix fixed it. If I want to build the Update
SQL manually do I use [@User ID] or @[User ID]? Also, do I have to specify
Insert and Delete SQL if I manually specify SQL for Update and don't use
SqlCommandBuilder?

Thanks for the help on this. At least I have something I know works.
================================================== =
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" <Wi*********@gmail.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Have you set the QuotePrefix and QuoteSuffix properties? That should
address the space problem you mention - although it's probably worth it to
bite the bullet and get rid of the names with spaces in them - even if you
always remember to address this issue - another newer developer will
probably forget - lot's of downside for little upside.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:2A*****************@newssvr11.news.prodigy.co m...

I removed the AcceptChanges and it now appears to be trying to update the
datbase. SqlCommandBuilder is incorrectly generating the SQL. Some of

the

field names have a space in them and SqlCommandBuilder is not putting []
around them when it uses them.
When I first wrote this test code I was building the update SQL by hand

but

couldn't figure out how to refer to these fields, either as [@User ID] or
@[User ID].
I didn't show my connection close or the try/catch statments.
================================================== =
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" <Wi*********@gmail.com> wrote in message
news:O7**************@TK2MSFTNGP12.phx.gbl...

> 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
> "Zachary Hilbun" <gu****@vianova.com> wrote in message
> news:Ka*****************@newssvr11.news.prodigy.co m...
>>
>> 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]

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:ED*****************@newssvr11.news.prodigy.co m...

Adding QuotePrefix and QuoteSuffix fixed it. --Good deal
If I want to build the Update SQL manually do I use [@User ID] or @[User ID]? --You don't need to use [] around parameter names.
Also, do I have to specify Insert and Delete SQL if I manually specify SQL for Update and don't use
SqlCommandBuilder? --You'll need to specify a command for each type of action that's taken.
Let's say on a particular screen that you don't allow Deletes. then you
wouldn't need a delete command. When you call Update on the Adapter, it
loops through each row on the table and checks rowstate- then it maps the
rowstate to the respective command (Modified will map to the Update command,
Added will map to the insert command and Deleted will map to the delete
command).

HTH,

Bill
Thanks for the help on this. At least I have something I know works.
================================================== =
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" <Wi*********@gmail.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Have you set the QuotePrefix and QuoteSuffix properties? That should
address the space problem you mention - although it's probably worth it to bite the bullet and get rid of the names with spaces in them - even if you always remember to address this issue - another newer developer will
probably forget - lot's of downside for little upside.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Zachary Hilbun" <gu****@vianova.com> wrote in message
news:2A*****************@newssvr11.news.prodigy.co m...

I removed the AcceptChanges and it now appears to be trying to update the datbase. SqlCommandBuilder is incorrectly generating the SQL. Some of

the

field names have a space in them and SqlCommandBuilder is not putting [] around them when it uses them.
When I first wrote this test code I was building the update SQL by hand

but

couldn't figure out how to refer to these fields, either as [@User ID] or @[User ID].
I didn't show my connection close or the try/catch statments.
================================================== =
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" <Wi*********@gmail.com> wrote in message
news:O7**************@TK2MSFTNGP12.phx.gbl...
> 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
> "Zachary Hilbun" <gu****@vianova.com> wrote in message
> news:Ka*****************@newssvr11.news.prodigy.co m...
>>
>> 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
>>
>>
>
>

[Cor Ligthert]

Bill,

I have never seen anybody complaining in the dotNet newsgroups about the
commandbuilder with a simple select except Bill and you (I remember me one
time when somebody used more than 100 items in a datarow, what is as well
not a pretty thing to do by hand and I know about a joining table).

I have followed you both often in my messages, however are there serious
complaints about its use in a simple single not joined use?

Cor

[W.G. Ryan eMVP]

There's a lot wrong with CommandBuilders but my biggest gripes are that they
are performance pigs, you have 0 control over concurrency and you can't use
procedures. So today you might be fine using a CommandBuilder- but if the
concurrency scenario changes - you are totally screwed and Must write new
code to deal with it - and a LOT of code

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Cor Ligthert" <no************@planet.nl> wrote in message
news:eX**************@tk2msftngp13.phx.gbl...

Bill,

I have never seen anybody complaining in the dotNet newsgroups about the
commandbuilder with a simple select except Bill and you (I remember me one
time when somebody used more than 100 items in a datarow, what is as well
not a pretty thing to do by hand and I know about a joining table).

I have followed you both often in my messages, however are there serious
complaints about its use in a simple single not joined use?

Cor