How to solve it plz help me?

Satyapriya Nayak

16y2.7k.NET

Hi

Suppose there is a table named product
Its columns are
1.e_date
2.e_name
3.quantity
4.Total
Its values are suppose
e_date      e_name          quantity Total
1/1/2000    Toys                   1        10
1/1/2000    books                 1        10
1/1/2000    furniture            1        10

2/1/2000    Music system      2        20
2/1/2000    Telivision             2        20
2/1/2000    computer           2        20

3/1/2000    Chocolate           3        30
3/1/2000    Softdrinks           3        30
3/1/2000    Icecream            3        30
4/1/2000    pen                    4        40
4/1/2000    pencil                 4        40
4/1/2000    rubber               4        40

My question is when i choose the date from (1/1/2000 to 2/1/2000)
Then the result output will be as below shown:
e_date      e_name                                        quantity       Total
1/1/2000   Toys,books,furniture                        1               10
2/1/2000   Music system,Telivision,computer     2              20

We can give the input two dates in two textboxs and output will be show in gridview or table.There is a button when clicked the output will be show in respective gridview or table.

Thanks in Advance

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

DRISHTY16y ago
I tried to create it. You have to do little modification in it..

CREATE PROCEDURE GET_ProductData()
AS
BEGIN
DECLARE @dt AS DATETIME
DECLARE @pnm AS NVARCHAR(MAX)
DECLARE @tp AS NVARCHAR(MAX)
DECLARE @tempDT AS DATETIME
set @tp=''
CREATE TABLE #temp2(e_date DATETIME,e_name NVARCHAR(MAX))
DECLARE curTemp CURSOR FOR SELECT e_date,e_name FROM PRODUCT
OPEN curTemp
FETCH NEXT FROM curTemp into @dt,@pnm
SET @tp=@pnm
SET @tempDT=@dt
WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM curTemp INTO @dt,@pnm

IF @tempDT=@dt
BEGIN
set @tp=@tp+','+@pnm
END
ELSE
BEGIN
INSERT INTO #temp2 SELECT @dt,@tp
SET @tempDT=@dt
set @tp=@pnm
END
END
INSERT INTO #temp2 SELECT @dt,@tp
CLOSE curTemp
DEALLOCATE curTemp
SELECT * FROM #temp2
DROP TABLE #temp2

END
0
DRISHTY16y ago
You can get this output by writing storedprocedure using SQL. Then attach your datasource to gridview
0
Satyapriya Nayak16y ago
Hello Mam,
Your sql query output result below.
e_date      e_name          quantity Total
1/1/2000    Toys                   1        10
1/1/2000    books                 1        10
1/1/2000    furniture            1        10

2/1/2000    Music system      2        20
2/1/2000    Telivision             2        20
2/1/2000    computer           2        20

But i don't need result like this
I need the result like
e_date      e_name                                        quantity       Total
1/1/2000   Toys,books,furniture                        1               10
2/1/2000   Music system,Telivision,computer     2              20
The difference between these two are
for the same e_date ,quantity and Total column e_name are combined and separated with commas.
Thanks
0
Satyapriya Nayak16y ago
hello sir ,
I have used the between operator .While using it shows me result
e_date      e_name          quantity Total
1/1/2000    Toys                   1        10
1/1/2000    books                 1        10
1/1/2000    furniture            1        10

2/1/2000    Music system      2        20
2/1/2000    Telivision             2        20
2/1/2000    computer           2        20

But i don't need result like this
I need the result like
e_date      e_name                                        quantity       Total
1/1/2000   Toys,books,furniture                        1               10
2/1/2000   Music system,Telivision,computer     2              20
The difference between these two are
for the same e_date ,quantity and Total column e_name are combined and separated with commas.

Thanks

0
DRISHTY16y ago
Hi Satyapriya,
If your product table contains records that for one day like date '1/1/2000', products have different quantity and total.. then what should be the outpu? This is valid or it wont happen?
0
Dipa Ahuja16y ago
hi.. bind your gridview with sqldatasource

on the top of your aspx page take two text boxes and two button

   start date: (mm/dd/yyyy)
   End date: (mm/dd/yyyy)
   Filter" OnClick="Button1_Click" />
Reset" />

.cs
//Filter
protected void Button1_Click(object sender, EventArgs e)
   {

   SqlDataSource1.SelectCommand = "select * from table1 where e_date>='" + txtstart.Text.ToString() + "' and e_date<='" + txtend.Text.ToString() + "'";

   GridView1.DataBind();
   }

//Reset

   protected void Button2_Click(object sender, EventArgs e)
   {
   SqlDataSource1.SelectCommand = "select * from table1";
   GridView1.DataBind();

   }

if it help you then mark my answer..
0
Amit Choudhary16y ago
hi friend,

Use Between operator in your sql query. The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

SQL BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Please mark "Do you like this post" if it helps you.
0

How to solve it plz help me?

SQL BETWEEN Syntax