how to avoid duplicate data????????????

Question

how to avoid duplicate data????????????

sql4us

Old Hand

Points: 388
More actions
December 9, 2011 at 10:57 am

#148847

Unable to achieve the expected result as shown below..still get duplicates please any help would be appreciated
Asp.net code:
protected void Page_Load(object sender, EventArgs e)
{
sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
sb.Append("<DATA>");
getdata();
sb.Append("</DATA>");
string encodexml = encodeXML(sb.ToString());
sendXMLcontent(encodexml);
}
private void getdata()
{
using (SqlConnection myConn = new SqlConnection(connStr))
{
try
{
SqlCommand myCommand = new SqlCommand("[SP]", myConn);
myCommand.CommandType = CommandType.StoredProcedure;
myConn.Open();
SqlDataReader sdr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (sdr.Read())
{
string node1 = (sdr["STATE"].ToString());
string node2 = (sdr["Subcat1"].ToString());
string node3 = (sdr["Subcat2"].ToString());
sb.Append("<LIST>");
sb.Append("<STATE NAME=\"" + node1 + "\" >");
sb.Append("<Subcat1 NAME=\"" + node2 + "\" >");
sb.Append("<Subcat2 NAME=\"" + node3 + "\" />");
sb.Append("</Subcat1>");
sb.Append("</STATE>");
sb.Append("</LIST>");
}
myConn.Close();
myCommand.Dispose();
sdr.Close();
sdr.Dispose();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
Response.End();
}
}
Current Results: // HERE STATE=CA but sub-category 1 has multiple subcategory2's. I want to show reslults like I mentioned in expected results below.
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA1234A">
<Subcat2 NAME="CA123" />
</Subcat1>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat1 NAME="CA147" />
</Subcat1>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat1 NAME="CA148" />
</WING>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat2 NAME="CA149" />
</Subcat1>
</STATE>
</LIST>
- <LIST>
- <STATE NAME="CA">
- <Subcat1 NAME="CA162CCG">
<Subcat2 NAME="CA216" />
</Subcat1>
</STATE>
</LIST>
Expected Output:
<LIST>
<STATE NAME="CA">
<Subcat1 NAME="CA1234A">
<Subcat2 NAME="CA123"/>
</Subcat1>
<Subcat1 NAME="CA162CCG">
<Subcat2 NAME="CA146"/>
<Subcat2 NAME="CA147"/>
<Subcat2 NAME="CA148"/>
<Subcat2 NAME="CA216"/>
</Subcat1>
</STATE>
</LIST>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

except for the bad choice of the stored procedure name [SP] posting the code of the sproc would help us helping you very much.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

hemant.kaushal Newbie Points: 7 More actions · Answer 2

Try something like this

sb.Append("<LIST>");

int i=0;

while (sdr.Read())

{

string node1 = (sdr["STATE"].ToString());

string node2 = (sdr["Subcat1"].ToString());

string node3 = (sdr["Subcat2"].ToString());

if(i==0)

sb.Append("<STATE NAME=\"" + node1 + "\" >");

i=1;

sb.Append("<Subcat1 NAME=\"" + node2 + "\" >");

sb.Append("<Subcat2 NAME=\"" + node3 + "\" />");

sb.Append("</Subcat1>");

}

sb.Append("</STATE>");

sb.Append("</LIST>");