|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Refresh not working on TableAdapterretrieve 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. 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. 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 Show quoteHide quote "fuzz_ball" <chris.r.put***@gmail.com> schreef in bericht news:1173985231.237471.63620@y80g2000hsf.googlegroups.com... >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,
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. ------------------------------- Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:O$MDlXVaHHA.2320@TK2MSFTNGP03.phx.gbl... > 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 > > > "fuzz_ball" <chris.r.put***@gmail.com> schreef in bericht > news:1173985231.237471.63620@y80g2000hsf.googlegroups.com... >>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. >> > > Robin,
It makes no sense to update a datatable, because the database contains those values direct after an update with those. Cor Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> schreef in bericht news:bN6dnWVaFp8dUmDYnZ2dnUVZ_hSdnZ2d@comcast.com... > 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]" <notmyfirstn***@planet.nl> wrote in message > news:O$MDlXVaHHA.2320@TK2MSFTNGP03.phx.gbl... >> 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 >> >> >> "fuzz_ball" <chris.r.put***@gmail.com> schreef in bericht >> news:1173985231.237471.63620@y80g2000hsf.googlegroups.com... >>>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. >>> >> >> > > 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. ---------------------------- Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:Ok4DaDfaHHA.5080@TK2MSFTNGP02.phx.gbl... > Robin, > > It makes no sense to update a datatable, because the database contains > those values direct after an update with those. > > Cor > > "RobinS" <RobinS@NoSpam.yah.none> schreef in bericht > news:bN6dnWVaFp8dUmDYnZ2dnUVZ_hSdnZ2d@comcast.com... >> 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]" <notmyfirstn***@planet.nl> wrote in message >> news:O$MDlXVaHHA.2320@TK2MSFTNGP03.phx.gbl... >>> 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 >>> >>> >>> "fuzz_ball" <chris.r.put***@gmail.com> schreef in bericht >>> news:1173985231.237471.63620@y80g2000hsf.googlegroups.com... >>>>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. >>>> >>> >>> >> >> > > > (In this scenario, he wrote his own update routine, and it will not update Maybe he can better sent a message to this newsgroup.> UserName, just UserID.) > > I'm sure there are better examples, but it's 1 a.m. here and I'm getting Look at my timetable of the messages this weekend and know that I am > loopy. It's the only case I can think of where he would want to do this. > writting this now at 7:07pm here 19:07 8 hours time difference with you if I calculated it right, Cor Show quoteHide quote > Robin S. > ---------------------------- > "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message > news:Ok4DaDfaHHA.5080@TK2MSFTNGP02.phx.gbl... >> Robin, >> >> It makes no sense to update a datatable, because the database contains >> those values direct after an update with those. >> >> Cor >> >> "RobinS" <RobinS@NoSpam.yah.none> schreef in bericht >> news:bN6dnWVaFp8dUmDYnZ2dnUVZ_hSdnZ2d@comcast.com... >>> 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]" <notmyfirstn***@planet.nl> wrote in message >>> news:O$MDlXVaHHA.2320@TK2MSFTNGP03.phx.gbl... >>>> 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 >>>> >>>> >>>> "fuzz_ball" <chris.r.put***@gmail.com> schreef in bericht >>>> news:1173985231.237471.63620@y80g2000hsf.googlegroups.com... >>>>>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. >>>>> >>>> >>>> >>> >>> >> >> > > 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. --------------------- Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:eECh1FlaHHA.4872@TK2MSFTNGP03.phx.gbl... >> (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 > > > >> Robin S. >> ---------------------------- >> "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message >> news:Ok4DaDfaHHA.5080@TK2MSFTNGP02.phx.gbl... >>> Robin, >>> >>> It makes no sense to update a datatable, because the database contains >>> those values direct after an update with those. >>> >>> Cor >>> >>> "RobinS" <RobinS@NoSpam.yah.none> schreef in bericht >>> news:bN6dnWVaFp8dUmDYnZ2dnUVZ_hSdnZ2d@comcast.com... >>>> 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]" <notmyfirstn***@planet.nl> wrote in message >>>> news:O$MDlXVaHHA.2320@TK2MSFTNGP03.phx.gbl... >>>>> 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 >>>>> >>>>> >>>>> "fuzz_ball" <chris.r.put***@gmail.com> schreef in bericht >>>>> news:1173985231.237471.63620@y80g2000hsf.googlegroups.com... >>>>>>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. >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > > What do you want to refresh. Well that's not what was occuring. When doing a single row update to a> After an update the datatable is equal in your dataset to that in the > server, nothing to refresh. 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. 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. ------------------------------------------ Show quoteHide quote "fuzz_ball" <chris.r.put***@gmail.com> wrote in message news:1174435839.948076.252210@d57g2000hsg.googlegroups.com... > >> 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. > > > >  
Other interesting topics
Password protect .mdb file
Import text file with DATE column into MS Access Database with VB.NET or C# A search module in c# How can I use connected mode instead of disconnected? Is it possible to fill a DataSet.DataTable with values from more than one database table? Get PK back on an insert into Oracle I need an approach to storing/editing data amongs forms DataGridView and Combobox columns Inherit form typed Dataset Is it possible to use flat files as datasource |
|||||||||||||||||||||||
