how to show result of a select query in needed format in asp.net

Question

I have the following query to show the datas

Select 
     convert(varchar(10),SR.StockDate,103) as StockDate,SR.PartNo,
     SR.Openingbalance,SR.Received,SR.Issued,SR.Balance,SR.Entrytype,A.MaterialName 
from 
     StockRegister SR 
join LEDProducts A 
     on SR.PartNo=A.PartNo  
where 
     SR.LocationId='"+ ddl_Location.SelectedValue.ToString() + "' and A.AudioEntityId='" 
    + ddl_CountrySR.SelectedValue.ToString() + "' 
order by SR.Sno

it shows the following result

 S Date       Type  ProductName   PartNo   OBalance  Receipt  Issues  CBalance
 1 26/07/2013  FTE     P16       01110101     500       0          0     500
 2 24/01/2014  EP      P16       01110101     500       0          100   400
 3 19/12/2013  FTE     P7        01110102     1000      0          0     1000

i need to display as follows ProductName:p16

Date            Type          Receipt  Issues    Balance
26/07/2013      FTE            500      0         500
24/01/2013       EP             0       100       400
Closing Balance :400

Productname:P7

opening balance :1000
    Date            Type          Receipt  Issues    Balance
    26/07/2013      FTE            1000      0         1000

    Closing Balance :1000

like that i want to show for all the products dynamically.

Is this query a stored procedure? It looks as though it could be tampered with to allow for SQL injection. — rhughes
– rhughes, Commented Feb 3, 2014 at 6:35
Do you want to have a list of products in your page(more than on product in the page) or if someone search for a product the page will show only one product at a time — Muhammad Nour
– Muhammad Nour, Commented Feb 3, 2014 at 6:54

Amit Tiwari · Accepted Answer · 2014-02-03 10:40:20Z

1

aspx code...

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm3.aspx.cs" Inherits="WebApplication1.WebForm3" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    </div>
    </form>
</body>
</html>

cs code...

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class WebForm3 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[9] { new DataColumn("S"), new DataColumn("Date"), new DataColumn("Type")
                ,new DataColumn("ProductName"), new DataColumn("PartNo"), new DataColumn("OBalance"),
                new DataColumn("Receipt"), new DataColumn("Issues"), new DataColumn("CBalance")});
                dt.Rows.Add("1", "26/07/2013", "FTE", "P16", "01110101", "500", "0", "0", "500");
                dt.Rows.Add("2", "24/01/2014", "EP", "P16", "01110101", "500", "0", "100", "400");
                dt.Rows.Add("3", "19/12/2013", "FTE", "P7", "01110102", "1000", "0", "0", "1000");
                var names = (from DataRow dr in dt.Rows
                             select (string)dr["ProductName"]).Distinct();
                StringBuilder s = new StringBuilder();
                s.Append("<table>");
                foreach (var r in names)
                {
                    s.Append("<tr><td colspan='5'>ProductName:" + r + "</td></tr>");
                    var rows = from DataRow dr in dt.Rows
                               where dr["ProductName"] == r
                               orderby (string)dr["S"]
                               select dr;
                    if (rows.Count() > 0)
                    {
                        s.Append("<tr><td>Date</td><td>Type</td><td>Receipt</td><td>Issues</td><td>Balance</td></tr>");

                        foreach (var r1 in rows)
                        {
                            s.Append("<tr><td>" + (string)r1["Date"] + "</td><td>" + (string)r1["Type"] + "</td><td>" + (string)r1["Receipt"] + "</td><td>" + (string)r1["Issues"] + "</td><td>" + (string)r1["CBalance"] + "</td></tr>");
                        }
                        s.Append("<tr><td colspan='5'>Closing Balance :" + (string)rows.Last()["CBalance"] + "</td></tr>");
                    }
                    s.Append("<tr><td colspan='5'></td></tr>");
                }
                s.Append("</table>");
                Response.Write(s.ToString());
            }
        }
    }
}

try to make a new demo web application and implement this code...

edited Feb 3, 2014 at 10:40

answered Feb 3, 2014 at 8:30

Amit Tiwari

3681 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Guna Over a year ago

Thanks Amit, you gave an brilliant idea to reach my need.

Guna Over a year ago

amit, it shows only one record but i need to show it as in my question

Guna Over a year ago

Hi Amit, it displays as you said but for P16 it have to show two rows because there are two entries(Rows) for P16 in the select Query. but here it shows only one row.i tried with my real data it shows one row for p16 and one row for P17.

Guna Over a year ago

from my select query there are 3 rcords, 2 for p16 and one for P17. in names it get distinct names as p16,p17. in rows in linq query it shows the first row only for P16. it didnt display the 2nd row of p16.

Muhammad Nour · Accepted Answer · 2014-02-03 08:09:14Z

0

You can use repeater control to do this with DataGrid Control with in

set its datasource property also you can set ItemTemplate in the repeater for whatever controls you want to display for every product

<asp:Repeater ID="Repeater1" runat="server">
    <ItemTemplate>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" EnableModelValidation="True"></asp:GridView>
        <asp:SqlDataSource runat="server" ID="SqlDataSource1"></asp:SqlDataSource>
    </ItemTemplate>
</asp:Repeater>

and you can use the task panel to set the columns you want to show like this configure the datagrid control using GUI tasks panel

edited Feb 3, 2014 at 8:09

answered Feb 3, 2014 at 7:38

Muhammad Nour

2,3572 gold badges20 silver badges25 bronze badges

6 Comments

Guna Over a year ago

can u explain? the very first time i meet that type displaying report

Muhammad Nour Over a year ago

I'm sorry, I couldn't understand your point, could you explain more please, what do you mean by first time meet that type

Guna Over a year ago

sorry Muhammad what i said is the very first time i try to display the report in that format

Muhammad Nour Over a year ago

Actually it depends, as I have told you before, you can filter the report by user search or you can directly use your datasource object to do the loading for all the records. here is a couple of topics that would give you a good introduction about using repeater control msdn.microsoft.com/en-us/library/… and msdn.microsoft.com/en-us/magazine/cc163780.aspx

Muhammad Nour Over a year ago

by the way repeater can do the heavy lifting for you because it handles the HTML for the template that you want to use rather that trying to write HTML from code behind which would be bulky and mixing stuff to gather

|

Collectives™ on Stack Overflow

how to show result of a select query in needed format in asp.net

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related