error on query of derived datatable in dataset

[Guest]

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

 

[Steven Cheng[MSFT]]

Hi Cindy,

Welcome to ASPNET newsgroup. Regarding on this issue, I've also found
another cross thread in the

Newsgroups: microsoft.public.dotnet.framework.aspnet.webcontrols

A community guy has posted his response there. Please feel free to followup
there.

Thanks,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)







--------------------
| Thread-Topic: error on query of derived datatable in dataset
| thread-index: AcXeQ7Mf6tJ4d1UnQt+PUdAyAXM4+A==
| X-WBNR-Posting-Host: 71.136.160.3
| From: "=?Utf-8?B?Y2luZHk=?=" <[email protected]>
| Subject: error on query of derived datatable in dataset
| Date: Mon, 31 Oct 2005 09:51:19 -0800
| Lines: 94
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.dotnet.framework.aspnet
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.dotnet.framework.aspnet:135035
| X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
|
| 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&lng
WId=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");
|
|
|
|
|
|
| --
| cindy
|