Undo Changes to a table after SQL fill() command

Posted on 2006-06-01
Medium Priority
Last Modified: 2010-04-23
Hi Experts,

I have a Data table that i fill from a SQLDataAdapter.  The table has a unique column that triggers an error if data gets added with a row with duplicate data in that column, which i want it to do but i cant get the reject changes to then remove the rows that i have just added!  here is the snippet of code that is not working.


SQLDa.SelectCommand.CommandText = "SELECT * FROM db WHERE ID = '123456'"

   CompTBL.Columns("ID").Unique = True
Catch ex As Exception
End Try
Question by:SteSi
  • 2
  • 2
LVL 34

Accepted Solution

Sancler earned 750 total points
ID: 16808141
.RejectChanges works by replacing a row of which the .RowState is Modified by (its own stored) version for which the .RowState is Unchanged.  When .Fill adds a row to a datatable it does so with its .RowState set to Unchanged.  So .RejectChanges won't see it.  That's why your approach is not working.

But why, I wonder, are you trying to add the row before you check if it already exists in the datatable?  What would be wrong with something on the lines of

   Dim dr() As DataRow = CompTBL.Select("ID = '123456'")
   If dr.Length = 0 then
   End If


Author Comment

ID: 16815065
Hi Roger,

the only reason i didn't do it that way and tried it the rejectchanges was because before i do the first fill command there is no column structure in the CompTBL so there is no ID column.  Is there anyway that i can set the CompTBL up without adding any data?
LVL 34

Expert Comment

ID: 16815327
Is there anyway that i can set the CompTBL up without adding any data?

Yes.  If you are using the wizards to set up your connection and dataadapter you can also get them to generate a strongly typed dataset including the relevant table.  Just right-click on the dataadapter on your form in design view and select "Generate Dataset ...".  

Or, if you are coding the dataadapter yourself, you can define the table before calling the .Fill method.  It's on these lines (very much in outline)

   Dim CompTBL As New DataTable
   Dim dc0 As New DataColumn
   With dc0
       .ColumnName = "<whatever you want>"
       .DataType = GetType(String) 'or Integer, or whatever
   End With
   'and similar for all other columns

But a different way round that problem would be to enclose

   Dim dr() As DataRow = CompTBL.Select("ID = '123456'")
   If dr.Length = 0 then
   End If

in some sort of test code.  E.g.

   If Not CompTBL Is Nothing Then

or even in a Try Catch block with nothing in the Catch.


Author Comment

ID: 16815555
I found a way to build the table just by using a SELECT command that didn't generate any records and then filled the table on initialize which got round my empty table problem.

Thanks for your help, i just thought there would be commands that would do these for me but thats always the way!

Points awarded

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question