C
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");
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");