Database attempt to retrieve all column name

[gahhon]

SqlCommand cmdColumnName = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DailyBudget' ORDER BY ORDINAL_POSITION", conBudget);

// bla bla bla

SqlDataReader dtrColumnName = cmdColumnName.ExecuteReader();
if (dtrColumnName.Read())                
    for (int i = 0, x = 1; i < count; i++,x++)
    categories[i] = dtrColumnName[x].ToString();            
dtrColumnName.Close();

The problem said the index was out of bounded.
As i check for the problem, i found out that i only have one value dtrColumnName[0].ToString() return to me the correct first column name, but i can't get others column. What should i do?
Thanks for advanced.

[JorgeM]

Create an array based on how many columns you get back from the query. Then use that array to store each column name.

You may need two queries, one to get a count of the columns that you use to build the array.

Then onece you know you can use your loop to increment the array position to store the different field names.

[gahhon]

dtrColumnName[0] is refering to the first column am i right?
so that suppose dtrColumnName[1] is refering to the next column?
can you provide a example to me? thanks for advanced.

[JorgeM]

Yes, you are correct, but I just ran an example of your query and the result is not one row with multiple columns, but actually multiple rows with only one column.

So, as you read through the results, you could read from column[0] and assign the values to an array, if you know the legth in advance. If not, simply perform some other operation as you read through.

[gahhon]

then how should i get one row with multiple columns?
i needed to get the all of the column names.
thanks for in advanced.

[JorgeM]

i needed to get the all of the column names.

Yes, that is why i orginally recommended an array. Grab the column names and place them in an array. If you know the length of the array ahead of time, just create the appropriate array size, otherwise, you could query to figure out how many rows your query will return, then build the array based on that size.

I am sure there are different ways you can do this, this is just the way that I would probably go about it.

protected void Page_Load(object sender, EventArgs e) {
        SqlConnection con = new SqlConnection("connectionStringInfo");
        SqlCommand cmd = new SqlCommand("yourSQL Query", con);
        con.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        try {
            int x = 0;
            string[] col = new string[15];
            while (reader.Read()){
                col[x] = (string)reader[0];
                Response.Write(col[x] + "<br/>");
                x++;
            }
        }
        finally {
            reader.Close();
        }
    }

[gahhon]

Okay, my one also correct but just change the if statement to while loop.
But how can i don't retrieve the first column name?

[JorgeM]

But how can i don't retrieve the first column name?

But you said that you were able to get the first column name.

dtrColumnName[0].ToString() return to me the correct first column name, but i can't get others column.

The code I posted above was tested with one of my DB tables. I was able to retrieve the first column values. I made sure that they were stored in the array properly by writing them back to the screen.

Check your syntax, maybe there is something missing there. I didnt test your exact version of the SQL block. I was focusing on the reader part.

[gahhon]

        protected void Page_Load(object sender, EventArgs e)
        {
            double totalUsage = 0;
            int rowCount = getRowCount();
            int columnCount = getColumnCount();
            string[] xValues = getColumnName();
            double[] yValues = new double[columnCount];

            SqlConnection conBudget = new SqlConnection(ConfigurationManager.ConnectionStrings["connMSJ"].ConnectionString);
            SqlCommand cmdBudget = new SqlCommand("SELECT Foods, Clothes, Drinks, Entertaiment FROM DailyBudget WHERE (Username=@username)", conBudget);            
            conBudget.Open();
            cmdBudget.Parameters.AddWithValue("@username", "gahhon");

            SqlDataReader dtrBudget = cmdBudget.ExecuteReader();
            if (dtrBudget.Read())
                for (int i = 0; i < columnCount; i++)
                {
                    yValues[i] = Convert.ToDouble(dtrBudget[i]);
                    totalUsage += Convert.ToDouble(dtrBudget[i]);
                }
            dtrBudget.Close();
            conBudget.Close();

            double RemainBudget = getRemainBudget(totalUsage);
            lblBudget.Text = "RM " + getDailyBudget().ToString("n2");
            lblCurrent.Text = "RM " + getCurrentBudget().ToString("n2");
            lblRemaining.Text = "RM " + RemainBudget.ToString("n2");
            insertChart(xValues, yValues);
        }

        protected void insertChart(string[] xValues, double[] yValues)
        {
            pcBudget.Series["Series1"]["PieLabelStyle"] = "Outside";
            pcBudget.Series["Series1"].Points.DataBindXY(xValues, yValues);
            pcBudget.Series["Series1"].EmptyPointStyle.IsValueShownAsLabel = false;
            pcBudget.Series["Series1"].EmptyPointStyle.IsVisibleInLegend = false;
            pcBudget.Series["Series1"].LegendText = "#VALX #PERCENT{P2}";
            pcBudget.DataManipulator.Sort(PointSortOrder.Descending, pcBudget.Series["Series1"]);

I am able to remove the first column name by using EXCEPT in database SQL Query. After that i try to insert into my piechart. it said Enumeration already finish.?
My piechart cannot `DataBindXY()'.

[JorgeM]

Sorry, i'm not following. I dont see how the last post of code is related to the original question.