ASP.NET 2.0 Problem with column name changing in a Gridview control

[DeMuy]

I have a gridview with AutoGenerateCol umns=true that is presenting a challenge to me.

Challenge #1

The data structure from the database server follows:
Product |Qty |Jan-09|Qty|Feb-09|Qty|Mar-09 etc
-----------------------------------------------------------------
CD|1.00 |$100.00|5.00|$ 500.00|7.00|$70 0.00 etc
DVD|2.00 |$400.00|7.00|$ 700.00|8.00|$80 0.00 etc

****** But the gridview control renders the Qty columns differently, I am getting the following columns from my gridview control.

Product |Qty |Jan-09|Qty1|Feb-09|Qty2|Mar-09 etc
-----------------------------------------------------------------
CD|1.00 |$100.00|5.00|$ 500.00|7.00|$70 0.00 etc
DVD|2.00 |$400.00|7.00|$ 700.00|8.00|$80 0.00 etc

I will like to have the column names "Qty" remain as "Qty" as generated by SQL

Challenge #2
How can I ensure that the Qty column is not rendered as type money in my RowDataBound event which I am using to calculate the totals?

It is important that I keep AutoGenerateCol umns=true so the gridview control can change dynamically based on the different SQL queries generated by a stored procedure.

Qty is a derived column

SUM(case when order_month = 2 Then Qty ELSE 0.00 END) AS Qty,

SUM(case when order_month = 2 Then total_price ELSE 0.00 END) AS [Feb-09],

SUM(case when order_month = 3 Then Qty ELSE 0.00 END) AS Qty,

SUM(case when order_month = 3 Then total_price ELSE 0.00 END) AS [Mar-09],

etc

[DeMuy]

I am not sure if I have made myself clear enough, anyway, I have a gridview where I need to have column names within the gridview to have the same name, but when I set up the sql query as

select SUM(case when order_month = 2 Then Qty ELSE 0.00 END) AS Qty,
SUM(case when order_month = 3 Then Qty ELSE 0.00 END) AS Qty,
SUM(case when order_month = 4 Then Qty ELSE 0.00 END) AS Qty,

etc

and it shows up in the gridview as

Qty
Qty1
Qty2

but I want it to just be Qty, Qty, and Qty. I cannot hardcode the headings as they are dynamically created.

[karthi84]

hope this helps

Try running the select query in DB server and i think the column names are named by the DB server.

If you want the name for the column header to be changed try from the code behind specifying individual column names to your choise after binding the data. this should get you the column name QTY in what ever column you like

[DeMuy]

Thank you for your feedback.

The column names Qty1, Qty2, Qty3, etc. are created by ASP.NET gridview control, they are Qty,Qty,Qty from the database side.

How do you specify individual column names when the SQL query itself is dynamic?

[karthi84]

Hi Demuy,
Its simple, you know that the first column is going to be Product and the rest are Qnty. and Month in alternate. count the number of columns and replace the alternate columns header till the last qnty column.

[DeMuy]

Thank you all for your assistance.