Refresh not working on TableAdapter

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

.

Relevant Pages

  • Re: SSIS - Retrieve Identity from DB-DataFlow Destination(ID after ins
    ... > Is there an easy method to retrieve a identity value during/after a row is ... > 3) Create customer record with the AddressID (identity column) from ...
    (microsoft.public.sqlserver.dts)
  • Re: How Do I setup a RowChangedEvent in a Typed DataSet
    ... William R. Vaughn ... I have a strongly typed dataset and when I write the identity column to the ... I set it and retrieve it. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: TableAdapter.Update fails with missing parameter
    ... First I created 2 DataSources: ... want to only retrieve the specific record that is selected in the MBList ... This line of code loads data into the ... The MBDetails.Updatecommand was also generated by the TableAdapter ...
    (microsoft.public.dotnet.framework.adonet)
  • SQLCeCommandBuilder...
    ... I retrieve a dataset with 0-1 rows of a table. ... an identity column as primary key. ... Identity column is selected into the ... Dim dcConn As New SqlCeConnection ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: SqlCommand which INSERTS and then SELECTs single value
    ... The Select statement to retrieve the Identity column can just be: ... You can append it to the INSERT statement separated by a semi-colon and use ExecuteScalar to execute the INSERT and retrieve the new Identity. ... The CommandText consists of the INSERT followed by and separated by a; ...
    (microsoft.public.dotnet.framework.adonet)