Undo Changes to a table after SQL fill() command
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.
Cheers,
SQLDa.SelectCommand.Comman
Try
SQLDa.Fill(CompTBL)
CompTBL.Columns("ID").Uniq
Catch ex As Exception
MsgBox(Err.Description)
CompTBL.RejectChanges()
End Try
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.
Cheers,
SQLDa.SelectCommand.Comman
Try
SQLDa.Fill(CompTBL)
CompTBL.Columns("ID").Uniq
Catch ex As Exception
MsgBox(Err.Description)
CompTBL.RejectChanges()
End Try
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>
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
CompTBL.Columns.Add(dc0)
'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
SQLDa.Fill(CompTBL)
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.
Roger
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
CompTBL.Columns.Add(dc0)
'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
SQLDa.Fill(CompTBL)
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.
Roger
ASKER
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
Thanks for your help, i just thought there would be commands that would do these for me but thats always the way!
Points awarded
ASKER
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?