realcoding
asked on
problem data binding gridview to sql data returned as xml using for xml auto
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();
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>
ASKER
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.
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.
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
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
ASKER
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
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
Do you pre-define any columns in your grid?
I think generaly the dataset column names that are generated look like "Milestone_Text"
MAC
I think generaly the dataset column names that are generated look like "Milestone_Text"
MAC
or rather "MilestoneText_Text"
try removing all pre-define columns and allow auto-generate columns
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What does the dataset table look like?
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;