how to handle single value and multiple values columns at the time of display in C#

10 pts.
Tags:
.NET
C#
Crystal Reports
i have 2 tables t1 & t2 in t1 customer's name address, phone etc all single value columns but in t2 table order product & qty.suppose a customer order 5 products when i disply report t1 table's values repeat 5 times corresponds 5 products means display 5 rows but i want single row. how?

Software/Hardware used:
p4, c#.net
ASKED: December 9, 2009  4:56 AM
UPDATED: January 22, 2010  5:54 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

To get customer info with a count of how many items they ordered:

SELECT t1.customerName, t1.address, t1.phone, SUM(t2.qty)
FROM t1
INNER JOIN t2 ON t1.customer = t2.customer
GROUP BY t1.customerName, t1.address, t1.phone

I’m assuming that t1 and t2 have some customer number in common.
If you want all the customer info and all of the products they ordered, this is a much more complex issue.

You can do it in c# by:

Set up a database connection, command, and data adapter. Set the data adapter’s command object to the command object you set up.

DataTable customer, orderitems;

// Get the customer data.
cmdobj.CommandText = “SELECT * FROM t1 WHERE customer = XXXXXX”;
adapterobj.Fill(customer);

// Get the order data.
cmdobj.CommandText = “SELECT * FROM t2 WHERE customer = XXXXXX AND order = YYYYY”;
adapterobj.Fill(orderitems);

string data = “”;
// Put customer data into a string
data += customer.Rows[0]["customerName">.ToString() + "\t";
data += customer.Row[s0]["address">.ToString() + "\t";
data += customer.Row[s0]["phone">.ToString();

// Add the order number once
data += orderitems.Rows[0]["order">.ToString();

// Loop through the order items and append them to the customer data.
foreach(DataRow row in orderitems.Rows)
{
data += row["product">.ToString() + "\t" + row["qty">.ToString() + "\t";
}

// Remove that extra tab.
if (data.Length > 1) data = data.SubString(0, data.Length - 1);
Close the database connection.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    What is the SQL query you are using ? What are your tables structures ? What database are you using ?
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following