Solved

problem data binding gridview to sql data returned as xml using for xml auto

Posted on 2010-08-20
9
467 Views
Last Modified: 2012-05-10
i have the xml below. it is being generated by a select that joins a couple tables (see the select here)

      SELECT      pm.ProjectMilestoneId,
                  pm.ProjectId,
                  pm.MilestoneCID,
                  pm1sc.CodeName as MilestoneText,        
                  pm.StartDate,        
                  pm.EndDate,        
                  pm.RAGStatusCID,        
                  pm2sc.CodeName as RAGStatusText,        
                  pm.CompletionStatusCID,        
                  pm3sc.CodeName as CompletionStatusText,        
                  pm.StatusText,        
                  pm.ReportingPriority,        
                  pm.Added,        
                  pm.LastUpdate,        
                  pm.UpdateBy  
      FROM ProjectMilestone pm  
                  INNER JOIN StatusCode pm1sc ON pm.MilestoneCID = pm1sc.CID  
                  INNER JOIN StatusCode pm2sc ON pm.RAGStatusCID = pm2sc.CID  
                  INNER JOIN StatusCode pm3sc ON pm.CompletionStatusCID = pm3sc.CID  
      WHERE pm.ProjectId = @ProjectId  
      ORDER by pm.StartDate desc
      for xml auto

the xml for this sql is below. and causes my C# data bind to fail. my c# code is as follows:

System.Xml.XmlReader xr = cmd.ExecuteXmlReader();
            DataSet ds2 = new DataSet();
            ds2.ReadXml(xr, XmlReadMode.InferSchema);
           
            DataView myDataView = new DataView();
            myDataView = ds2.Tables[0].DefaultView;

            gvMilestone.DataSource = myDataView;
            gvMilestone.DataBind();
<pm ProjectMilestoneId="100" ProjectId="2" MilestoneCID="18" StartDate="2010-08-18T00:00:00" EndDate="2010-08-19T00:00:00" RAGStatusCID="7" CompletionStatusCID="32" StatusText="something more wicked this way comes" Added="2010-08-16T00:00:00" LastUpdate="2010-08-18T14:06:26.510" UpdateBy="AMRS\ssalvati">
  <pm1sc MilestoneText="SAD/LLD Due                                       ">
    <pm2sc RAGStatusText="Red                                               ">
      <pm3sc CompletionStatusText="In-Progress                                       " />
    </pm2sc>
  </pm1sc>
</pm>
<pm ProjectMilestoneId="99" ProjectId="2" MilestoneCID="19" StartDate="1988-05-16T00:00:00" EndDate="2011-01-01T00:00:00" RAGStatusCID="7" CompletionStatusCID="35" StatusText="something really good is about to happen" ReportingPriority="1" Added="2010-08-10T00:00:00" LastUpdate="2010-08-18T11:04:09.660" UpdateBy="AMRS\ssalvati">
  <pm1sc MilestoneText="Hardware Required                                 ">
    <pm2sc RAGStatusText="Red                                               ">
      <pm3sc CompletionStatusText="Cancelled                                         " />
    </pm2sc>
  </pm1sc>
</pm>
<pm ProjectMilestoneId="991" ProjectId="2" MilestoneCID="1" StartDate="1933-05-16T00:00:00" EndDate="2033-01-01T00:00:00" RAGStatusCID="7" CompletionStatusCID="33" StatusText="testing text11111" Added="2010-08-10T00:00:00" LastUpdate="2010-08-17T18:44:31.813" UpdateBy="mshinder">
  <pm1sc MilestoneText="High                                              ">
    <pm2sc RAGStatusText="Red                                               ">
      <pm3sc CompletionStatusText="Delayed                                           " />
    </pm2sc>
  </pm1sc>
</pm>

Open in new window

0
Comment
Question by:realcoding
  • 6
  • 3
9 Comments
 
LVL 8

Expert Comment

by:mac-will
ID: 33485164
What is the error you are seeing?

btw. you don't need the "new DataView()" line as this new object is being removed in the following line.

Change to:

DataView myDataView = ds2.Tables[0].DefaultView;
0
 

Author Comment

by:realcoding
ID: 33485216
A field or property with the name 'MilestoneText' was not found on the selected data source

seems like the tables that get joined produces sub nodes in the xml instead of tossing all the values in the select into a single xml node as attributes. i think having this would solve the problem.
0
 
LVL 8

Expert Comment

by:mac-will
ID: 33485525
Yes that is probably true.

If you infer the schema than everything will be a node as the xml parser has no idea what should/shouldn't be an attribute.

If you know what the schema is why don't you explicitly create it first?

MAC
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:realcoding
ID: 33486002
guys its simpler than that.
instead of for xml auto (creating new nodes)
i used for xml raw (creates all fields as attributes to a single noe)

and it works fine
0
 
LVL 8

Expert Comment

by:mac-will
ID: 33486607
Do you pre-define any columns in your grid?

I think generaly the dataset column names that are generated look like "Milestone_Text"

MAC
0
 
LVL 8

Expert Comment

by:mac-will
ID: 33486631
or rather "MilestoneText_Text"
0
 
LVL 8

Expert Comment

by:mac-will
ID: 33486638
try removing all pre-define columns and allow auto-generate columns
0
 

Accepted Solution

by:
realcoding earned 0 total points
ID: 33504460
i used for xml raw and it returns everything in a single xml node as an attribute.
0
 
LVL 8

Expert Comment

by:mac-will
ID: 33521027
What does the dataset table look like?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now