error on query of derived datatable in dataset

Discussion in 'ASP .Net Web Controls' started by cindy, Oct 31, 2005.

  1. cindy

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






    --
    cindy
    cindy, Oct 31, 2005
    #1
    1. Advertising

  2. 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


    "cindy" wrote:

    > 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");
    >
    >
    >
    >
    >
    >
    > --
    > cindy
    Phillip Williams, Oct 31, 2005
    #2
    1. Advertising

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

    "Phillip Williams" wrote:

    > 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
    >
    >
    > "cindy" wrote:
    >
    > > 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");
    > >
    > >
    > >
    > >
    > >
    > >
    > > --
    > > cindy
    Phillip Williams, Oct 31, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nedu N
    Replies:
    1
    Views:
    1,768
    Chris Taylor
    Oct 31, 2003
  2. Nedu N
    Replies:
    2
    Views:
    2,135
  3. =?Utf-8?B?Y2luZHk=?=

    error on query of derived datatable in dataset

    =?Utf-8?B?Y2luZHk=?=, Oct 31, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    2,029
    Steven Cheng[MSFT]
    Nov 1, 2005
  4. Replies:
    1
    Views:
    378
    myork
    May 23, 2007
  5. Replies:
    1
    Views:
    369
    Victor Bazarov
    May 23, 2007
Loading...

Share This Page