A couple of questions about updating data through a dataset

[Rod]

I have been using .NET for three years. We are currently using VS .NET 2003
and SQL Server 2000. Whenever I have needed to update data I normally
created a SqlCommand object, assigned it the appropriate string (depending
upon whether it is a stored procedure or not) and executed it. However, now
I would like to use the SqlDataAdapter's UpdateCommand and Update() method.

However, I am running into problems, which I think I can break down into two
questions. The first is, how do I assign values to the dataset which I want
to be updated? For example, if I have something like this:

DataRow r = DataTable.Rows[0];

and the row has a column named FirstName, can I do this:

r["FirstName"] = "Rod";

or do I have to do something like this first:

r.BeginEdit();
r["FirstName"] = "Rod";

And if I do have to use the BeginEdit() method, do I also have to issue the
EndEdit() method? Or will that basically clear the row's Current view and
reassign that to the Original view?

I know that the datarow's SourceVersion will come into play, but I am not
sure how.

Second question: What DOES the Update() method of the SqlDataAdapter really
work on? For example, after struggling with this for half the day I finally
got rid of the error message I was getting saying,

"Procedure 'spMyProc' expects parameter '@FirstName', which was not
supplied"

to go away, but now when I assign a value to the datarow's FirstName column,
then I would have expected that to actually be saved to the database when I
issued the SqlDataAdapter's Update() method. Instead, it just quietly goes
about its business and nothing at all is saved/updated. So, what I am doing
wrong now?

Rod

 

[W.G. Ryan eMVP]

DataRow r = DataTable.Rows[0];

and the row has a column named FirstName, can I do this:

r["FirstName"] = "Rod";

or do I have to do something like this first:

r.BeginEdit();
r["FirstName"] = "Rod";

And if I do have to use the BeginEdit() method, do I also have to issue
the EndEdit() method? Or will that basically clear the row's Current view
and reassign that to the Original view?

If you are doing this programatically, then no you don't. THe problme is
that when you use the UI, often it will start the beginedit but nothing
triggers the end edit. HOwever, I'd highly recommed that you do use
beginedit. If you don't, then for instance, if your values are sorted, it
could affect the sort immediately.

I know that the datarow's SourceVersion will come into play, but I am not
sure how.

Second question: What DOES the Update() method of the SqlDataAdapter
really work on? For example, after struggling with this for half the day
I finally got rid of the error message I was getting saying,

"Procedure 'spMyProc' expects parameter '@FirstName', which was not
supplied"

to go away, but now when I assign a value to the datarow's FirstName
column, then I would have expected that to actually be saved to the
database when I issued the SqlDataAdapter's Update() method. Instead, it
just quietly goes about its business and nothing at all is saved/updated.
So, what I am doing wrong now?

Rod

Update goes through each row and looks at the RowState. If it's modified,
added, deleted, then it looks to the corresponding command and fires it. If
you look at the overload for a dataColumn DataColumn dc = new
DataColumn("ColumnName", typeof(Whatever), Column). This is the value that
will map back to the parameter if you've coded your CRUD correctly or of you
had it generated. IF you don't have any changeds, you can call Update all
year and nothing will happen. If you call Update and you have modified rows
but no update command, you'll get an exception. It maps those values back to
the params.

Does this answer your question?

BTW< I'd highly encourage using a Keyed table, and using the DataAdapter
Configuration wizard and deconstructing what it created. You'll learn a
lot - or at least, I did.

 

[Rod]

Thank you, W.G. for replying. My reply is in-line (towards the bottom):

DataRow r = DataTable.Rows[0];

and the row has a column named FirstName, can I do this:

r["FirstName"] = "Rod";

or do I have to do something like this first:

r.BeginEdit();
r["FirstName"] = "Rod";

And if I do have to use the BeginEdit() method, do I also have to issue
the EndEdit() method? Or will that basically clear the row's Current
view and reassign that to the Original view?

