gridview need a column to auto decrement the s.no.

Question

1.00/5 (1 vote)

See more:

C#

In a gridview need a column to auto decrement the s.no.. having a condition that it will be based on the year and month . assume that the gridview having a column based on date. for every month s.no starts from 1.

Output will be like this:

S.no    Date
   
  2       29-01-2015
  1       30-01-2015
  2       01-02-2015
  1       05-02-2015
  2       02-03-2015
  1       05-03-2015
  3       06-04-2015
  2       07-04-2015
  1       08-04-2015


aspx page:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
        
            <columns>
             <asp:TemplateField HeaderText="S.no">
                    <itemtemplate>
                      
                    <asp:Label  ID="lblSerial" runat="server" />
               
                    </itemtemplate>
                    
                <asp:BoundField DataField="Date" HeaderText="Date" />
            </columns>
        
</asp:GridView >
aspx.cs page:

 public partial class test : System.Web.UI.Page
    {
        MySqlConnection con = new MySqlConnection(ConStr.ConnectStr());
        int monthcount;
        DataTable dt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Binddata();
            }
        }
        public void Binddata()
        {
            try
            {

                con.Open();
                MySqlCommand cmd = new MySqlCommand("select * from test", con);

                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
               
                da.Fill(dt);

                  monthcount = dt.Rows.Count + 1;
                  for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string date = dt.Rows[i]["Date"].ToString();
                    string[] sl = date.Split('-');
                    string sl2 = sl[2].ToString();
                    string sl1 = sl[1].ToString();
                    string current_row_date = sl2 + sl1;
                  
                    if (i != 0)
                    {
                        string ldate = dt.Rows[i - 1]["Date"].ToString();
                        string[] lsl = date.Split('-');
                        string lsl2 = lsl[2].ToString();
                        string lsl1 = lsl[1].ToString();
                        string lcurrent_row_date = lsl2 + lsl1;
                        Session["lcurrent_row_date"] = lcurrent_row_date.ToString();
                    }
                    else
                    {
                        Session["lcurrent_row_date"] = "";
                    }

                    string lastrow = Session["lcurrent_row_date"].ToString();
                    if (current_row_date == lastrow)
                    {
                       Label lblSerial = (Label)GridView1.Rows[i].FindControl("lblSerial");
                       lblSerial.Text = monthcount.ToString();
                    }
                    else
                    {
                        Label lblSerial = (Label)GridView1.Rows[i].FindControl("lblSerial");
                        lblSerial.Text = monthcount.ToString();
                    }
               
                }
                monthcount--;
            
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
            catch (Exception)
            {

                throw;
            }



        }
   }
}

Posted 14-May-15 1:31am

Surandar Raj R

Updated 18-May-15 20:22pm

v6

Add a Solution

Comments

BacchusBeale 14-May-15 7:51am

what is s.no..? pls show some code

ZurdoDev 14-May-15 9:34am

Do you have a question?

Sinisa Hajnal 15-May-15 3:20am

Please move the code from comments into the question and format it. And ask the question. What is the problem with your code? What do you get out? An error? Unordered column?

Surandar Raj R 15-May-15 3:28am

i didnt get that output. plz help me to solve ...

deepankarbhatnagar 15-May-15 8:01am

Not getting your point..

Surandar Raj R 15-May-15 9:04am

auto decrement s.no based on month and year in date that to in desc.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

deepankarbhatnagar · Accepted Answer · 2015-05-15T18:37:00

Solution 3

For auto generated S.No column, pLease write this code inside gridview<column> on design page.

<templetefield>

XML

<ItemTemplate> <asp:Label ID = "lblserial" Text ='<%# Container.DataItemIndex + 1%>' runat="server"></asp:Label> </ItemTemplate>

Thanks in advance

Posted 15-May-15 18:37pm

deepankarbhatnagar

Updated 15-May-15 18:41pm

v3

Comments

Member 11414035 18-May-15 1:30am

Very correct answer. I use this code always.

