[RESOLVED] Help with Copying Datarow

[daimous]

hi guys help please.
Currently i have a code below that Supposedly copy the structure of the Dataset "ds" to Dataset "tmpds" and perform a logical test and if it is true a Datarow from Table1 of "ds" Dataset will be copied or Add to Table1 of "tmpds" Dataset but it has an error (See the Code in Bold letters)

CODE:

DataSet FuncTrimValidity(DataSet ds)
{
DataSet tmpds = ds.Clone();
DataRow dr;// = new DataRow();
try
{
//My logical test here if if it is true perform the code below
//IF(My logic Test){ //But this does not matter to my question
dr = ds.Tables[1].Rows[0];
tmpds.Tables[1].Rows.Add(dr); //Error Occurs Here
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return tmpds;
}


ERROR:

"This row already belongs to another table."


Any suggestion guys to solve the problem? Thanks in advance!

[jmcilhinney]

tmpds.Tables[1].ImportRow(dr);

[daimous]

Thanks for That JM..But another error occurs in the same line where the previous error occured.

"ForeignKeyConstraint data_row requires the child key values (0) to exist in the parent table."

[jmcilhinney]

If you have a foreign key relationship you have to have a parent record to have a child record, so obviously you need to either have a parent record or not have a foreign key constraint.

Do you really need to clone this DataSet? Are you only using one DataTable from it? if so then just clone the DataTable rather than the whole DataSet.

You should also be aware of the DataView.ToTable method. If you want only certain records from an existing DataTable then you can create a DataView, set its RowFilter, RowStateFilter and Sort properties, then call ToTable.

[daimous]

Thanks for that JM...Actually, what i want to clone is the Datatable itself and not the Dataset, so thank for the suggestion. But i already get of error by putting this code

tmpds.Tables[1].Constraints.Clear();
tmpds.Tables[1].ParentRelations.Clear();

before try clause but i think your suggestion is more better than mine. But anyway, I have another question is it posible to import a row in specified position instead of appending it at the end of the record? Thanks!

[jmcilhinney]

Given that you can sort data using a DataView the actual order of the records in a DataTable doesn't really mean much. If you really need your records in a specific order in the DataTable then do as I suggested and create a DataView, sort it and then call its ToTable method.

[daimous]

...Maybe i'll just tell what im trying to achieve and maybe solicit for some advice, suggestions or comments...

I have one Datatable Table1 in my Datatset ds1. My Table1 has 3 columns, DocumentID, DocumentTitle and ExpirationDate, and there could be one or more rows and it is posible that the value of Column DocumentID of each rows could have the same value. Now, what im trying to achieve is to retrieve those rows for each DocumentID that has the latest ExpirationDate.
For example I have 5 rows in my Table1.

Table1:
1,Title1,2007-08-15
2,Title2,2007-08-13
1,Title1,2007-08-20
2,Titel2,2007-08-18
3,Title3,2007-08-25

Rows I only want to retrive:
1,Title1,2007-08-20
2,Titel2,2007-08-18
3,Title3,2007-08-25

Hope you get my problem.

Any Advice or Suggestion on how to achive this? Thanks in advance!

[jmcilhinney]

Dim table2 As DataTable = table1.Copy()
Dim row As DataRow

For index As Integer = table2.Rows.Count - 1 To 0 Step -1
row = table2.Rows(index)

If CDate(row("ExpirationDate")) <> CDate(table2.Compute("MAX(ExpirationDate)", _
String.Format("DocumentID = {0}", _
row("DocumentID")))) Then
table2.Rows.Remove(row)
End If
Next indexThat would be a little inefficient with large tables, so I'd create a Dictionary of ExpirationDates by DocumentID first to avoid repeating any Compute operations.

You could also use Clone instead of Copy to get an empty table, then perform the same test but use ImportRow on records that DID match instead of Remove on those that DIDN'T.

[daimous]

Thanks for that JM..I'll work on that...

[daimous]

Thanks JM..I've been able to solved the problem and thanks to DataTable.Compute method...It helps me a lot!