If you are doing this programatically, then no you don't. THe problme is
that when you use the UI, often it will start the beginedit but nothing
triggers the end edit. HOwever, I'd highly recommed that you do use
beginedit. If you don't, then for instance, if your values are sorted, it
could affect the sort immediately.

I know that the datarow's SourceVersion will come into play, but I am not
sure how.

Second question: What DOES the Update() method of the SqlDataAdapter
really work on? For example, after struggling with this for half the day
I finally got rid of the error message I was getting saying,

"Procedure 'spMyProc' expects parameter '@FirstName', which was not
supplied"

to go away, but now when I assign a value to the datarow's FirstName
column, then I would have expected that to actually be saved to the
database when I issued the SqlDataAdapter's Update() method. Instead, it
just quietly goes about its business and nothing at all is saved/updated.
So, what I am doing wrong now?

Rod

Update goes through each row and looks at the RowState. If it's modified,
added, deleted, then it looks to the corresponding command and fires it.
If you look at the overload for a dataColumn DataColumn dc = new
DataColumn("ColumnName", typeof(Whatever), Column). This is the value
that will map back to the parameter if you've coded your CRUD correctly or
of you had it generated. IF you don't have any changeds, you can call
Update all year and nothing will happen. If you call Update and you have
modified rows but no update command, you'll get an exception. It maps
those values back to the params.

Does this answer your question?

Almost. I will modify a column in the dataset, but then when I call the
Update() method of the SqlDataAdapter it doesn't save/change anything. I
don't understand how I could modify the value but then the Update() not
work.

BTW, I am doing this programmatically, rather than using any wizard like the
CommandBuilder.

 

[Rod]

I finally figured out what I was doing wrong. I had used the DataRowVersion
enumeration for the SourceVersion of the SqlParameter object, but I had not
assigned the SourceColumn for the SqlParameter object. Once I assigned
that, then it worked fine. (BTW, I didn't use BeginEdit(), and it still
worked fine.)

Rod

Thank you, W.G. for replying. My reply is in-line (towards the bottom):

DataRow r = DataTable.Rows[0];

and the row has a column named FirstName, can I do this:

r["FirstName"] = "Rod";

or do I have to do something like this first:

r.BeginEdit();
r["FirstName"] = "Rod";

And if I do have to use the BeginEdit() method, do I also have to issue
the EndEdit() method? Or will that basically clear the row's Current
view and reassign that to the Original view?

If you are doing this programatically, then no you don't. THe problme is
that when you use the UI, often it will start the beginedit but nothing
triggers the end edit. HOwever, I'd highly recommed that you do use
beginedit. If you don't, then for instance, if your values are sorted, it
could affect the sort immediately.

I know that the datarow's SourceVersion will come into play, but I am
not sure how.

Second question: What DOES the Update() method of the SqlDataAdapter
really work on? For example, after struggling with this for half the
day I finally got rid of the error message I was getting saying,

"Procedure 'spMyProc' expects parameter '@FirstName', which was not
supplied"

to go away, but now when I assign a value to the datarow's FirstName
column, then I would have expected that to actually be saved to the
database when I issued the SqlDataAdapter's Update() method. Instead,
it just quietly goes about its business and nothing at all is
saved/updated. So, what I am doing wrong now?

Rod

Update goes through each row and looks at the RowState. If it's modified,
added, deleted, then it looks to the corresponding command and fires it.
If you look at the overload for a dataColumn DataColumn dc = new
DataColumn("ColumnName", typeof(Whatever), Column). This is the value
that will map back to the parameter if you've coded your CRUD correctly
or of you had it generated. IF you don't have any changeds, you can call
Update all year and nothing will happen. If you call Update and you have
modified rows but no update command, you'll get an exception. It maps
those values back to the params.

Does this answer your question?

Almost. I will modify a column in the dataset, but then when I call the
Update() method of the SqlDataAdapter it doesn't save/change anything. I
don't understand how I could modify the value but then the Update() not
work.

BTW, I am doing this programmatically, rather than using any wizard like
the CommandBuilder.