error on query of derived datatable in dataset

[cindy]

Get data into datatable, add to dataset dsSearch "

Get data into datatable, add to dataset dsSearch

Using In-Memory SQL Engine join the tables and select the filenames from the
join, add to dataset dsSearch
CODE ON
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3994&lngWId=10

using SQL connection get data from view on sql server, add to dataset dsSearch

What I want to do is use dsHelper code from MS to create a relationship
join the derived third datatable and the sql connection datatable (fourth)
then fill the created table with query of third and fourth datatables.


I can assign the third derived table to a grid and see results. Using one of
the filenames from the grid I can get data out of view, view in grid ok also
the error comes creating the join, it does not like the DocFiles.FileTitle
because it is derived? or because I am using table named when it is added to
dataset?

MICROSOFT DSHELPER
http://support.microsoft.com/default.aspx?scid=kb;en-us;326080

------------------------------------------------------------
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Source Error:


{
DataColumn dc = SourceTable.Columns[Field.FieldName];
dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
}
else



----------------------------------------------------------
CODE IS DONE ON PAGE LOAD

First
DataTable dt = dsSearch.Tables.Add("Lists");
dt.Columns.Add("ListTitle", typeof(string));
foreach (SPSearchResult oSR in oSRSet)
{
string title = oSR.Title.ToString();
dt.Rows.Add(new Object[] {title});
}
Second
DataTable dt1 = dsSearch.Tables.Add("Docs");
dt1.Columns.Add("DocTitle", typeof(string));
foreach (SPSearchResult oSR in oSRSet1)
{
string title = oSR.Title.ToString();
dt1.Rows.Add(new Object[] {title});
}
Third
SelectCommand sc = new SelectCommand(dsSearch);
DataTable dtm = dsSearch.Tables.Add("DocFiles");
dtm.Columns.Add("FileTitle",typeof(string));
dtm = sc.Execute("Select ListTitle from Lists inner join Docs on
Lists.ListTitle = Docs.DocTitle");
Fourth
DataTable dtProps = dsSearch.Tables.Add("DocProps");
SqlConnection connection = new SqlConnection(mystr);
SqlCommand command = new SqlCommand();
string sql = "Select * from view";
SqlDataAdapter adapter = new SqlDataAdapter();
command.CommandText = sql;
command.Connection = connection;
adapter.SelectCommand = command;
adapter.Fill(dtProps);
dsHelper JOin
dsSearch.Relations.Add("relationResult",
dsSearch.Tables["DocProps"].Columns["LeafName"],
dsSearch.Tables["DocFiles"].Columns["FileTitle"]);
dsHelper.CreateJoinTable("SearchPage",dsSearch.Tables["DocProps"],"Modality,[Product Series],Abstract,[Doc Type],[Revised Date],[LeafName]");

dsHelper Fill
dsHelper.InsertJoinInto(dsSearch.Tables["SearchPage"],
dsSearch.Tables["DocProps"],
"Modality,Abstract,ProductSeries,DocType,RevisedDate,DocFiles.FileTitle
docname",null,"docname");

 

[Phillip Williams]

Hi Cindy,

By looking at the code in the DSHelper class on the MSDN, I see within the
definition for the InsertJoinInto method the following:
DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName);
DestRow[Field.FieldName] = ParentRow[Field.FieldName];

This means that it was expecting you to pass the relation name
"relationResult" instead of the table named "DocFiles". I do not have either
codes on my desktop to verify this but my guess is try the following instead
and see if it works:

dsHelper.InsertJoinInto(dsSearch.Tables["SearchPage"],
dsSearch.Tables["DocProps"],

"Modality,Abstract,ProductSeries,relationResult,RevisedDate,relationResult.FileTitle
docname",null,"docname");

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com

Get data into datatable, add to dataset dsSearch "

Get data into datatable, add to dataset dsSearch

Using In-Memory SQL Engine join the tables and select the filenames from the
join, add to dataset dsSearch
CODE ON
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3994&lngWId=10

using SQL connection get data from view on sql server, add to dataset dsSearch

What I want to do is use dsHelper code from MS to create a relationship
join the derived third datatable and the sql connection datatable (fourth)
then fill the created table with query of third and fourth datatables.


I can assign the third derived table to a grid and see results. Using one of
the filenames from the grid I can get data out of view, view in grid ok also
the error comes creating the join, it does not like the DocFiles.FileTitle
because it is derived? or because I am using table named when it is added to
dataset?

MICROSOFT DSHELPER
http://support.microsoft.com/default.aspx?scid=kb;en-us;326080

------------------------------------------------------------
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Source Error:


{
DataColumn dc = SourceTable.Columns[Field.FieldName];
dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
}
else



----------------------------------------------------------
CODE IS DONE ON PAGE LOAD

First
DataTable dt = dsSearch.Tables.Add("Lists");
dt.Columns.Add("ListTitle", typeof(string));
foreach (SPSearchResult oSR in oSRSet)
{
string title = oSR.Title.ToString();
dt.Rows.Add(new Object[] {title});
}
Second
DataTable dt1 = dsSearch.Tables.Add("Docs");
dt1.Columns.Add("DocTitle", typeof(string));
foreach (SPSearchResult oSR in oSRSet1)
{
string title = oSR.Title.ToString();
dt1.Rows.Add(new Object[] {title});
}
Third
SelectCommand sc = new SelectCommand(dsSearch);
DataTable dtm = dsSearch.Tables.Add("DocFiles");
dtm.Columns.Add("FileTitle",typeof(string));
dtm = sc.Execute("Select ListTitle from Lists inner join Docs on
Lists.ListTitle = Docs.DocTitle");
Fourth
DataTable dtProps = dsSearch.Tables.Add("DocProps");
SqlConnection connection = new SqlConnection(mystr);
SqlCommand command = new SqlCommand();
string sql = "Select * from view";
SqlDataAdapter adapter = new SqlDataAdapter();
command.CommandText = sql;
command.Connection = connection;
adapter.SelectCommand = command;
adapter.Fill(dtProps);
dsHelper JOin
dsSearch.Relations.Add("relationResult",
dsSearch.Tables["DocProps"].Columns["LeafName"],
dsSearch.Tables["DocFiles"].Columns["FileTitle"]);
dsHelper.CreateJoinTable("SearchPage",dsSearch.Tables["DocProps"],"Modality,[Product Series],Abstract,[Doc Type],[Revised Date],[LeafName]");

dsHelper Fill
dsHelper.InsertJoinInto(dsSearch.Tables["SearchPage"],
dsSearch.Tables["DocProps"],
"Modality,Abstract,ProductSeries,DocType,RevisedDate,DocFiles.FileTitle
docname",null,"docname");

 

[Phillip Williams]

rather it should be:
dsHelper.InsertJoinInto(dsSearch.Tables["SearchPage"],
dsSearch.Tables["DocProps"],"Modality,Abstract,ProductSeries,DocType,RevisedDate,relationResult.FileTitle docname","docname");

Hi Cindy,

By looking at the code in the DSHelper class on the MSDN, I see within the
definition for the InsertJoinInto method the following:
DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName);
DestRow[Field.FieldName] = ParentRow[Field.FieldName];

This means that it was expecting you to pass the relation name
"relationResult" instead of the table named "DocFiles". I do not have either
codes on my desktop to verify this but my guess is try the following instead
and see if it works:

dsHelper.InsertJoinInto(dsSearch.Tables["SearchPage"],
dsSearch.Tables["DocProps"],

"Modality,Abstract,ProductSeries,relationResult,RevisedDate,relationResult.FileTitle
docname",null,"docname");

--
HTH,
Phillip Williams
http://www.societopia.net
http://www.webswapp.com

Get data into datatable, add to dataset dsSearch "

Get data into datatable, add to dataset dsSearch

Using In-Memory SQL Engine join the tables and select the filenames from the
join, add to dataset dsSearch
CODE ON
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3994&lngWId=10

using SQL connection get data from view on sql server, add to dataset dsSearch

What I want to do is use dsHelper code from MS to create a relationship
join the derived third datatable and the sql connection datatable (fourth)
then fill the created table with query of third and fourth datatables.


I can assign the third derived table to a grid and see results. Using one of
the filenames from the grid I can get data out of view, view in grid ok also
the error comes creating the join, it does not like the DocFiles.FileTitle
because it is derived? or because I am using table named when it is added to
dataset?

MICROSOFT DSHELPER
http://support.microsoft.com/default.aspx?scid=kb;en-us;326080

------------------------------------------------------------
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.

Source Error:


{
DataColumn dc = SourceTable.Columns[Field.FieldName];
dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);
}
else



----------------------------------------------------------
CODE IS DONE ON PAGE LOAD

First
DataTable dt = dsSearch.Tables.Add("Lists");
dt.Columns.Add("ListTitle", typeof(string));
foreach (SPSearchResult oSR in oSRSet)
{
string title = oSR.Title.ToString();
dt.Rows.Add(new Object[] {title});
}
Second
DataTable dt1 = dsSearch.Tables.Add("Docs");
dt1.Columns.Add("DocTitle", typeof(string));
foreach (SPSearchResult oSR in oSRSet1)
{
string title = oSR.Title.ToString();
dt1.Rows.Add(new Object[] {title});
}
Third
SelectCommand sc = new SelectCommand(dsSearch);
DataTable dtm = dsSearch.Tables.Add("DocFiles");
dtm.Columns.Add("FileTitle",typeof(string));
dtm = sc.Execute("Select ListTitle from Lists inner join Docs on
Lists.ListTitle = Docs.DocTitle");
Fourth
DataTable dtProps = dsSearch.Tables.Add("DocProps");
SqlConnection connection = new SqlConnection(mystr);
SqlCommand command = new SqlCommand();
string sql = "Select * from view";
SqlDataAdapter adapter = new SqlDataAdapter();
command.CommandText = sql;
command.Connection = connection;
adapter.SelectCommand = command;
adapter.Fill(dtProps);
dsHelper JOin
dsSearch.Relations.Add("relationResult",
dsSearch.Tables["DocProps"].Columns["LeafName"],
dsSearch.Tables["DocFiles"].Columns["FileTitle"]);
dsHelper.CreateJoinTable("SearchPage",dsSearch.Tables["DocProps"],"Modality,[Product Series],Abstract,[Doc Type],[Revised Date],[LeafName]");

dsHelper Fill
dsHelper.InsertJoinInto(dsSearch.Tables["SearchPage"],
dsSearch.Tables["DocProps"],
"Modality,Abstract,ProductSeries,DocType,RevisedDate,DocFiles.FileTitle
docname",null,"docname");