Surandar Raj R 18-May-15 3:25am

this code is for asc oly.....

deepankarbhatnagar 18-May-15 3:30am

As per question requirement, it requires auto increment, but not decrement...

Sinisa Hajnal · Accepted Answer · 2015-05-14T22:52:00

Solution 2

Add into your SELECT ROW_NUMBER() function, partition over the date, ORDER BY DESC

The details depend on your database - it could be ROWNUM, ROW_NUMBER or maybe some other function. Do the research, this simple query should be very simple to implement.

This is for SQL Server
SELECT * ,ROW_NUMBER() OVER (PARTITION BY YEAR(your_date_field), MONTH(your_date_field) ORDER BY your_date_field DESC) AS sno
FROM your_table

NOTE that you shouldn't have SELECT * in production as it tends to break as new joins or views are added (ambigous fields) or you depend on the order of the fields for something. State explicitly what you're returning from the query.

This might be useful for mySQL (I didn't work with MySQL before):
MySQL RowNumber example[^]

If this helps, please take time to accept the solution. Thank you.

Posted 14-May-15 22:52pm

Sinisa Hajnal

Updated 14-May-15 22:56pm

v2

Comments

Surandar Raj R 15-May-15 7:23am

Thanks for your reply... It will be useful but i cannot add this ,because i used group by in my stored procedure....

See it ....

CREATE DEFINER = 'root'@'localhost'
PROCEDURE eliteelevator.sp_logistics(
flag varchar(50),
p_memberid int,
p_customerpid int
)
BEGIN

if(flag='logistic') then

SELECT
a.memberid,
a.UserName AS BillingName,
b.ReferredBy AS ReffName,
b.City,
b.Phone,
a.EmailID,
b.Address,
b.Product,
c.totalproductcost AS fact_totalproductcost,
c.fact_Termofschedule_first,
SUM(c.paidamt_first) AS fact_paidamt_first,
c.paiddate_first AS fact_paiddate_first,
c.fact_termofschedule_second,
SUM(c.paidamt_second) AS fact_paidamt_second,
c.paiddate_second AS fact_paiddate_second,
c.fact_termofschedule_third,
SUM(c.paidamt_third) AS fact_paidamt_third,
c.paiddate_third AS fact_paiddate_third,

c.fact_termofschedule_four as fact_term_four,sum(c.paidamt_four) as fact_paidamt_four,c.paiddate_four as fact_paiddate_four,
((c.totalproductcost) - (SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))) AS Fac_Paymentdue,
concat(round(((SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))/c.totalproductcost*100),2),'%') AS Fac_Pay_per,

d.pid,
d.totalamt AS ProductCost,
d.termsofschedule_first,
SUM(d.paidamt) AS cus_paidamt_first,
d.paydate AS cus_paydate_first,
d.fileurl1,
d.termofschedule_second,
SUM(d.paidamt_second) AS cus_paidamt_second,
d.paiddate_second,
d.fileurl2,
d.termofschedule_third,
SUM(d.paidamt_third) AS cus_paidamt_third,
d.paiddate_third,
d.fileurl3,

d.termofschedule_four,sum(d.paidamt_four) as cus_paidamt_four,d.paiddate_four as cus_paydate_four,
((d.totalamt) - (SUM(d.paidamt) + SUM(d.paidamt_second) + SUM(d.paidamt_third) + sum(d.paidamt_four))) AS Cus_paymentdue,
concat(round(((SUM(d.paidamt) + SUM(d.paidamt_second) + SUM(d.paidamt_third) + sum(d.paidamt_four))/d.totalamt*100),2),'%') AS cus_pay_rec_per,

e.installationcost,
e.paiddate AS installation_paidamount,
((e.installationcost) - SUM(e.paidamount)) AS intallation_Paymentdue,

f.ordplacedfactdate,
f.productionstartdate,
f.readlinessdate,
f.pickedupdate,
f.reachindiadate,
f.reachsitedate,

e.installationstartdate,
e.installationcompletedate,
e.handoverdate,

