Filling a 2 table typed dataset with relation

[Guest]

Environment C# 2003

I have a stored procedure consisting of two select statements. I succesfully dragged this onto a new dataset creating a 2 table dataset. I also verified that the relation between the tables is correct.

I use this code in an attempt to fill the tables:

DataSets.CompContRelate ds = new Contacts.DataSets.CompContRelate();
string str = ConfigurationSettings.AppSettings["SqlConnection"];
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnection"]);
SqlDataAdapter da = new SqlDataAdapter ("sp_GetCompanyContact", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds.CompanyTbl);
da.Fill(ds.ContactTbl);
return ds;

However, I'm getting a contraint exception on the second fill. I've verified that all the correct required fields are in fact non-null. I've verified that each record in the contact table correctly relates to an existing record in the company table.

Any ideas or suggestions?

Thanks,
Dave

 

[Miha Markic [MVP C#]]

Hi Dave,

Iterate through each row and check its RowError property.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Environment C# 2003

I have a stored procedure consisting of two select statements. I

succesfully dragged this onto a new dataset creating a 2 table dataset. I
also verified that the relation between the tables is correct.

I use this code in an attempt to fill the tables:

DataSets.CompContRelate ds = new Contacts.DataSets.CompContRelate();
string str = ConfigurationSettings.AppSettings["SqlConnection"];
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnection"]);
SqlDataAdapter da = new SqlDataAdapter ("sp_GetCompanyContact", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds.CompanyTbl);
da.Fill(ds.ContactTbl);
return ds;

However, I'm getting a contraint exception on the second fill. I've

verified that all the correct required fields are in fact non-null. I've
verified that each record in the contact table correctly relates to an
existing record in the company table.

 

[Guest]

I added a try catch. For some reason the second fill is trying to fill the Contact table with the company table data. So now the question becomes, is it possible to force it to fill from the second table.

BTW, I tried da.Fill (ds);
I ended up with 4 tables in my dataset.

 

[Guest]

My current solution follows, if anyone has better way I'm open to suggestion

Thanks
Dave

DataSet TempDS = new DataSet();
DataSets.CompContRelate ds = new Contacts.DataSets.CompContRelate();
string str = ConfigurationSettings.AppSettings["SqlConnection"];
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnection"]);
SqlDataAdapter da = new SqlDataAdapter ("sp_GetCompanyContact", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

// The fill is not clever enough to auto fill the second table from the stored procedure
// except as an untyped fill.
da.Fill (TempDS);

// Do the typed fill that works
da.Fill (ds.CompanyTbl);

// Now we need to do a bit of work to load the second typed dataset.
foreach ( DataRow row in TempDS.Tables[1].Rows )
{
ds.ContactTbl.ImportRow (row);
}
ds.ContactTbl.AcceptChanges();
return ds;

 

[Miha Markic [MVP C#]]

Ah, I see now.
Dave, only one Fill(ds) is enough.
You should define the tablemapping so the adapter will know which tables to
fill.
See
Setting Up DataTable and DataColumn Mappings

..net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I added a try catch. For some reason the second fill is trying to fill the

Contact table with the company table data. So now the question becomes, is
it possible to force it to fill from the second table.

BTW, I tried da.Fill (ds);
I ended up with 4 tables in my dataset.