Refresh not working on TableAdapter

[fuzz_ball]

Okay, so I've read various articles on setting up a TableAdapter to
retrieve a newly generated identity column value back into the dataset
after an insert. However, I'm having issues with the dataset actually
getting updated. I swear this was working Monday, but once I thought
it was working, I moved on to get my updates and deletes working, and
now today when I ran my final tests the refresh after insert is not
working

So here is what I did: Right-clicked on the TableAdapter and chose
"Add Query". I selected an "INSERT" for my new query and I left the
default generated SQL for the insert as is, which came out like this
(admittedly I'm typing this by memory so may not be exact, but
basically I left it as is):

INSERT INTO [dbo].[mytable] ([column1],[column2]) VALUES (@column1,
@column2);
SELECT [keycolumn], [column1], [column2] FROM [dbo].[mytable] WHERE
([keycolumn] = SCOPE_IDENTITY())

So then I name this new insert "InsertSingleRecord" (I've tried this
with the InsertSingleRecord set to "NonQuery" and "Scalar"; of course,
when I set it to scalar, I can retrieve the identity as a return value
from InsertSingleRecord if I change the SELECT to "SELECT
SCOPE_IDENTITY()" however it does me no good since at that point
row.keycolumn is read-only so I can't manually update it myself with
the real value.

Later in my code I have something like this:

mytableadapter.InsertSingleRecord( row.column1, row.column2 );

I'm leaving out some stuff for brevity, and if you think it matters
let me know and I'll post it, but suffice to say, after the
InsertSingleRecord call completes, if I examine row.keycolumn, I can
see that it has NOT been refreshed after the insert completed (and I
verified the insert was successful). This is the case immediately
after the insert finishes, and even after all inserts have completed
and I have called AcceptChanges().

Does the refresh not work when you use these custom inserts as opposed
to just calling TableAdapter.Update(...)? I swear I had this working
like a charm on Monday before I started on my updates/deletes; maybe I
imagined it...

Any help or suggestions appreciated.

 

[fuzz_ball]

I have come to the conclusion that I must be mistaken, and that when
doing the custom, one-row-at-a-time insert, the data is not refreshed.
I'm going to fall back to modifying my in memory dataset manually
after the insert. If it turns out there is a way to have the dataset
auto-refreshed after each insert, I would still be interested in
knowing.

 

[Cor Ligthert [MVP]]

Fuzz_ball,

What do you want to refresh.
After an update the datatable is equal in your dataset to that in the
server, nothing to refresh.

It can be that you want the new rows from a database in your dataset, which
are made by others.

That often wanted funtionality is not yet in ADONET.

Cor

 

[RobinS]

Cor,

That's true, unless there are triggers firing in the database and making
additional changes to his recordset as he does the changes.

fuzz_ball -- what's the deal? Can you explain what you mean by refreshing
the datatable after the updates are saved to the database?

Robin S.

 

[Cor Ligthert [MVP]]

Robin,

It makes no sense to update a datatable, because the database contains those
values direct after an update with those.

Cor

 

[RobinS]

I still say that is true *unless* he has some triggers going on.

order Table:
DateField
UserID
UserName
[lots of other fields]

User Table:
UserID
UserName

Let's say there is a trigger on the Order Table. Whenever a record is
updated or inserted, it goes out to the User Table and gets the UserName
and fills it in in the Order Table.

If he is displaying that information, and they change the UserID, he will
want to display the new UserName that goes with it. Knowing it is updated
by a trigger, he can do an Update on the data table and get back the
updated information. (In this scenario, he wrote his own update routine,
and it will not update UserName, just UserID.)

I'm sure there are better examples, but it's 1 a.m. here and I'm getting
loopy. It's the only case I can think of where he would want to do this.

Robin S.

 

[Cor Ligthert [MVP]]

(In this scenario, he wrote his own update routine, and it will not update

UserName, just UserID.)

Maybe he can better sent a message to this newsgroup.

I'm sure there are better examples, but it's 1 a.m. here and I'm getting
loopy. It's the only case I can think of where he would want to do this.

Look at my timetable of the messages this weekend and know that I am
writting this now at 7:07pm here 19:07

8 hours time difference with you if I calculated it right,

Cor

 

[fuzz_ball]

What do you want to refresh.
After an update the datatable is equal in your dataset to that in the
server, nothing to refresh.

Well that's not what was occuring. When doing a single row update to a
table that contained an identity column (and my datatable is using
temporary values for the identity column); after the insert if you
look in the database, you see the database generated value for the
column with the identity attribute. However, when you examine the row
from my example, you will find that the associated column in that row
still retains the temporary value that was used prior to inserting the
record into the database. So basically I had to update that value
manually after the insert.

Not a big deal in the end, it just seemed odd that even when you add a
specific INSERT command, it generates code that supposedly refreshes
the datatable after executing the command, but in reality, the key
column (the identity column) was never updated unless I did it
manually.

 

[RobinS]

Yes, you're right. I am in the San Francisco area, and IIRC, you are in
Holland. You are 8 hours ahead of me. So as I write this at 10:30 p.m., I
hope you're still sleeping at 6:30 a.m. (I'm not a morning person, and will
*definitely* be sleeping at 6:30 a.m. tomorrow.)

Robin S.

 

[RobinS]

You have to specifically request the identity column value's back. So you
need two queries for your insert. Try something like this:

INSERT blahblahblahrestofquery; SELECT myIdCOlumn = @@IDENTITY

I always do this with stored procedures, so I'm not sure of how to do this
when specifically setting the code for the SQL, but this might work.

Robin S.
------------------------------------------