g.startdate,
g.expdate

FROM logintable AS a

LEFT JOIN clientinfo AS b
ON a.memberid = b.MemberID
LEFT JOIN factory_payment AS c
ON a.memberid = c.memberid
LEFT JOIN customerpayment AS d
ON a.memberid = d.memberid
LEFT JOIN installationcost AS e
ON a.memberid = e.memberid
LEFT JOIN logistics AS f
ON a.memberid = f.memberid
LEFT JOIN amc AS g
ON a.memberid = g.memberid
WHERE b.Status='Completed' or b.Status='Installation' or b.Status='Confirmed'
GROUP BY a.memberid,d.pid;

ELSEIF (flag = 'viewone') THEN
if(p_customerpid!=0) then
SELECT
a.memberid,
a.UserName AS BillingName,
b.ReferredBy AS ReffName,
b.City,
b.Phone,
a.EmailID,
b.Address,
b.Product,
c.totalproductcost AS fact_totalproductcost,
c.fact_Termofschedule_first,
SUM(c.paidamt_first) AS fact_paidamt_first,
c.paiddate_first AS fact_paiddate_first,
c.fact_termofschedule_second,
SUM(c.paidamt_second) AS fact_paidamt_second,
c.paiddate_second AS fact_paiddate_second,
c.fact_termofschedule_third,
SUM(c.paidamt_third) AS fact_paidamt_third,
c.paiddate_third AS fact_paiddate_third,

c.fact_termofschedule_four as fact_term_four,sum(c.paidamt_four) as fact_paidamt_four,c.paiddate_four as fact_paiddate_four,
((c.totalproductcost) - (SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))) AS Fac_Paymentdue,
concat(round(((SUM(c.paidamt_first) + SUM(c.paidamt_second) + SUM(c.paidamt_third) + sum(c.paidamt_four))/c.totalproductcost*100),2),'%') AS Fac_Pay_per,
d.pid,
d.totalamt AS ProductCost,
d.termsofschedule_first,
SUM(d.paidamt) AS cus_paidamt

Sinisa Hajnal 15-May-15 9:33am

Please, no. That is totally unreadable. I see group by, but I don't see how that changes anything? At worst you could wrap ROW_NUMBER around your select:
SELECT ROWNUM, * FROM (your select here)

Surandar Raj R 16-May-15 3:23am

As per you said Rownumber is perfectly works,

Here is the code tat perfectly works...
SELECT @row_num := IF(@prev_value= date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y'),@row_num+1,1) AS RowNumber
,@Customer:=date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y') as Date
,o.PayDate

,@prev_value := date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y')
FROM test o,
(SELECT @row_num := 1) x,
(SELECT @prev_value := '') y
order BY RowNumber DESC

Thanks a lot Sinisa Hajnal ....

Sinisa Hajnal 16-May-15 16:24pm

Glad to be of help :)

Surandar Raj R · Accepted Answer · 2015-05-15T21:25:00

Solution 4

SQL

By the reference of Sinisa Hajnal ... i figured it out..  thank u so much Sinisa Hajnal...
In Mysql:

SELECT  @row_num := IF(@prev_value= date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y'),@row_num+1,1) AS RowNumber
       ,@Customer:=date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y') as Date
       ,o.PayDate
      
       ,@prev_value := date_format(str_to_date(paydate, '%d-%m-%Y'), '%m%Y')
  FROM test o,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
  order BY RowNumber DESC

Posted 15-May-15 21:25pm

Surandar Raj R

Comments

King Fisher 16-May-15 3:47am

Can u ccept Sinisa hajval Solution :)

Sinisa Hajnal 16-May-15 16:25pm

Thank you, but its fine, I just provided wrong database example and didn't bother translating it in MySQL. Just gave him reference.

Surandar Raj R 17-May-15 23:40pm

because of ur reference oly i figured it out sinisa hajnal......

King Fisher 18-May-15 0:29am

That's good , yea you right . :)